Macro in MS Access to import multiple Excel sheets with variable names

[Sepop]

Hi,

I am trying to create a macro to automatically import several Excel reports into my data base in Access.

The names have some similarities (I use this similarities for another macro I use that depending on the file name it gives the proper format to the file) for example dependig if the data is Year, Period or Month to data (YTD, MTD or PTD) the name contains this, and depending on the file type it has PPS or HC in the name, but there are some things that change on the name from month to month like the date.

I need a macro to look for these files into an specific folder and bring import the information into Access.


Thanks!
Sebastian

 

[TerryHogarth21]

Everything you need about importing from Excel to Access
EXCEL Import

 

[Sepop]

Everything you need about importing from Excel to Access
EXCEL Import

Thanks a lot!

I want to use this code but not sure what should I put at the beginning and the end (lik Sub and End Sub for excel macros) and how can I lunch this macro...

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:\Documents\"

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

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​

​

​

 

[TerryHogarth21]

In Access Press Alt + F11, Insert on Ribbon -> Module. Copy and Paste the below in that module.

I'm going to warn you that you have to follow and replace the variables to what you need to for strPath and strTable.

Sub Import
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:\Documents\"

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

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
​

You can run it from the module itself by clicking anywhere in the code and pressing the play button in the ribbon, or you can put it into a form with a button that can run that code.


​

 

[Sepop]

Thanks a lot! It worked perfectly!

Now I want to go a little further (if possible):

In the folder I selected I have to different types of reports, ones called PPS and another ones called SAP HC. I wanted to know if there is a way we can add an If formula (or something like that) to that code that if the excel file name contains "PPS" in its name the information will be uploaded to the PPS table or if it contains "SAP HC" it will uploaded to the SAP HC table.

Thanks a lot!

 

[lolster]

Hi,
I have a simular problem just the other way around. I would like to import several Access files at the same time in Excel (but only one table inside a file). Is this anyhow possible?
Br

 

[silentwolf]

Hi loster,
you should start a new thread in access )

 

[lolster]

ok