Import From Excel from unusual range of rows

[Guest]

I use Access 2002 and Excel 2002.

I have an excel workbook with many tabs. I need to import from one of the
tabs. The data I need to import is located in rows 33 though the end of that
tab, wherever that might be. Rows 1 through 32 above USED to have values in
a column that I could select around. I was able to successfully import using
the following:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "Temp_Whatever",
strFullPathExcelFile, True, inputFileTabName & "!"
strSQL = "SELECT * FROM Temp_Whatever WHERE My_Column is not null;"

This worked because MyColumn had nulls in rows 1 through 32.

Now, however, the rows above do NOT have any distinguishable values in ANY
of the columns, so my previous logic will not work.

1. Is there any way to do a "TransferSpreadsheet acLink" to just a
selection of rows within a tab of a workbook?
2. OR, is there a way to "SELECT ROWS 33 through whatever FROM
Temp_Whatever WHERE My_Column is not null;"?

 

[Graham Mandeno]

Hi Bill

You can specify a range as well as a worksheet name in the last argument -
for example:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "Temp_Whatever",
_
strFullPathExcelFile, False, inputFileTabName & "!A33:Z300"

This will link only the rows after 32, but it will NOT include row 1 (the
column headings) so you must set the second-last argument to False.

 

[PC Datasheet]

Look at the Transferspreadsheet function in the Help file. You can specify a
range to import. Mahe rows 33 to the end a range and then specify that range
in the Transferspreadsheet function.