Thread: Copy-and-paste issues with period in column label

Hi,

I have a VBA function that simply accepts an Excel file with simple column & row data as input and pastes the result into another.

The problem is that, if one of my column names contains a period, the period (.) in the name gets converted to a pound sign (#). For example, if I abbreviate AMOUNT to AMT. with a trailing period, the result after the paste gives AMT# (trailing pound sign) .

I am using the following VBA subroutine in order to parse the table and paste the result into my final Excel sheet:

' *** Parse the table and write result record set to Excel sheet
Do Until objRecordset.EOF
ActiveSheet.Cells.ClearContents
SheetName = ActiveSheet.Name
Sheets(SheetName).Range("A2").CopyFromRecordset objRecordset
For n = 0 To (objRecordset.Fields.Count - 1)
iCol = iCol + 1
Sheets(SheetName).Cells(1, iCol).Value = objRecordset.Fields(n).Name
iSheetCount = ThisWorkbook.Sheets(SheetName).UsedRange.Rows.Count
Next n
Loop


The following line from the above subroutine is responsible for the final paste of the column name:

Sheets(SheetName).Cells(1, iCol).Value = objRecordset.Fields(n).Name


Any help would be greatly appreciated in order to get around this issue.

Kind regards,
Shawn