Import from Excel stops on unvalid column/field name is there a way around

Question

Hi

An external system provides Excel files. One of the columns in the Excel sheet has the name "Ex. moms".

The dot (.) stops the TransferSpreadsheet acImport since I am not able to create a field name in Access with a dot.

But I do not need the data contained in the column "Ex. moms". Is there a way around?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "NYTTLEVPORTALUNDERLAG", strFolder3 & "NYTTLEVPORTALUNDERLAG" & ".xlsx", True

---

Best // Peter Forss Stockholm GMT +1.00

Answer 1

If I try to do this, I don't get an error message. The field name is automatically changed to "Ex# moms":

But does it work for you if you use acLink instead of acImport? If so, you could then use a query to transfer the data to NYTTLEVPORTALUNDERLAG.

As an alternative, you could automate Excel from VBA to change the column header before importing data into Access.

Or use ADO to open a recordset on the Excel sheet...

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Hi Hans and Thanks

I created a table with one of the field named as "Ex# moms". Then there is no issues with the acImport.

Also tried the acLink and it works good too.

But I will stay to acImport this time.

Thank you very much!

---

Best // Peter Forss Stockholm GMT +1.00

Answer 3

I do not import directly form Excel.  I link to the spreadsheet and use append queries to import the datainto an existing table.  I also can do validation queries before I append the data.

Answer 4

Hey there, Chris.  I think this is what you want.

    1) Open Access and create a new blank database.
    2) Go to File | Get External Data | Link Tables.
    3) Navigate to the Student Data File in your Excel folder.
    4) Select the Worksheet.
    5) Click Next twice.
    6) Click Finished.

Also, you can append the data form Excel to Access.

Dim db As Database
Dim rs As Recordset
Dim ssql As String


Set db = OpenDatabase("C:\seq.xls",

False, False, "Excel 8.0")

'if you need to create the table as part of the import
ssql = "SELECT * INTO test IN 'c:\seq.mdb' FROM [sheet1$]"


db.Execute ssql

db.Close
Set db = Nothing 

   

Finally, see this link.

http://allenbrowne.com/casu-19.html

---

MY BOOK