Using " and , and ; in macro

RichardSchollar said:

Hi

Are you saving the file down as a csv using SaveAs? If so, you end up with those 'helpful' tags. You need to amend the way you create the file to eg:

Code:

Dim i as Integer, strToWrite As String
Dim strFileName As String
 
i = FreeFile
strFileName = "C:\YourFolder\YourFileToWriteTo.csv"
 
strToWrite = ActiveSheet.Range("A1").Value
 
Open strFileName For Binary Access Write As #i
  Put #i, , strToWrite
Close #i

Click to expand...

RichardSchollar said:

Here's a pair of VBA functions you could use to do this:

Code:

Function WriteText(ByVal strPathAndFilename As String, ByRef strToWrite As String, Optional ByVal blnOverWrite As Boolean = False) As Boolean
Dim i As Integer, strTemp
strTemp = Dir(strPathAndFilename)
If Len(strTemp) > 0 Then
    If blnOverWrite Then
        Kill strPathAndFilename
    Else
        WriteText = False
        Exit Function
    End If
End If
i = FreeFile
Open strPathAndFilename For Binary Access Write As #i
    Put #i, , strToWrite
Close #i
WriteText = True
End Function
 
Function MakeText(ByRef rng As Range, Optional ByVal strDelim As String = ",", Optional ByVal strNewLine As String = vbCrLf) As String
Dim varArray As Variant
Dim i As Long, j As Long
Dim strTemp As String
 
If rng.Count = 1 Then
    MakeText = rng.Value
    Exit Function
Else
    varArray = rng.Value
    For i = 1 To UBound(varArray, 1)
        For j = 1 To UBound(varArray, 2)
            strTemp = strTemp & varArray(i, j) & strDelim
        Next j
        strTemp = Left(strTemp, Len(strTemp) - 1) & strNewLine
    Next i
    strTemp = Left(strTemp, Len(strTemp) - 1)
    MakeText = strTemp
End If
End Function

Use in code like:

Code:

Sub WriteMySheet()
Dim strMyTextIWantToWrite as string, strFile as string
strMyTextIWantToWrite = maketext(activesheet.usedrange,",")
strFile = "C:\SomeFolder\SomeFile.csv"
WriteText strFile, strMyTextIWantToWrite,True
End Sub

Click to expand...