Automate the transfer of multiple excel files to One Access Table

[Clifton]

Automate the transfer of multiple excel files to one Access table.

Hello. I'm new to this forum and whilst not a complete newbie to Access or excel or basic VBA programming i didn't have a clue how to do this but i did find some code when searching for answers through google
However, i want to do two things in addition to what the code below does. I want to transfer these files but my records start on a certain row in excel. To Be exact row 17. I also want to be able move the imported excel files into an exported folder and exporting

I hope someone can give me some advise on how to do this

Thanks

Here is the code i found




Option Compare Database
Option Explicit

Public Function ImportExcelMultipleFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

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

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\Kevin\Google Drive\ATOS\ATOS Reports\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "ATOSData"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Function

 

[Clifton]

Hi
Can no one help with this?

 

[ajvol01]

Automate the transfer of multiple excel files to one Access table.

Hello. I'm new to this forum and whilst not a complete newbie to Access or excel or basic VBA programming i didn't have a clue how to do this but i did find some code when searching for answers through google
However, i want to do two things in addition to what the code below does. I want to transfer these files but my records start on a certain row in excel. To Be exact row 17. I also want to be able move the imported excel files into an exported folder and exporting

I hope someone can give me some advise on how to do this

Thanks

Here is the code i found




Option Compare Database
Option Explicit

Public Function ImportExcelMultipleFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

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

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\Kevin\Google Drive\ATOS\ATOS Reports\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "ATOSData"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Function

Hi Clifton.

I think one thing you may need to do in the Excel files is to define a named range for the rows and columns you will be importing data from.

The named range can then be used to import data from Excel to Access

see sample code below for importing data from a spreadsheet

Sub ImportExcel()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table", "File", True, "Range"
End Sub