How to Edit and Re-Use a Saved Import Specification for EXCEL using VBA

[Kody_Devl]

I manually created an import specification for one Excel Spreadsheet. It works perfectly for that one spreadsheet.


How can I use that Import Specification (the same structure) but a different (700 different) Excel.xls names.

I want to do this using vba.


Thanks


Kody_Devl

[ranman256]

you dont really need an import spec for Excel files. Access uses the fields in the spreadsheet.
specs are for text files, where formatting is barbaric.

docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12 ,sTable,sFile,true,tabname

[ssanfu]

to the forum.....

The quick answer, without having more info about your project, is use looping.
Do you already have some code to import the spreadsheet?
Are you creating a new table for each spreadsheet?
Or are you appending the spreadsheet data to an existing table?

[Kody_Devl]

I can do the Looping with recordset and I know how to import using docmd.transferspreadsheet.
Then reason I want to create a Import Specification is that I want to force the first column to Text (It defaults to long) because I am interested in the text values in the number columns. My challenge is to Import the ColumnA as a Text so I don't lose the text values.

I may be able to transferspreadsheet into an existing table that already has the text value? That may be my solution.

Thanks

Kody_Devl

[ssanfu]

You may have to import the Excel into a tmp table, then use an append query move the records to the production table, converting the field to text. And this can be automated (done in VBA).

[Kody_Devl]

To Resolve my Problem, I have a Saved Import Spec for Excel that works perfectly on one (of my 700 Excel Spreadsheets) of my spreadsheets. I
f I could take this spec and edit the file name to the next Excel name and then run using VBA, my problem would be solved!

ie
Original Spec is "C:\Book1.xls"
Can I change it with VBA to
"C:\Book2.xls" and run it again (same spec name with a different file)

I can do this manually (Edit the file name) in the "Manage Data Tasks" form

If the Path and File for the Specification is stored in a system table, that may be the Answer.

[Kody_Devl]

I Found Some Code that will create a Custom Excel Import File Specification and

IT Works: This is very handy for extracting things out of ODD places in Excel

Dim Import_XML As String
Dim Name_of_Spec As String
Name_of_Spec = "mySpec"
'Import Exce as Text all columns
Import_XML = "<?xml version=""1.0""?>" & vbCrLf & _
"<ImportExportSpecification Path=""" & strPathFile & """ xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & vbCrLf & _
" <ImportExcel FirstRowHasNames=""false"" AppendToTable=""zImport_tmp"" Range=""" & strTabs & "$"">" & vbCrLf & _
" <Columns PrimaryKey=""{Auto}"">" & vbCrLf & _
" <Column Name=""Col1"" FieldName="""" Indexed=""NO"" SkipColumn=""false"" DataType=""text""/>" & vbCrLf & _
" <Column Name=""Col2"" FieldName="""" Indexed=""NO"" SkipColumn=""true"" DataType=""text""/>" & vbCrLf & _
" <Column Name=""Col3"" FieldName="""" Indexed=""NO"" SkipColumn=""false"" DataType=""text""/>" & vbCrLf & _
" </Columns>" & vbCrLf & _
" </ImportExcel>" & vbCrLf & _
"</ImportExportSpecification>"

On Error Resume Next
CurrentProject.ImportExportSpecifications.Item(Nam e_of_Spec).Delete
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "zImport_tmp"
DoCmd.SetWarnings True
On Error GoTo 0

'Debug.Print Import_XML

CurrentProject.ImportExportSpecifications.Add Name_of_Spec, Import_XML

' now this actually imports the file
DoCmd.RunSavedImportExport Name_of_Spec

[ssanfu]

I suggested looping, but is was not meaning looping in a recordset. Use code to navigate to a folder, then loop through each of the Excel files. Much easier that editing the code 700 times.

[Vstar920AZ♣]

One of the best signature tags I've seen Veni, Vidi, Risu Ventris

[Vstar920AZ♣]

Thank you, that will give me much more control of my import specs!! Been looking for something like this for a while.
I ran into the Line Continuation max . . . and so if anyone else does as well I used what I do with large SQL statements in code:
Import_XML = "<?xml version='1.0' encoding='utf-8' ?>"
Import_XML = Import_XML & "<ImportExportSpecification Path = 'C:\LoadFiles\MyLoadFile.xlsx' xmlns='urn:www.microsoft.com/office/access/imexspec'>"
Import_XML = Import_XML & "<ImportExcel FirstRowHasNames='true' Destination='MyLoadTable' Range='Sheet1$' >"
Import_XML = Import_XML & "<Columns PrimaryKey='{Auto}'>"
Import_XML = Import_XML & "<Column Name='Col1' FieldName='DocumentNumber' Indexed='NO' SkipColumn='false' DataType='Text' />"
Import_XML = Import_XML & "<Column Name='Col2' FieldName='Folder' Indexed='NO' SkipColumn='false' DataType='Text' />" . . . I had about 30 columns.

[isladogs]

Have a look at my utility which allows you to edit any existing saved import/export data task specification. No code required
View & Edit IMEX Data Tasks (isladogs.co.uk)

EDIT just realised this is a 6 year old thread.

[Welshgasman]

Have a look at my utility which allows you to edit any existing saved import/export data task specification. No code required
View & Edit IMEX Data Tasks (isladogs.co.uk)

EDIT just realised this is a 6 year old thread.

Appears to be still useful?