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

On Thu, 14 Aug 2008 11:07:06 -0700, DanRoy wrote:

> 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"
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail