Solved Invisible Characters from Excel

[pooldead]

I have a spreadsheet report coming from an application. I do some VBA manipulation in Excel from Access to the file, then import the data to Access. One of the manipulation steps I have Access perform is to add in a column to the Excel file called "Key" (see code below). However, I get the following error when I try to import the data. I do not tell Excel to add in those 3 special characters and when I check the file itself, those characters are not part of that column header.

How can I keep those special characters from supposedly being added?

.Columns("A:A").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow
    .Range("A1").FormulaR1C1 = "Key"

 

[Isaac]

Maybe the problem is in the original column A - the one where the insert (to create the new column A) is occurring. Does it have a weird format or extra character there that is being perpetuated via the CopyOrigin argument? Just thinking out loud here

 

[pooldead]

I did think of that and tried deleting row 1, then inserting a new row1 and manually adding the headers back in. But it was the same result. I've struggled with these in the past and never found a solution, just came up with some workarounds. This time I'd like to figure out how to get rid of these nuisances.

Or if I can't get rid of those characters, is it possible to do TransferText (for a CSV file) and designate no headers? That way I could just delete the header row in the file and focus on the data?

 

[pooldead]

Okay I remembered why this happens. It's because of the CSV file saves as CSV UTF-8 instead of plain CSV.