Search and Replace before DoCmd.TransferSpreadsheet via VBA

[w99t]

Hello,

as I already mentioned in my introduction, I am working on an Access database at work.
There is a problem I need help with:

I get an Excel spreadsheet from another department which contains dots in some column headlines which give an error when importing via DoCmd.TransferSpreadsheet.
Is there a way to search and replace those dots before importing the Excel file?
Or do you have another recommondation how to solve this problem?


Thanks for your help in advance and have a nice day,

Tim

 

[Orthodox Dave]

In the command:

DoCmd.TransferSpreadsheet( ** TransferType, ** SpreadsheetType, ** TableName, ** FileName, ** HasFieldNames, ** Range, ** UseOA** )

the HasFieldNames element is set to True if you want the first row to be used as field names. If you make this False (or leave blank as False is the default), Access will create the table with field names from "F1" to "F10" (however many there are). The headings will then be saved as Record 1.

If this is something you do occasionally, you could then just change the field names manually.

If you need to do this programmatically, you would use a table recordset, go to the first record, and extract the values of each field to string variables. You can then parse each of these to remove the dots (using InStr, Left, Right, Mid functions). Following this you would use the parsed variables to rename the field names (there's help here: https://access-programmers.co.uk/forums/showthread.php?t=173179).

You'll then need to delete record 1 of course.