Have Access Repeat Import Wizard Steps for EACH worksheet in a SINGLE Excel Workbook?

[Im2bz2p345]

Hi all,

I tried searching this question numerous places online and on this forum, but couldn't find out a way to automate this.

I don't know VBA too well, but I can follow directions if someone can point me in the right direction.

I have a single Excel file with 29 worksheets (each worksheet has less than 50 rows of data).

I want to import each worksheet into Access using the exact same methods: External Data tab -> New Data Source -> From File -> Excel -> Import the source data into a new table in the current database -> Press "OK" button -> Select the next worksheet -> Press "Next" button -> Check mark "First Now Contains Column Headings" -> Default settings for Field Options -> Press "Next" button -> Less Access add primary key -> Press "Next button" -> Import to Table with default filename (it's the worksheet name) -> "Finish" button

Is there an automated way to achieve this? I can generate a unique worksheet name list if that is needed. This website describes what I am doing if it is easier to reach, but basically says you have to continue to use the Wizard for each worksheet: https://smallbusiness.chron.com/impo...ess-40473.html



This thread provides code to the closest thing to what I'm looking for, but it is from 2013 and not sure if it applies to my situation: https://www.accessforums.net/showthread.php?t=37381

Any helps would be greatly appreciated!

~ Im2bz2p345

[ssanfu]

To automate the process, I'm afraid you will need to use VBA.

You said you don't know VBA very well, but I would suggest looking at Ken Snell's site Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
Are all 29 worksheets going into a single Access table?


In the "Importing from EXCEL Workbook Files" page, there are two examples I think you should look at:

Import Data from All Worksheets in a single EXCEL File into One Table via TransferSpreadsheet (VBA)

or

Browse to a single EXCEL File and Import Data from that EXCEL File via TransferSpreadsheet (VBA)

[Welshgasman]

What happened to the original post?

[pbaldy]

The original post was moderated, and apparently re-moderated itself when edited by the OP. I've re-approved it.

[Im2bz2p345]

Thanks! Yeah, I was just fixing some spelling errors in my OP. I just joined this forum, so I can understand the post approval moderation/anti-spam measures being taken. I'm a lot more active on MrExcel forum.

Anyway, a HUGE thank you to ssanfu and original author of the code (Ken Snell) for pointing me to a solution that could work! I haven't messed with Ken's VBA code, but I have a feeling it's exactly what I need. I'll attempt it later today if I get some free time. I will reference the first link and the code below (posting for my own reference in the future) as I attempt this.



Import Data from All Worksheets in All EXCEL Files in a single Folder into Separate Tables via TransferSpreadsheet (VBA)

Generic code to import the data from all worksheets in all EXCEL files in a single folder. Each worksheet's data will be imported into a separate table whose name is 'tbl' plus the worksheet name plus an integer value that represents a "counter" for the workbooks (e.g., "tblWorksheetName1").

Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPath As String, strFile As String
Dim strPassword As String

' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set objExcel = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\MyFolder\ with the actual path to the folder that holds the EXCEL files
strPath = "C:\MyFolder\"

' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = "passwordtext"

blnReadOnly = True ' open EXCEL file in read-only mode

strFile = Dir(strPath & "*.xls")

intWorkbookCounter = 0

Do While strFile <> ""

      intWorkbookCounter = intWorkbookCounter + 1

      Set colWorksheets = New Collection

      Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , _
            blnReadOnly, , strPassword)
      For lngCount = 1 To objWorkbook.Worksheets.Count
            colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
      Next lngCount

      ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
      objWorkbook.Close False
      Set objWorkbook = Nothing

      ' Import the data from each worksheet into a separate table
      For lngCount = colWorksheets.Count To 1 Step -1
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                  "tbl" & colWorksheets(lngCount) & intWorkbookCounter, _
                  strPath & strFile, blnHasFieldNames, _
                  colWorksheets(lngCount) & "$"
      Next lngCount

      ' Delete the collection
      Set colWorksheets = Nothing
      ' Uncomment out the next code step if you want to delete the
      ' EXCEL file after it's been imported
      ' Kill strPath & strFile

      strFile = Dir()

Loop

If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

Thanks all,

~ Im2bz2p345