• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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