issue with automating excel import to access

[graveyard]

good morning all, i have an issue with automating excel import to access.
ive got a link on how to do it (sorry i cant show link as i have less than 10 post count) but still couldnt get it working

what i am trying to do
1. Import 1 worksheet called "Input" from 2 excel files called "HKG.xls" and "SIN.xls" to a table called "Excel_Import" in access

2. Both excel files are in a sub folder called "MAA/MAAA" on C drive

3. Tag the following code to Command38 to activate import upon click

----------------------------------------------------------------

Private Sub Command38_Click()
 
Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer
 
' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
Dim strWorksheets(1) As String
 
' Replace 3 with the number of worksheets to be imported
' from each EXCEL file (this code assumes that each worksheet
' with the same name is being imported into a separate table
' for that specific worksheet name)
Dim strTables(1) As String
 
' Replace generic worksheet names with the real worksheet names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
 
strWorksheets(1) = "Input(1)"
 
' Replace generic table names with the real table names
strTables(1) = "Excel_Import(1)"
 
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
 
strPath = "C:\MAA\MAAA"
 
' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
For intWorksheets = 1 To 1
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTables(intWorksheets), _
strPathFile, blnHasFieldNames, _
strWorksheets(intWorksheets) & "$"
strFile = Dir()
Loop
Next intWorksheets
 
End Sub

-----------------------------------------------------------------------Can anyone enlighten me why this doesnt work when i click on the button (there is no error message, it just doesnt load)

Ive tried transhferspreadsheet on macro builder - pls see attached. I used a make table query at the end to add in some calculated fields that i need to include. however, i realize this method returns me huge multiples of duplicates and i suspect it has to do with the "range" setting (right after "has field names" at the bottom of the screen. I left the range blank as i wanted the macro to capture every details in the spreadsheet but obviously this is not working

Pls assisit. Thanks!!

 

[Beetle]

Looks like you're slightly misunderstanding how the code works, but it also looks like the code needs a little modification to work for your scenario. Assuming both worksheets have the same number of columns and the first row of each worksheet contains field names, the following modified code should work;

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim i As Integer
 
' we're only importing to 1 table
strTable = "Excel_Import"
 
' declare variable for (2) Workbook names.
Dim strWorkbooks(2) As String
 
' declare variable for (2) Worksheet names.
Dim strWorksheets(2) As String
 
' assign the actual Workbook names.
strWorkbooks(1) = "HKG.xls"
strWorkbooks(2) = "SIN.xls"
 
' assign the actual Worksheet names
strWorksheets(1) = "Input"
strWorksheets(2) = "Input"
 
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
 
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\MAA\MAAA\"
 
' Create loop to import from the 2 different Workbooks
For i = 1 To 2
strFile = Dir(strPath & strWorkbooks(i))
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, _
strPathFile, blnHasFieldNames, _
strWorksheets(i) & "$"
strFile = Dir()
Loop
Next i

 

[graveyard]

Looks like you're slightly misunderstanding how the code works, but it also looks like the code needs a little modification to work for your scenario. Assuming both worksheets have the same number of columns and the first row of each worksheet contains field names, the following modified code should work;

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim i As Integer
 
' we're only importing to 1 table
strTable = "Excel_Import"
 
' declare variable for (2) Workbook names.
Dim strWorkbooks(2) As String
 
' declare variable for (2) Worksheet names.
Dim strWorksheets(2) As String
 
' assign the actual Workbook names.
strWorkbooks(1) = "HKG.xls"
strWorkbooks(2) = "SIN.xls"
 
' assign the actual Worksheet names
strWorksheets(1) = "Input"
strWorksheets(2) = "Input"
 
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True
 
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\MAA\MAAA\"
 
' Create loop to import from the 2 different Workbooks
For i = 1 To 2
strFile = Dir(strPath & strWorkbooks(i))
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, _
strPathFile, blnHasFieldNames, _
strWorksheets(i) & "$"
strFile = Dir()
Loop
Next i

It worked! thanks, Beetle!! Appreciate your help!