Import Excel Table into Access Table

[alvinmathew88]

I have set up a table named "BOM" in a excel spreadsheet that I am trying to import into an Access table named "BOM2008".
Both the table in the excel spreadsheet have the same header info and column #s.
The only variable in this is that the rows in the excel spreadsheet could increase or decrease depending on the user.

I cant use the DoCmd.Transferspreadsheet method because the "named" range reference in the method is only for named RANGES and not TABLES.

Can someone please help me with this? Im thinking potentially a looping code? I don't know how to get started on this.

Thanks in advance,

- Alvin

 

[Joe4]

I cant use the DoCmd.Transferspreadsheet method because the "named" range reference in the method is only for named RANGES and not TABLES.

The "Range" argument for the Transferspreadsheet command is optional. If you leave it blank, it will import the whole active sheet.

 

[alvinmathew88]

The "Range" argument for the Transferspreadsheet command is optional. If you leave it blank, it will import the whole active sheet.

Hey Joe4

Understood, but the area I'm trying to import is within table range. I'm not necessarily trying to import the entire spreadsheet.

 

[Joe4]

So, did you try naming the range which you want to import, and then try using the Range argument?

 

[alvinmathew88]

So, did you try naming the range which you want to import, and then try using the Range argument?

The table in Excel is already called "BOM"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, conTABLE_NAME, strFILE_PATH, False, "BOM"

^ If i do the above, i get a runtime error saying that the Range couldn't be found. I confirmed my finding by using the import utility and selecting import named range and it cant find the table. Im having to circumvent it now by opening the file and getting the cell address of the table and then using the transferspreadsheet method.

I cant think of another way to do this.

 

[Joe4]

A named Range is different from a Table name, I believe.
See here for how to name a range: How to Create a Named Range in Excel
The named range can be less than your whole Table.

 

[alvinmathew88]

A named Range is different from a Table name, I believe.
See here for how to name a range: How to Create a Named Range in Excel
The named range can be less than your whole Table.

Yup! A listobject (table name) is different from a named range. I was trying to leverage the listobject's ability to be referenced even when the data is dynamic. I've had to circumvent the system by getting the address of listobject and passing it to the range portion of the transferspreadsheet method.

Thanks for your help, Joe!

 

[Joe4]

Glad you got it all sorted out now!