Set objExcel = CreateObject("Excel.Application") Set objExcel = GetObject(, "Excel.Application") Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Booleanĭim objExcel As Object, objWorkbook As Objectĭim strPathFile as String, strTable as String Because all of the worksheets' data will be imported into the same table, all of the EXCEL files' worksheets must have the data in the same layout and format.
Generic code to import the data from all worksheets in a single EXCEL file. Import Data from All Worksheets in a single EXCEL File into One Table via TransferSpreadsheet (VBA) StrSql = "Insert Into tblMP3s(FilePathFileName) Select '" & str1 & "'" Sub ListFiles(fld As Object, Mask As String) Sub ListFolders(fldStart As Object, Mask As String) Set fldStart = fso.GetFolder("C:\1E") ' <- starting folder '-add a reference to Microsoft Scripting Runtime xlsx will require ACE engine for ADO) Sub ListFoldersSubfldsFiles() Here is the FileSystemObject routine for getting your list (question: what OS you on? winXP - win7? What version of Access? 2003-2010? ADO works differently between Jet an ACE, butįileSystemObject works the same in either version - and Excel. I find ADO to be a lot more reliable and flexible Once you have this list of Excel files to import Data from - you can loop through this list - and I would use ADO to read the content of each Excel file. Here is a routine that uses the FileSystemObject class to recursively dig through all subfolders in a starting folder and then writes Just my 2 Ȼ worth, but I would start by acquiring a list of your Excel files and store that list in a table. I canĭo them one at a time but there had got to be a better way!! The filesĪre all very similar and need to be imported into one table in excel. "Steelreserve40" schrieb im Newsbeitrag have hundreds of excel files that I need to import to access.
If the table doesn't exist yet it will be created based on the content of the first excel sheet you import. If your Excel sheets don't have column headers set the last parameter in the DoCmd call to False instead of True Where "C:\Temp\ToBeImported" is the directory where all your excel sheets are located and "MyExcelImport" is the name of the table the data have to imported to. ? importExcelSheets("C:\Temp\ToBeImported", "MyExcelImport") Then you could do a loop with Dir() and just import each of these files into your table.Ĭode could look as follows: Public Function importExcelSheets(Directory As String, TableName As String) As LongĭoCmd.TransferSpreadsheet acImport,, TableName, strFile, True 'has columnheadersĬopy above code into a new, empty standard modul and then call it in the immediate window of VBA this way: Let's say you have all excel file in a single folder.