Solved Import 3Excel files in a table and make a copy of them

[mansied]

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

 

[theDBguy]

How can I add Delima or import specification to change the data type in importing for excel file ???

I think import specs are only for text files. For Excel, create a table structure first and then import your spreadsheet data into it.

 

[mansied]

I think import specs are only for text files. For Excel, create a table structure first and then import your spreadsheet data into it.

thanks for your advices ,So I can add

Dim td As TableDef

so how can I call it to be filled with my excel files ?

 

[Cotswold]

As far as I know you can import an Excel file from <External Data>, <From File>, <Excel>
You can import and Create a table or link the sheet (Access2010 to 2019)

 

[mansied]

As far as I know you can import an Excel file from <External Data>, <From File>, <Excel>
You can import and Create a table or link the sheet (Access2010 to 2019)

My question was related to the VBA coding of this process . make it Automatically.

 

[theDBguy]

thanks for your advices ,So I can add

Dim td As TableDef

so how can I call it to be filled with my excel files ?

You probably don't need a TableDef. Just use the name of the table you created in your TransferSpreadsheet command.

 

[mansied]

You probably don't need a TableDef. Just use the name of the table you created in your TransferSpreadsheet command.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA", varFile, True, "Report Details!"
DATA is my table name

 

[theDBguy]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA", varFile, True, "Report Details!"
DATA is my table name

And did you specify the data types in the design of your DATA table?

 

[mansied]

And did you specify the data types in the design of your DATA table?

yes i changed it to long text

 

[theDBguy]

yes i changed it to long text

Then it should work, I think. What is the problem now? If you specify the data type, then the data from Excel should be automatically converted.

 

[mansied]

I found the issue
in this line

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA", varFile, True, "Report Details!"

I changed the excel type to

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "DATA1", varFile, True, "Report Details!"
and the import format is the same as excel that I want it.
Thanks all

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.