SQL code for totalling multiple tables

[Nevsky78]

Hi all,

I was wondering if anyone had an example code for importing multiple Excel sheets (i.e. Customer One, Customer Two etc) with exactly the same layout (up to 50 sheets) into 1 table in Access, without having to use 50 import steps?

Each Excel sheet resides in a separate Excel file.

All the tables include are: Items Ordered across Jan, Feb, Mar etc

There are other threads about merging multiple tables once they are already in Access but I'd like to combine them all on import in one step!

Thanks,
Nick

 

[Trevor G]

Nick here is some code which I use to import a single spreadsheet into a table. You will have to replace the path and ensure all the workbooks are in the folder.


Function ImportallExcel()

Dim myfile
Dim mypath
mypath = "n:\importxls\Trevor G\"
Do
myfile = Dir(mypath & "*.xls")
'this will import ALL the excel files (one at a time, but automatically) in this folder. Make sure that's what you want.
DoCmd.TransferSpreadsheet acImport, 8, "Table Name", mypath & myfile
myfile = Dir
Loop Until myfile = ""

End Function

 

[Nevsky78]

Hi Trevor,

Thanks so much for getting back to me with that. Let me give it a go and get back to you with the results!

Kind Regards,
Nick

 

[Nevsky78]

Hi again Trevor,

Is there any way I can include in the code which Sheet from the Excel file I would like to import?

Nick

 

[Nevsky78]

I also get a Run-time error '2391'

Field 'F1' doesn't exist in destination table 'Customer Forecast Totals'

Public Function ImportallExcel()
Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
Do
myfile = Dir(mypath & "*.xlsx")
DoCmd.TransferSpreadsheet acImport, 8, "Customer Forecast Totals", mypath & myfile
myfile = Dir
Loop Until myfile = ""
End Function

 

[Trevor G]

When you look at the code if you add in a new docmd and begin to repeat the same as above you will eventually get to the part which asks for a named range, this is where you would need to add the sheet name (I think).

As for the F1 this means field name, so your table you are importing to must have exactly the same names as per your table in Excel. I am assuming you are starting from cell a1 as well.

I think once you look a this you may realise as well that it will possible continue looping so you might also want to consider deleting the workbooks as it imports them maybe through a kill command?

 

[Nevsky78]

I think I'm being very thick here - I'm not actually too au fait with VBA/SQL

I need to create the DoCmd.TransferSpreadsheet, don't I?

I put this in, and it came up with a Macro prompt box

Public Function ImportAllExcel()
Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
Do
myfile = Dir(mypath & "*.xlsx")
DoCmd.TransferSpreadsheet acImport, 7, "Customer One", mypath & myfile
DoCmd.TransferSpreadsheet acImport, 7, "Customer Two", mypath & myfile
DoCmd.TransferSpreadsheet acImport, 7, "Customer Three", mypath & myfile
myfile = Dir
Loop Until myfile = ""
End Function

 

[Nevsky78]

I think I've got somewhere with this but it keeps looping! I don't want to delete the 'feed' sheets so do you have any suggestions to kill the loop once it has run all the imports? IN my test scenario, I have 3 tables.

Public Function ImportAllExcel()
Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
Do
myfile = Dir(mypath & "*.xlsx")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Customer Totals", mypath & myfile
myfile = Dir
Loop Until myfile = ""
End Function

 

[Trevor G]

I thought it might continue to loop.

At work I have additional code which will deal with that, but for now I would suggest that you look to copy all the books into a temp folder and as it loops through the temp folder you can then look to delete the workbooks by using a kill command. Please look at this code

'Uncomment out the next code step if you want to delete the file after it's imported
' Kill strPathFile
strFile = Dir()

 

[Nevsky78]

Hiya Trevor,

I'm not entirely sure where to put it! I really am a VBA simpleton.

Public Function ImportAllExcel()
Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
Do
myfile = Dir(mypath & "*.xlsx")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Customer Totals", mypath & myfile
myfile = Dir
Loop Until myfile = ""
Kill strPathFile
strFile = Dir()
End Function

I would probably set up a batch file to copy these files overnight.

Nick

 

[Trevor G]

Public Function ImportAllExcel()
Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
Do
myfile = Dir(mypath & "*.xlsx")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Customer Totals", mypath & myfile
myfile = Dir
Loop Until myfile = ""
Kill strPathFile
strFile = Dir()
End Function

Change this as follows

Public Function ImportAllExcel()
Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
Do
myfile = Dir(mypath & "*.xlsx")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Customer Totals", mypath & myfile
Kill MyFile
Loop Until myfile = ""
mypath = Dir()
End Function

 

[Nevsky78]

Hi Trevor,

Thanks again for taking the time to help.

I've pasted that in exactly, but I'm now getting a "Run-time error '53' - File not found"!

Nick

 

[Nevsky78]

...but I have worked out how to direct it to a specific sheet though. I've pasted it below for reference if someone comes across the same problem.

Public Function ImportAllExcel()
Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
Do
myfile = Dir(mypath & "*.xlsx")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Customer Totals", mypath & myfile, , "FC!"
Kill myfile
myfile = Dir
Loop Until myfile = ""
'mypath = Dir()
End Function

Where "FC!" is the tab name

 

[Trevor G]

...but I have worked out how to direct it to a specific sheet though. I've pasted it below for reference if someone comes across the same problem.

Public Function ImportAllExcel()
Dim myfile
Dim mypath
mypath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
Do
myfile = Dir(mypath & "*.xlsx")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Customer Totals", mypath & myfile, , "FC!"
Kill myfile
myfile = Dir
Loop Until myfile = ""
'mypath = Dir()
End Function

Where "FC!" is the tab name

Nick I have looked through another example in another database I have and this seems to work ok

Sub ImportAllExcelFiles()
On Error GoTo Err_F
Dim strPathFile As String, strFile As String, strPath As String, strSpec As String
Dim strTable As String, ynFieldName As Boolean
ynFieldName = False
strPath = "M:\Access Files\Import Multiple Books\"
'strSpec = "NameOfImportSpecification" ' Put your name here
strTable = "sheet1"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, True 'ynFieldName
' Uncomment out the next code step if you want to delete the file after it's imported
Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub
Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F
End Sub

 

[Nevsky78]

Thanks Trevor!

I've altered it for my purposes, but not sure about the

'strSpec = "NameOfImportSpecification" ' Put your name here

line

Should that be

'strSpec = "Customer Totals"

Like this:

Sub ImportAllExcelFiles()
On Error GoTo Err_F
Dim strPathFile As String, strFile As String, strPath As String, strSpec As String
Dim strTable As String, ynFieldName As Boolean
ynFieldName = False
strPath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
'strSpec = "Customer Totals"
strTable = "FC!"
strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, strPathFile, True 'ynFieldName
Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub
Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F
End Sub

 

[Nevsky78]

OK, slowly getting there! The code below now runs, but it only imports the data from 1 sheet. Have you got any suggestions why this might be the case?

Sub ImportAllExcelFiles()
On Error GoTo Err_F
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strPath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
strTable = "Customer Totals"
strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
            strTable, strPathFile, blnHasFieldNames, "FC!"
strFile = Dir()
Loop
Exit_F:
Exit Sub
Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F
End Sub

 

[Nevsky78]

I got it to work perfectly! Thanks so much Trevor! Here's the code for reference. You also need to remember to delete the existing Totals table in Access.

Sub ImportAllExcelFiles()
On Error GoTo Err_F
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strPath = "C:\Documents and Settings\nick\My Documents\Forecasts\"
strTable = "Customer Totals"
strFile = Dir(strPath & "*.xlsx")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
            strTable, strPathFile, blnHasFieldNames, "FC!"
'Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub
Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F
End Sub

 

[Trevor G]

Excellent news Nick.

Pleased to help you.