Double Quotes In VBA Export

[Thorin]

I have some VBA code that exports data into a CSV file, it all works Ok, but the alphanumeric code needs to have double quotes on either side of the field, eg "SK1", instead of just SK1.

I either end up with no quotes or too many, I have tried using Chr(34) on either side of my field, eg Chr(34) & ctype & Chr(34). With this I have one double quote before the field but three after it.

Its driving me nuts, can anyone help please.

Regards
Andy

 

[pgc01]

Hi Andy

Can you post the part of the code that you use to output the data?

Kind regards
PGC

 

[Thorin]

ctype = Sheets("Setup").Range("Charge_Type")

Cells(iRow, 1).Value = Chr(34) & ctype & Chr(34)

ActiveWorkbook.SaveAs Filename:=myPath & NomCode & "Bulk" & yrPath & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close SaveChanges:=True


In this case the ctype is a constant of the letter C, I need it to output to the CSV file as "C". There are other fields that are true variables, but the principle is the same.

 

[pgc01]

Hi again

You are trying to export the data using SaveAs.

I may be wrong, but I think that, unfortunately, there is no way to solve your problem using any automatic way of exporting data. Excel will always mess with the double quotes.

I believe the only way is for you to write your own exporting macro.

This is a simple example that exports a range (A1:C6), comma delimeted. This code gives you complete control of what to output.

Sub Testa()
Dim rPrint As Range, rRow As Range, rCell As Range
Dim sRow As String, FileNumber As Integer
    
Set rPrint = Range("A1:C6") ' Range to export

FileNumber = FreeFile
Open "MyData.txt" For Output As #FileNumber    ' Create file

For Each rRow In rPrint.Rows
    sRow = ""
    For Each rCell In rRow.Cells
        sRow = sRow & rCell.Text & ","
    Next rCell
    Print #FileNumber, Left(sRow, Len(sRow) - 1)
Next rRow

Close #FileNumber    ' Close file
End Sub

Hope this helps
PGC