Solved Import 3Excel files in a table and make a copy of them (1 Viewer)

mansied

Member
Local time
Today, 02:16
Joined
Oct 15, 2020
Messages
99
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

I’m here to help
Staff member
Local time
Yesterday, 23:16
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 02:16
Joined
Oct 15, 2020
Messages
99
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

Active member
Local time
Today, 06:16
Joined
Dec 31, 2020
Messages
521
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

Member
Local time
Today, 02:16
Joined
Oct 15, 2020
Messages
99
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

I’m here to help
Staff member
Local time
Yesterday, 23:16
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 02:16
Joined
Oct 15, 2020
Messages
99
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

I’m here to help
Staff member
Local time
Yesterday, 23:16
Joined
Oct 29, 2018
Messages
21,358
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?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:16
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 02:16
Joined
Oct 15, 2020
Messages
99
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

I’m here to help
Staff member
Local time
Yesterday, 23:16
Joined
Oct 29, 2018
Messages
21,358
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom