Can't import Object is Read Only

[CCIDBMNG]

I'm trying to import an excel file through code with the docmd.transfertext but it tells me that it can not run because the database or object is read only. Can anyone tell me how to fix this please.

 

[CCIDBMNG]

Nevermind I figured it out, I was using the transfertext when I should have been using the transferspreadsheet. But does anyone know how to specify a worksheet. I know you can specify a range but I want to import a specific worksheet without knowing the range.

 

[boblarson]

You can import a specific sheet, however it requires a range to do so. The good news is that you can just set the columns and not the rows.

For example, if I had a sheet named "MySheet" within a workbook named "MyWorkbook.xls" then the code to import the specific sheet would be:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCMS", _
"C:\MyWorkbook.xls", True, "MySheet!A:ZZ"

Or, if you know how many columns there will be, just go to that many, like: "MySheet!A:F"

BL
hth

 

[CCIDBMNG]

Thanks that worked perfect