Double Quotes In VBA Export

Thorin

Thorin

Board Regular
Joined
Feb 8, 2007
Messages
243
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Hi Andy

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

Kind regards
PGC
 
Upvote 0
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.
 
Upvote 0
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.

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Range to CSV with quotes
Replies
2
Views
88
Bushy3
B
S
  • Question Question
VBA Email Templates
Replies
3
Views
149
Domenic
D
B
  • Question Question
Using VBA to retrieve specific pages from PDFs
Replies
1
Views
205
Big_Al_fae_the_rigs
B
S
  • Question Question
2 questions related to VBA
Replies
4
Views
75
Skyybot
Skyybot
M
  • Question Question
Edit CSV file adding double quote via macro
Replies
4
Views
755
rlv01
R

Forum statistics

Threads
1,224,604
Messages
6,179,857
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top