cycle thru changing fields (Office 2000)

Topic

New Reply

I have a table that is imported from an ever-changing Excel spreadsheet that I need to convert to a usable table structure. The imported table specifies the quantity of dynamic items (columns) to include for each system (rows). The 2 leftmost columns identify the system and are static, the rest of the imported column names (table fields) and quantities change regularly. Actual data is either a number or a blank, i.e. there are no zeroes in the Excel file.

Here’s what I want to accomplish:
1. Import the latest Excel spreadsheet to a temporary table.
2. Iterate through each field in a particular record of the temporary table and, if there is a number in that field, append a new record into a table with structure System identifier 1, System identifier 2, (the 2 leftmost columns in the Excel file and the 1st two fields in the temp table), the current field NAME, and the value in that field.

Does that make sense? Does anyone know how to do that?

Thanks for any hints.
Kathi

Reply | Quote

Replies

Code corrected by HansV – see below

Try the following. You must set a reference to the Microsoft DAO 3.6 Object Library in Tools | Reference in the Visual Basic Editor. Substitute the correct names of the tables in the constants at the beginning of the code.

Sub ProcessTable()
‘ Substitute actual table names
Const strTemp = “tblTemp”
Const strDest = “tblTarget”

Dim dbs As DAO.Database
Dim rstTemp As DAO.Recordset
Dim rstDest As DAO.Recordset
Dim i As Integer

On Error GoTo ErrHandler

‘ Reference to database
Set dbs = CurrentDb
‘ Open recordsets on tables
Set rstTemp = dbs.OpenRecordset(strTemp, dbOpenDynaset)
Set rstDest = dbs.OpenRecordset(strDest, dbOpenDynaset)

‘ Loop through records of imported table
Do While Not rstTemp.EOF
‘ Loop through fields (starting with 3rd field)
‘ originally had For i = 2 To rstTemp.Fields.Count
For i = 2 To rstTemp.Fields.Count – 1
If Not IsNull(rstTemp.Fields(i)) Then
‘ Add new record to destination table
rstDest.AddNew
rstDest.Fields(0) = rstTemp.Fields(0)
rstDest.Fields(1) = rstTemp.Fields(1)
rstDest.Fields(2) = rstTemp.Fields(i).Name
rstDest.Fields(3) = rstTemp.Fields(i)
rstDest.Update
End If
Next i
rstTemp.MoveNext
Loop

ExitHandler:
‘ Clean up
On Error Resume Next
rstDest.Close
rstTemp.Close
Set rstDest = Nothing
Set rstTemp = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Hans, thanks for replying. I set the reference, pasted your code as a Public sub on an unbound form that contains a single command button. I named the sub ProcessTable and changed the constants to the names of my tables. The OnClick event for the button has one line “ProcessTable”

What have I missed? The very informative (sarcasm) error I get when I click the button is “Item not found in this collection”. No identification of which item was not found, no debug button and highlighted code, nothing.

Any ideas?
Kathi

Reply | Quote

I’m very sorry, that’s my fault. I didn’t test the code before posting it. Change the line

For i = 2 To rstTemp.Fields.Count

to

For i = 2 To rstTemp.Fields.Count – 1

(fields are numbered starting at 0, so the last one is Count-1, not Count)

Reply | Quote

And VOILA!!! There we have it.

I should have seen that myself, I suppose, but I got so wrapped up in making sure all the DAO references were set and the items were declared. You know, “Can’t see the forest for the trees.”

Once again, you are my hero!

Thanks, Kathi

Reply | Quote