Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

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
0
maximyshka
Asked:
maximyshka
  • 7
  • 4
1 Solution
 
PatHartmanCommented:
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.
0
 
maximyshkaAuthor Commented:
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
0
 
PatHartmanCommented:
Are you sure your Worksheet name is correct?  I thought they couldn't contain spaces or special characters?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
maximyshkaAuthor Commented:
Yes it is correct. The do contain spaces.
0
 
PatHartmanCommented:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Data", "c:\file.xlsx", True, "My Dates!"

The file name was missing quotes.
0
 
maximyshkaAuthor Commented:
I use string in real code, it does not work
0
 
maximyshkaAuthor Commented:
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?
0
 
maximyshkaAuthor Commented:
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
0
 
maximyshkaAuthor Commented:
I got the answer " 'Add Bills-Electricity!' " - it works .
0
 
PatHartmanCommented:
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.
0
 
maximyshkaAuthor Commented:
I found the answer which works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now