Change fields names when importing

[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)