Hello
I am new in VBA,
I have a scenario to import 3 excel files that have the same sheet name ( all 3 have "Report details" sheet) , I want to open a dialog box and choose 3 excel files at the same time and then make a copy in a folder of them .and then import all 3 sheets in ONE table . what I did is in below . Can you help me to correct it to work?
How can I add Delima or import specification to change the data type in importing for excel file ???
Thanks a lot for your help
Private Sub Command120_Click()
DoCmd.SetWarnings False
Dim Name As String
Dim varFile As Variant
Dim diag As Office.FileDialog
Dim item As Variant
//open a dialog box o choose 3 files
Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = True
diag.Title = "Please Select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheets", "*.*, *.xlsx,, *.xls"
If diag. Show Then
For Each item In diag.SelectedItems
Me.TextBox = item
Debug.Print i
For Each varFile In .SelectedItems
Name = varFile
FileName = Dir(varFile)
//copy in a folder
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Call oFSO.CopyFile(Name, Application.CurrentProject.Path & "\NewExcel\", True)
FSO_FileCopy = True
// import in a table ????/ how can add delima or import specification to change datatype ??????
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA", varFile, True, "Report Details!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA1", varFile, True, "Report Details!"
Next
End If
End Sub
I am new in VBA,
I have a scenario to import 3 excel files that have the same sheet name ( all 3 have "Report details" sheet) , I want to open a dialog box and choose 3 excel files at the same time and then make a copy in a folder of them .and then import all 3 sheets in ONE table . what I did is in below . Can you help me to correct it to work?
How can I add Delima or import specification to change the data type in importing for excel file ???
Thanks a lot for your help
Private Sub Command120_Click()
DoCmd.SetWarnings False
Dim Name As String
Dim varFile As Variant
Dim diag As Office.FileDialog
Dim item As Variant
//open a dialog box o choose 3 files
Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = True
diag.Title = "Please Select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheets", "*.*, *.xlsx,, *.xls"
If diag. Show Then
For Each item In diag.SelectedItems
Me.TextBox = item
Debug.Print i
For Each varFile In .SelectedItems
Name = varFile
FileName = Dir(varFile)
//copy in a folder
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Call oFSO.CopyFile(Name, Application.CurrentProject.Path & "\NewExcel\", True)
FSO_FileCopy = True
// import in a table ????/ how can add delima or import specification to change datatype ??????
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA", varFile, True, "Report Details!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA1", varFile, True, "Report Details!"
Next
End If
End Sub