VBA-export sheets to .csv without double quotes...

[RobbieC]

Hi there, I have been using this script successfully to export data from a sheet to csv (.txt):

Sub CSVExport()


    Dim var1 As String
    Dim var2 As String
    Dim var3 As String
    Dim var4 As String
    
    'Find the last row that contains data
    With Worksheets("Sheet1")
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).row
    End With
    
    'Setting the name and the path of text file based on workbook path
    sFName = ThisWorkbook.Path & "csvoutput.txt"
        
    'Get an unused file number
    intFNumber = FreeFile
    
    'Create a new file (or overwrite an existing one)
    Open sFName For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFNumber]#intFNumber[/URL] 
        
    For lCounter = 1 To lLastRow


     'Read specific data from the worksheet
        With Worksheets("Sheet1")
            var1 = .Cells(lCounter, 1)
            var2 = .Cells(lCounter, 2)
            var3 = .Cells(lCounter, 3)
            var4 = .Cells(lCounter, 4)
        End With
        
        'Write selected data to text file
        Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFNumber]#intFNumber[/URL] , var1, var2, var3, var4
        
    'Continue looping until the last row
    Next lCounter
    
    'Close the text file
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFNumber]#intFNumber[/URL] 
   
End Sub

However, this script inserts double quotes around each "item" - I need to export the file without double quotes... Is there any way to adapt my above script to do this or will I have to look for a new solution?

If you can point me in the right direction, I'd be most grateful

Thanks

 

[sameer7089]

Ah, there was a missing '\' in the output line... try this:

Sub CSVExport()


On Error Resume Next


Application.ScreenUpdating = False


SheetName = ActiveSheet.Name
Sheets("Sheet1").Activate
'Sheets("ImportExport").Range("A1:E100").Select
'Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete


    Dim field1 As String
    Dim field2 As String
    Dim field3 As String
    Dim field4 As String
   
    Dim sFName As String
    Dim intFNumber As Integer
    Dim lCounter As Long
    Dim lLastRow As Long
   
    With Worksheets("Sheet1")
        lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
   
    sFName = ThisWorkbook.Path & "\csvOutput.txt"
    intFNumber = FreeFile
    Open sFName For Output As #intFNumber
       
    For lCounter = 1 To lLastRow
        With Worksheets("Sheet1")
            field1 = .Cells(lCounter, 1)
            field2 = .Cells(lCounter, 2)
            field3 = .Cells(lCounter, 3)
            field4 = .Cells(lCounter, 4)
        End With
        Write #intFNumber, field1, field2, field3, field4
    Next lCounter
    Close #intFNumber
   
    'Sheets("Sheet1").Cells.Clear


    Sheets(SheetName).Activate


    Application.ScreenUpdating = True
 
End Sub

Hi,, Yes, this worked successfully. Thank you so much for your help..

 

[RobbieC]

No problem, glad I could help out!