Excel VBA Exporting CSV

[joes2007]

I currently have and use some code that exports a range as a CSV. It has done everything I need up until now. I need to export data that contains commas. I need to export the data as a .CSV for the next step of my operation, but I need it delimited by something other than a comma.

Does anyone have any thoughts?

 

[wideboydixon]

You can put quotes around values to allow commas to be exported:

a,b,c,"d,e",f,g,"h,i",j

Book1
	A	B	C	D	E	F	G	H
1	a	b	c	d,e	f	g	h,i	j
test

WBD

 

[joes2007]

Sorry, here is the code.

Private Sub sh05_print_Click()


Call unlocksheet


'to export data to csv -- ends with calling the 'clear' sub


Dim TextFile As Integer
Dim FilePath As String
Dim record_num As Integer
Dim record_count As Integer
Dim column_num As Integer
record_count = Range("aa4").Value
FilePath = "C:\Bartender Commander Folder\SH_05_PART_LABELS.csv"
TextFile = FreeFile
Open FilePath For Output As TextFile
For record_num = 1 To record_count
        For column_num = 1 To 8
            If column_num = 8 Then
                Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TextFile]#TextFile[/URL] , Range("aa7:af10").Cells(record_num, column_num).Value
            Else
                Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=TextFile]#TextFile[/URL] , Range("aa7:af10").Cells(record_num, column_num).Value,
            End If
        Next column_num
Next record_num
Close TextFile




Call locksheet


Call sh05clear_Click
End Sub

 

[joes2007]

I had a cell that was performing a vlookup and the return had a comma in it. I could not get concat to work with double quotes. This ended up working: CONCATENATE(char(34); B2 ;char(34))

Thank you for your help!

 

[joes2007]

Problem not solved! The delimiting problem is but a new one is now here. This data gets embedded into a 2d barcode, later scanned and delimited by power query. Power query is separating ["test, inc"] into ["test] and [inc"].

Thoughts?

 

[joes2007]

The barcode is generated by BarTender, I will have it encode ; as the delimiter and then power query delimit by that. Thanks all!

 

[wideboydixon]

Haha. Seems you got it sorted yourself. Good work.

WBD