Change fields names when importing (1 Viewer)

[Etxezarreta]

Hello everyone,
In order to import datas form an excel file, I use an intermediate Access, and the code below:
it works fine, except when the field names contain special characters. So i would like to change the field names while importing: what would be the best strategy? Creating a data set with SELECT As ?
Many thanks in advance.
Etxe.

Dim app As New Access.Application

Dim strPathLocal As String
Dim strPathBaseIntermed As String
Dim strPathBaseBE As String
Dim strPathBaseIntermedTemplate As String
Dim strFichierExcel_Affaires As String

Dim sDest As String
Dim sSource As String

On Error GoTo errsub

strPathLocal = CurrentProject.Path & "\"

strPathBaseIntermed = DLookup("[PathBE]", "t_PathBE", "[Id_ligne] = 1") & "\BaseIntermediaire\"
strPathBaseBE = DLookup("[PathBE]", "t_PathBE", "[Id_ligne] = 1") & "\"
Debug.Print strPathBaseIntermed
strPathBaseIntermedTemplate = DLookup("[PathBE]", "t_PathBE", "[Id_ligne] = 1") & "\BaseIntermediaireTemplate\"
Debug.Print strPathBaseIntermedTemplate
strFichierExcel_Factures = strPathLocal & "ExtractionsVentes\EXTRACTION FACTURES.xlsx"
strFichierExcel_BonCommandes = strPathLocal & "ExtractionsVentes\EXTRACTION COMMANDES EN COURS.xlsx"
strFichierExcel_BonLivraisons = strPathLocal & "ExtractionsVentes\EXTRACTION BON DE LIVRAISON.xlsx"

'supprime la tbase intermediaire
If Dir(strPathBaseIntermed) <> "" Then
    Kill strPathBaseIntermed & bddinter
    sSource = strPathBaseIntermedTemplate & bddinter
    sDest = strPathBaseIntermed & bddinter
    FileCopy sSource, sDest
Else
End If
'
'FileCopy sSource, sDest
'---------------chargement table Excel
' ouvre l'appli access
Set app = CreateObject("Access.application")
app.Visible = False
'ouverture du fichier asccdb
app.OpenCurrentDatabase strPathBaseIntermed & bddinter, False
'app.OpenCurrentDatabase strPathBaseBE & bddBE, False
'Creation du fichier accdb
'app.NewCurrentDatabase strPathBaseIntermed & bddinter

app.DoCmd.TransferSpreadsheet acImport, , tablename:=tblSource_Affaires, filename:=strFichierExcel_Factures, hasfieldNames:=True

'app.DoCmd.DeleteObject acTable, tblSource
app.Quit
'---------------attachement de la table intermediaire
'DoCmd.TransferDatabase acLink, "Microsoft Access", strPathBaseIntermed & bddinter, acTable, tblSource, "VENTES_EXTRACTIONS_tListeVentesTroisAnnees"
Exit Sub
errsub:
    MsgBox Err.Number & " -- " & Err.Description
  
End Sub

 

[June7]

Either change the column headers in Excel file or use a different method than TransferSpreadsheet. Reviewhttp://accessmvp.com/KDSnell/EXCEL_Import.htm#WriteFileRst

 

[isladogs]

Or link to your Excel file so it is a linked table in Access.
Now use a query or VBA procedure to import to a local Access table using Replace expressions to replace any special characters as part of the process.
If you have a lot of special characters you may find the ReplaceBadCharacters function by @MajP helpful. See post #284 in this very lengthy thread
Solved - Multiple search engine with dropdowns | Page 15 | Access World Forums (access-programmers.co.uk)

 

[Pat Hartman]

Define the table with the column names the way you want.
Link to the spreadsheet instead of importing it
Create an append query that maps the linked table to the one with the good names.

No code is required.

If the column names in Excel are so bad that Access won't allow them. you can say the table doesn't have column names and then in the query, use criteria to ignore the headings row. Mapping is a little harder since all the Excel columns will have bogus names but if you do the columns in the same order, it will be easy enough.