asked on

Import data in access from Excel using vba

Hi I need to import or link to an excel file worksheet. One file can have many worksheet, i need to be able to link or import particular worksheet in access 2013.

I use the following code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Data", c:\file.xlsx, True, "My Dates!"

thanks for help

PatHartman

That is VBA. What is wrong with it? Are you asking to do the import with OLE automation? Why would you do that if the TransferSpreadsheet works for your situation? There is no advantage to using OLE automation and in fact there are disadvantages. The only reason you would use automation is if the TransferSpreadsheet didn't work because you wanted to import individual cells at specific locations rather than importing a whole sheet or named range.

maximyshka

ASKER

I have a different problem now, unrelated to my previous questions.
I have excel files with many worksheets in them. For example one file might have worksheet1, worksheet2, worksheet3... I need to import in access only worksheet3. The code I provided does not work . It only works if i do not specify worksheet. In that case it takes only worksheet1

PatHartman

Are you sure your Worksheet name is correct? I thought they couldn't contain spaces or special characters?

maximyshka

ASKER

Yes it is correct. The do contain spaces.

PatHartman

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Data", "c:\file.xlsx", True, "My Dates!"

The file name was missing quotes.

maximyshka

ASKER

I use string in real code, it does not work

maximyshka

ASKER

the problem is that I have sheet names with spaces . The code is working when sheet name does not have spaces. Is there any way to rename the sheets in excel file using access vba?

maximyshka

ASKER

strange when i create worksheet named "sheet 2!" it is working, but it doesn't work with worksheet named "Add Bills-Electricity!" . I've attached template i am getting. In case any one can help.

I need to make a link table or import data doesn't metter. when i use it with current sheet names i get

error 3129, invalid SQL statement. Expected DELETE, INSERT, PROCEDURE, SELECT, UPDATE.

thanks,
Max
test-file.xlsx

ASKER CERTIFIED SOLUTION

maximyshka

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

Just another of the little problems caused by using object names with embedded spaces or special characters. I knew when I saw your statement that the problem was with the sheet name. Glad you found the solution and thanks for posting back.

maximyshka

ASKER

I found the answer which works