Transferspreadsheet issue

[vanhagar121]

I am developing an access application which is used to print out transmittal cover sheets and individual transmittal forms. The data is kept on excel sheets. I am attempting to bring data into an access table using the docmd.transferSpreadsheet command. I make a copy of the sheet and I do some manipulation of the copied sheet before I import the data. I name the copied sheet sheet1 I also check the range of the sheet for the number of rows I need to import My command line with all the variables is the following

DoCmd.TransferSpreadsheet acImport, 10 , "tblDocumentDetail_Temp", True, RangeName

I use variables to define RangeName

RangeName = "Sheet1!A14:R" & Cstr(rwcurrent -1) which works out to Sheet1!A14:R583. rwcurrent is just a variable I am using to count the number of rows to import I cannot use rowcount because there are rows below the rows I need to import which would be counted using the row count command. When the command is executed I get the following error

The Microsoft Office Access database engine could not find the object 'Sheet1$A14:R583' The same error occurs when I hard code the range instead of using a variable. Any idea why this occurs if I remove the ! from the range the $ does not appear in the error message but it still cannot find the range. Any help on this would be appreciated. Thanks.

 

[Solo712]

I am developing an access application which is used to print out transmittal cover sheets and individual transmittal forms. The data is kept on excel sheets. I am attempting to bring data into an access table using the docmd.transferSpreadsheet command. I make a copy of the sheet and I do some manipulation of the copied sheet before I import the data. I name the copied sheet sheet1 I also check the range of the sheet for the number of rows I need to import My command line with all the variables is the following

DoCmd.TransferSpreadsheet acImport, 10 , "tblDocumentDetail_Temp", True, RangeName

I use variables to define RangeName

RangeName = "Sheet1!A14:R" & Cstr(rwcurrent -1) which works out to Sheet1!A14:R583. rwcurrent is just a variable I am using to count the number of rows to import I cannot use rowcount because there are rows below the rows I need to import which would be counted using the row count command. When the command is executed I get the following error

The Microsoft Office Access database engine could not find the object 'Sheet1$A14:R583' The same error occurs when I hard code the range instead of using a variable. Any idea why this occurs if I remove the ! from the range the $ does not appear in the error message but it still cannot find the range. Any help on this would be appreciated. Thanks.

Rename "Sheet1" to something else, eg "MyData". The RangeName called
"MyData!A14:R583" should work ok.

Best,
Jiri

 

[vanhagar121]

Hi Jiri,

I tried naming the sheet LNGSheet and I get the same error. The error message just says can't find LNGSheet$A14:R583 Any other ideas?

Thanks in advance for any suggestions.

 

[CJ_London]

Think you are missing an exclamation mark in your range name - LNGSheet!$A14:R583

Also, not sure if you have simplified your transferspreadsheet command, but it is different for .xls and .xlsx (presume 10 is one of these?) and you are missing the file path name which will include the filetype suffix

Case ".xls"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tblDocumentDetail_Temp", "C:\temp\book1.xls", True, RangeName
Case ".xlsm", ".xlsa", ".xlsx"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, ""tblDocumentDetail_Temp", "C:\temp\book1.xlsx", True, RangeName

 

[Solo712]

Hi Jiri,

I tried naming the sheet LNGSheet and I get the same error. The error message just says can't find LNGSheet$A14:R583 Any other ideas?

Thanks in advance for any suggestions.

Hmmmm, ...strange. I once had the same problem but it cleared after I renamed the worksheet. Have you tested if the rangename is found when you omit the sheet name ? After all it is the first sheet, so the command should work without the worksheet label.

Just realized: the command above does not show the name of the Excel file you are transferring from. Make sure you give the full path to the file and it is correct.


Best,
Jiri

 

[spikepl]

I had a similar issue one time, and did it like this:

Set objRange = objWorksheet.Range("myrange")
    mystring = objRange.Address
    mystring = Replace(mystring, "$", "")
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Employees" & Str(I), strFileName, False, objWorksheet.Name & "!" & mystring

Further, it is worthwhile to note that namede ranges, when defined in the spreadsheet, are global (per default) so that the actual sheeet name does not need to be included.

 

[CJ_London]

You are missing an exlcamation mark

I tried naming the sheet LNGSheet and I get the same error. The error message just says can't find LNGSheet$A14:R583 Any other ideas?

It should be LNGSheet!$A14:R583

 

[vanhagar121]

Hello,

When I posted my original line of code I made a mistake as many people noted I neglected to include the path to the spreadsheet. Here is the correct line of code. Do.Cmd.TransferSpreadsheet acImport acSpreadsheetTypeExcel12, "tbl_DocumentDetail_temp, sFile, True , RangeName

sFile is the path and name of the spreadsheet I am trying to get the data from.
I also changed the 10 to acSpreadsheetTypeExcel12 as recommended by another person
RangeName is my variable for the Range. In my code the Range was LNGSheet!A14:R583 . I did not leave out the !. The resulting error message box from using the defined range is still The Microsoft Access Database Engine could not find the object LNGSheet$A14:R583. Make sure the object exists and that you spellits name and the path name correctly

Thanks in advance for any help.

 

[vanhagar121]

Hello,

When I posted my original line of code I made a mistake as many people noted I neglected to include the path to the spreadsheet. Here is the correct line of code. Do.Cmd.TransferSpreadsheet acImport acSpreadsheetTypeExcel12, "tbl_DocumentDetail_temp, sFile, True , RangeName

sFile is the path and name of the spreadsheet I am trying to get the data from.
I also changed the 10 to acSpreadsheetTypeExcel12 as recommended by CJ_London RangeName is my variable for the Range. In my code the Range was LNGSheet!A14:R583 . I did not leave out the !. The resulting error message box from using the defined range is still the Microsoft Access Database Engine could not find the object LNGSheet$A14:R583. Make sure the object exists and that you spellb its name and the path name correctly

Thanks in advance for your help.

 

[Solo712]

Hello,

When I posted my original line of code I made a mistake as many people noted I neglected to include the path to the spreadsheet. Here is the correct line of code. Do.Cmd.TransferSpreadsheet acImport acSpreadsheetTypeExcel12, "tbl_DocumentDetail_temp, sFile, True , RangeName

sFile is the path and name of the spreadsheet I am trying to get the data from.
I also changed the 10 to acSpreadsheetTypeExcel12 as recommended by CJ_London RangeName is my variable for the Range. In my code the Range was LNGSheet!A14:R583 . I did not leave out the !. The resulting error message box from using the defined range is still the Microsoft Access Database Engine could not find the object LNGSheet$A14:R583. Make sure the object exists and that you spellb its name and the path name correctly

Thanks in advance for your help.

Hi vanhagar121,
there have been some ideas offered for you to try. Have you tried the range name without the sheet designation ? Have you run the code by spikepl ? We are trying to help, you know ?

Best,
Jiri

 

[CJ_London]

try

Do.Cmd.TransferSpreadsheet acImport acSpreadsheetTypeExcel12, "tbl_DocumentDetail_temp", sFile, True , RangeName

- missing quotation mark after the table name (tho' I suspect this is a typo - wouldn't have compiled)

Also make sure your sfile variable contains the full filepath and file extension - use debug.print just before calling the transferspreadsheet do double check

Have you tried manually linking to the file to make sure it works in principle?

Also try substituting RangeName with "LNGSheet!A14:R583" and see if that works - if it does, it implies a problem with your RangeName variable

 

[antonsachs]

Think you are missing an exclamation mark in your range name - LNGSheet!$A14:R583

Also, not sure if you have simplified your transferspreadsheet command, but it is different for .xls and .xlsx (presume 10 is one of these?) and you are missing the file path name which will include the filetype suffix

Case ".xls"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tblDocumentDetail_Temp", "C:\temp\book1.xls", True, RangeName
Case ".xlsm", ".xlsa", ".xlsx"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, ""tblDocumentDetail_Temp", "C:\temp\book1.xlsx", True, RangeName

Thank you, "! works
My current code is:

DoCmd.TransferSpreadsheet acImport, , strImportTable, strFullFileName, True, "!" & strImportRange
works for importing from both open and closed Excel workbook.