Importing an Excel file into Access via VBA

Dev Articles Community Forums Sponsor:

July 8th, 2005, 09:32 AM

summer brew

Registered User

Join Date: Jul 2005

Posts: 12

Time spent in forums: 2 h 51 m 8 sec
Reputation Power: 0

Importing an Excel file into Access via VBA

I have found a ton of information on how to import an Access table into Excel, but I have come up short when I have been looking for a way to import an Excel file into Access via VBA.

Currently, I have 3 tables that I update monthly manually by means of the Import Wizard, but I am looking to automate the process as much as possible.

At the least, I would like to hit a button in a form that imports a particular Excel file (the name doesn't change from month to month) and at least brings me into the import wizard.

I am just starting out in VBA, and I know this is going to require a bit of code, so I'm reaching out for HELP!

Big thanks in advance for helping me out...

Paul

July 8th, 2005, 10:08 AM

sherrington

Contributing User

Join Date: Aug 2004

Posts: 110

Time spent in forums: 13 h 30 m 33 sec
Reputation Power: 9

try

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportSpecification", "FileName", True

July 8th, 2005, 11:05 AM

summer brew

Registered User

Join Date: Jul 2005

Posts: 12

Time spent in forums: 2 h 51 m 8 sec
Reputation Power: 0

I tried the TransferSpreadsheet Method, and it works, but only if the Excel file is open. If the Excel file isn't open I get an error message: "External table isn't in the expected format".

Any insight?

July 11th, 2005, 01:30 PM

lwells

Contributing User

Join Date: Sep 2004

Posts: 632

Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 9

Try checking which version of Excel you are using and change the acSpreadsheetTypeExcel9 to that version of Excel and see if that doesn't solve the problem.

lwells

July 19th, 2005, 05:07 PM

br_esper

Registered User

Join Date: Jul 2005

Posts: 23

Time spent in forums: 10 h 55 m 14 sec
Reputation Power: 0

Import XLS using RecordSet

Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command

With cnn2
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source="c:\temp.xls;" & "Extended Properties=Excel 8.0;"
.Open
End With

Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [Sheet1$] Where [Date] IS NOT NULL AND [Expense ID] IS NOT NULL "
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenStatic
rs2.LockType = adLockReadOnly
rs2.Open cmd2

While Not rs2.EOF
...do stuff...
rs2.MoveNext
Wend

May 3rd, 2006, 12:48 PM

Anand Khinvasar

Registered User

Join Date: May 2006

Posts: 1

Time spent in forums: 14 m 48 sec
Reputation Power: 0

A simple solution

Check following things:

1) If you have generated excel sheets in older version at some location (say DoCmd.OutputTo command which generates excel sheet in Excel3 format) and then tried generating excel files in newer version with the same name at same location(say Docmd.TransferSpeadsheet method which allows variety of Excel formats like Excel97). Solution for this:
Delete all the older excel sheets at the specified location and try again.

2) Try changing datatypes. Excel3 format of Docmd.OutputTo allows text field of maximum 255 characters. So to allow more characters use Docmd.Spreadsheet method. Also linked tables should have there field format set to memo instead of text. (On SQL Server set the same field to text(16))

February 15th, 2010, 12:03 PM

erekh

Registered User

Join Date: Feb 2010

Posts: 2

Time spent in forums: 6 m 44 sec
Reputation Power: 0

Inserting header into excel column

I'm attempting to import a workbook (need data from multiple tabs) and that seems to be going ok, except for the fact that I need a heading added to one of my columns. Is there a way to do this in vba as the workbook is systematically generated?

Quote:

Originally Posted by sherrington

try

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportSpecification", "FileName", True

February 15th, 2010, 02:10 PM

AlanSidman

Contributing User

Join Date: Nov 2008

Location: Lake County, IL

Posts: 240

Time spent in forums: 1 Day 15 h 49 m 19 sec
Reputation Power: 5

Why doesn't your excel spreadsheet have a heading already? Access imports the headings from Excel if you set the first row to contains headings.

Alan

__________________
Alan Sidman
__________
If I helped you, I'd like to know. Click on the icon next to the thread number and tell me.

February 15th, 2010, 04:51 PM

erekh

Registered User

Join Date: Feb 2010

Posts: 2

Time spent in forums: 6 m 44 sec
Reputation Power: 0

Magic of our IT team I work with and just to get it added I would have to go through 'change control' get funding, have meetings, and waste a ton of time and resources for something I would think would be doable as long as someone out there knows the code... There are two columns on the specific worksheet i'm dealing with.
The first column does have text (which i use as a heading, even though it is more like a title) but the second column just has the values...

Quote:

Originally Posted by AlanSidman

Why doesn't your excel spreadsheet have a heading already? Access imports the headings from Excel if you set the first row to contains headings.

Alan

#10

February 16th, 2010, 09:34 AM

AlanSidman

Contributing User

Join Date: Nov 2008

Location: Lake County, IL

Posts: 240

Time spent in forums: 1 Day 15 h 49 m 19 sec
Reputation Power: 5

I still don't understand why you can't open the excel spreadsheet before importing it into Access and simply type in the heading you desire. Failing this, Access will import the data and will put a fieldname called Field1 if there is no name. You can then go into the table properties and change the name to what ever you would like. It appears to me like you are trying to over-engineer this project. A second alternative is to have it import the the data into an existing table which already has the field names assigned.

Alan

#11

November 27th, 2012, 08:04 AM

TudX

Registered User

Join Date: Nov 2012

Location: Brazil

Posts: 1

Time spent in forums: 18 m 24 sec
Reputation Power: 0

Solution for Access 2010

By now you have probably solved your problem, but as this is one of the first results in my Google search, I'll post my solution, that might be usefull to someone:

Code:

Sub ImportXLS()
Dim db As DAO.Database
Set db = CurrentDb
On Error Resume Next:   db.TableDefs.Delete "tblImport":   On Error GoTo 0
db.TableDefs.Refresh
DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="tblImport", _
    FileName:="C:\FileName.xls", _
    HasFieldNames:=True, _
    Range:="SheetName!A:DN"
db.TableDefs.Refresh
db.Close:   Set db = Nothing
End Sub

If you want help on the TransferSpreadsheet function, put the cursor over it and press F1.

If you receive a "not defined user-defined data type" error, you need to reference the DAO Library. Check http://support.microsoft.com/kb/145759 for the solution.

Good luck!