VBA Import Excel to Access Table with VBA

Rx_ · Jun 5, 2013

PNG - create a table with this design (NOTE: NO Primary Key)
.XLS NOTICE - change the extension to .XLSX (this site won't allow .xlsx extensions to be uploaded - so change it back to avoid an error)

In Code window menu - Tools -> Reference -> check on MS Excel

Put this code in Access Module: in immediate window - run the BasicImportExcel2Access

Note: The Access table probably won't automatically refresh.

Code:

Option Compare Database
Option Explicit
' in code window menu Tools - Reference - must have Excel checked
Sub BasicImportExcel2Access()
      Dim xlsht As Excel.Worksheet
      Dim xlWrkBk As Excel.Workbook
      Dim myRec As DAO.Recordset
      Dim xlApp As Excel.Application
      Dim xlWrksht As Excel.Worksheet
      Dim i As Long

10    Set myRec = CurrentDb.OpenRecordset("XLImportTest")
20    Set xlApp = CreateObject("Excel.Application")

30    Set xlWrkBk = xlApp.Workbooks.Open("C:\ImportDemo.xlsx") ' Path can be a variable from a list in a table
40    Set xlWrksht = xlWrkBk.Sheets(1) ' 1 can be a variable to loop 1..10 for example
       
50     For i = 2 To 10
       
60        myRec.AddNew
70        On Error Resume Next
80        myRec.Fields(0) = xlWrksht.Cells(i, "A") ' CInt(xlWrksht.Cells(i, "A"))  if conversion necessary
90        myRec.Fields(1) = xlWrksht.Cells(i, "B")
100       myRec.Fields(2) = xlWrksht.Cells(i, "C")
110       myRec.Update

120    Next
121    Set myRec = Nothing
122    Set xlApp = Nothing

130   Exit Sub
          ' add some real error trapping
End Sub

Frothingslosh · Jun 21, 2013

Why would you use this over TransferSpreadsheet of you don't need on-the-spot conversion? TransferSpreadsheet grabs an entire worksheet and is noticeably faster.

Code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "XLImportTest", "C:\ImportDemo.xlsx", True, "Sheet1!"

I personally use a much longer, more involved home-made procedure, but that's just because the tools I have to develop and use at work require importing different kinds of spreadsheets into different tables in the same database, where I have to cherry-pick columns that aren't ever in the same locations on sheets that are never identically named or even in the same order, and do data validation and conversion during the import. (The only constants are the column headers and the fact that the sheets I need all have "data" somewhere in their names, and are the ONLY ones with "data" in their names.)

That said, I still use TransferSpreadsheet any time I possibly can.

Rx_ · Dec 11, 2013

That raised a great question in case anyone is searching for what solution to use. A code solution is not for everyone.
Searching for the key word TransferSpreadsheet on this forum currently brings up about 20 pages.
Often, the TransferSpreadSheet isn't behaving exactly as desired.
While the code sample didn't do much, it displayed the basics for the options that code can present.
A coding solution is probably not for every occasion just as you indicated.
Please be encouraged to submit a coding solution of your own.
The web link is useful to cite as the next 20 TransferSpreadsheet questions appear with questions about special features it doesn't satisfy.

My most common use is to design a query, save the string in a variable, create a recordset, then copy the recordset into Excel.

E.G.
550 Set strDataT_84 = CurrentDb.OpenRecordset(SQLQueryStringVariable, dbOpenSnapshot, dbReadOnly)
560 intRowPos = 6 ' Sets starting Row for data in Excel - reference fields to this
580 ObjXL.DisplayAlerts = False ' Turn off Display Alerts
590 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset strDataT_84
610 intMaxRecordCount = strDataT_84.RecordCount - 1 ' - use for max rows returned in formatting later

The desired result will often determine the correct procedures.
Thanks for the comment. As the number of views indicate, people are looking for the different solutions.

nhtuan · Nov 10, 2020

Frothingslosh said:
Why would you use this over TransferSpreadsheet of you don't need on-the-spot conversion? TransferSpreadsheet grabs an entire worksheet and is noticeably faster.

Code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "XLImportTest", "C:\ImportDemo.xlsx", True, "Sheet1!"

I personally use a much longer, more involved home-made procedure, but that's just because the tools I have to develop and use at work require importing different kinds of spreadsheets into different tables in the same database, where I have to cherry-pick columns that aren't ever in the same locations on sheets that are never identically named or even in the same order, and do data validation and conversion during the import. (The only constants are the column headers and the fact that the sheets I need all have "data" somewhere in their names, and are the ONLY ones with "data" in their names.)

That said, I still use TransferSpreadsheet any time I possibly can.

Thank you Frothingslosh for sharing,
As I use MS Access 365, just want to share for someone who may need, it works well.

Private Sub cmdGetExcel_Click()
If IsNull(Len(Dir("C:\Users\admin\Documents\MyProject\GoogleSheetName.xlsx"))) = False Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TableFromGoogle", _
"C:\Users\admin\Documents\MyProject\GoogleSheetName.xlsx", True, "GoogleSheetName!"
Else
MsgBox "No data file to import"
Exit Sub
End If
End Sub

VBA Import Excel to Access Table with VBA

Rx_

Nothing In Moderation

Attachments

Frothingslosh

Premier Pale Stale Ale

Rx_

Nothing In Moderation

nhtuan

New member

Similar threads

Users who are viewing this thread