DoCmd.Transferspreadsheet - need help in determining Excel range

[DanRoy]

Hello,

I developed a usable transferspreadsheet command in my code, but since the
data starts on row 3, I cannot use the default for range. My tables have
field 1 as a key field and any blank records, such as blank lines, cause an
error.

(CPSPass is the tab in the excel workbook I am reading)

Here is the code I am using:

tablename = "Dash-Import"
fn = "G:\Proposals\ECP0419 Program Affordability\BOEs\BOE Scorecard\BOE
Database\ECP419-1CPSPass1c as of 7-30-08.xls"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, tablename, fn,
True, "CPSPass!A2:z200"


In this particular table, I have the field titles in row 2, and data in rows
3-149. In the 7 other files that I will be importing, I may have as many as
1500 rows of data. I am trying to develop a routine I can use without regard
for the number of rows in any particular file. All of the files I will
import will have the same exact file structure.

To set the range correctl for a particular file I am reading, I need to know
the last row of data in the excel file to replace the "Z200" reference above.

Do you have any suggestions? Would you suggest that prior to performing the
excel import using the transferspreadsheet protocol, I should open an
instance of Excel, open the file, save the maxrows to Access as the upper
range for my import?

Thanks

Dan

 

[fredg]

Hello,

I developed a usable transferspreadsheet command in my code, but since the
data starts on row 3, I cannot use the default for range. My tables have
field 1 as a key field and any blank records, such as blank lines, cause an
error.

(CPSPass is the tab in the excel workbook I am reading)

Here is the code I am using:

tablename = "Dash-Import"
fn = "G:\Proposals\ECP0419 Program Affordability\BOEs\BOE Scorecard\BOE
Database\ECP419-1CPSPass1c as of 7-30-08.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, tablename, fn,
True, "CPSPass!A2:z200"

In this particular table, I have the field titles in row 2, and data in rows
3-149. In the 7 other files that I will be importing, I may have as many as
1500 rows of data. I am trying to develop a routine I can use without regard
for the number of rows in any particular file. All of the files I will
import will have the same exact file structure.

To set the range correctl for a particular file I am reading, I need to know
the last row of data in the excel file to replace the "Z200" reference above.

Do you have any suggestions? Would you suggest that prior to performing the
excel import using the transferspreadsheet protocol, I should open an
instance of Excel, open the file, save the maxrows to Access as the upper
range for my import?

Thanks

Dan

I believe :z will return all records to the end of column Z.

"CPSPass!A2:z"