Excel cell style references

[sconly]

Can anyone advise me on the following please.

I was just wondering if it was possible to loop through a tables data (cell by cell), much like you can do with an Excel worksheet in vba.

For example;

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")

    Do Until objXL.Cells(intRowCount, 1) = ""
        
        'then read (and store) the data for the rest of the current row cells
        strProduct = objXL.Cells(intRowCount, 1).Value
        If objXL.Cells(intRowCount, 2).Value <> "" Then
            strXLVal01 = objXL.Cells(intRowCount, 2).Value
        End if
        intRowCount = intRowCount + 1
    Loop

The reason I ask is that in one of my projects i create a separate spreadsheet (workbook), which the user can amend the data and then update the data in the database with the click of a button, for each job. But the headers (field names) in the sheets can be different for each job, so i can't reference the headers (field names) in an Access query.

I hope this makes sense, and you can follow it.

Thanks in advance.

 

[Kafrin]

Yes it is possible.

You need to open up the recordset/table, then you can loop through the fields by index number, from 0 to (Fields.Count-1). You reference the fields by number then. Eg:

Dim Ctr as Byte, rs as Recordset

Set rs=CurrentDb.Openrecordset("MyTable")

For Ctr=0 to rs.Fields.Count-1
rs.Fields(Ctr)=...
Next Ctr



If you also need to loop through the records then you need:

rs.MoveFirst

Do Until rs.EOF 'Do until you get to the end of the records
...
rs.MoveNext
Loop

 

[sconly]

Cheers for that. I'll give it a go.