I'm importing data from multiple Excel sheets into an Access table.
Using the code below. However, the code below starts copying data on
row 1 of the Excel sheet. Since the Excel sheets I am accessing may
not always have records starting on row 1, how can I modify the
code below to first scroll down to where the field name starts and
then start grabbing the data below and then stop upon reaching an
empty row? All my Excel tables have the exact same column headings;
they just don't always start on row 1. Before the import I call
the SQL CreateTable function to define the table before copying data
from
the Excel sheet.

Thanks for any help you can provide...

' open a recordset on the Excel WorkSheet
Set rsExcel = dbExcel.OpenRecordset(tdf.Name)
rsExcel.MoveFirst

' loop throught all data in excel and stuff into access
Do
rsNewTbl.AddNew
For Each fld In tdf.Fields
rsNewTbl(fld.Name) = rsExcel(fld.Name)
Next
rsNewTbl.Update
rsExcel.MoveNext
' expect err 3021 and trap for it when we move beyond the
end of the data
If Len(rsExcel(0)) = 0 Then ' blank line also tosses us
out
Exit Do
End If
Loop

I would use something like this inserted into the top of the loop.

If Len(rsExcel(0)) = 0 then rsexcel.movenext



hth


" wrote:

I'm importing data from multiple Excel sheets into an Access table.
Using the code below. However, the code below starts copying data on
row 1 of the Excel sheet. Since the Excel sheets I am accessing may
not always have records starting on row 1, how can I modify the
code below to first scroll down to where the field name starts and
then start grabbing the data below and then stop upon reaching an
empty row? All my Excel tables have the exact same column headings;
they just don't always start on row 1. Before the import I call
the SQL CreateTable function to define the table before copying data
from
the Excel sheet.

Thanks for any help you can provide...

' open a recordset on the Excel WorkSheet
Set rsExcel = dbExcel.OpenRecordset(tdf.Name)
rsExcel.MoveFirst

' loop throught all data in excel and stuff into access
Do
rsNewTbl.AddNew
For Each fld In tdf.Fields
rsNewTbl(fld.Name) = rsExcel(fld.Name)
Next
rsNewTbl.Update
rsExcel.MoveNext
' expect err 3021 and trap for it when we move beyond the
end of the data
If Len(rsExcel(0)) = 0 Then ' blank line also tosses us
out
Exit Do
End If
Loop