Save CSV files without adding empty line at the end?!?

[ramkrau]

Hi all!

I've got a workbook with a macro that will export my various tabs, each as separate CSV files, and save them into a specified folder. The problem I'm having is that the files get saved with a blank line at the end. In order to use the files in another system, I have to open each one in a notepad editor, delete the line, and then save the file. I'm 100% sure something like this could be automated or fixed, but I'm 0% sure how to do it... can anyone help?

My macro is below (with some edits to protect confidential information ).

Thank you!!!!!!!

Sub ExportCSVs()

'Prevent the screen from flickering and speed up code.
Application.ScreenUpdating = False

'Yes/No Box to make sure the user wants to run the macro.
CarryOn = MsgBox("Do you want to export CSV files?" & vbCrLf & "This will also save your workbook.", vbYesNo, "Export CSV Test Files")
If CarryOn = vbYes Then

'Yes/No Box For Importing Resources
CarryOn = MsgBox("Do you want to import the latest export file?", vbYesNo, "Import Latest Export?")
If CarryOn = vbNo Then
GoTo Skip_Resources
End If

'Settings to get started and enable functions further down.
Dim Sht As Worksheet
Dim ThisSheet As Worksheet
Set ThisSheet = ActiveSheet
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook

'Prevents Dialogue Boxes
Application.DisplayAlerts = False

'Import Latest Export File
Call ImportCSVExport.ImportDatafromotherworksheet

'Prevent the screen from flickering and speed up code. This is needed again if the user clicks yes to import the Taxonomy export.
Application.ScreenUpdating = False

Skip_Resources:

'Enable dialogue boxes
Application.DisplayAlerts = True

'More settings to get enable functions
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String
Set xWb = Application.ThisWorkbook

'Setting up and creating the folder with time-date stamp in the name
DateString = Format(Now, "mm-dd-yyyy hh-mm-ss")
FolderName = xWb.Path & "\" & "UAT Import Files " & DateString
MkDir FolderName

'Actual saving of worksheets as separate CSV files
''Skip specific worksheets
For Each xWs In xWb.Worksheets
On Error Resume Next
            If (xWs.Name <> "Summary") And (xWs.Name <> "Export") And (xWs.Name <> "UAT Record") Then
    xWs.Copy
        FileExtStr = ".csv": FileFormatNum = 6
   
'Save the test cases inside the folder and use the tab name for the file name
    xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
    Application.ActiveWorkbook.Close False
    End If
Next xWs

'Turn screen updating back on for Excel
Application.ScreenUpdating = True

'Go back to the "Summary" worksheet and clear some memory
ThisWorkbook.Sheets("Summary").Activate
ThisWorkbook.Sheets("Summary").Range("A1").Select
ThisSheet.Select
Set ThisSheet = Nothing

'Yes/No box asking if the folder should open with the new files
CarryOn = MsgBox("Import Files Are Finished Processing" & vbNewLine & "Do you want to view the files?", vbYesNo, "Test Cases Are Finished")
If CarryOn = vbYes Then
Dim source_folder_name As String
source_folder_name = FolderName
ActiveWorkbook.FollowHyperlink Address:=source_folder_name, NewWindow:=True
End If
End If

'Save Workbook
ActiveWorkbook.Save
End Sub

 

[Marc L]

Hi,​

​

for any software with a couple of neurons inside it's very not a concern !​

But for one created by Dumb or Dumber you need to use the VBA statement Print # - to see in VBA help - with a ; character at the end of the statement.​

If you really need further help link a zip file on a files host website like Dropbox for example …​

​

 

[Domenic]

As @Marc L has already pointed out, you can use the Open statement to create your CSV files. For example, first add the following code to your project by copying it into a regular module...

Function ExportWorksheetToCSV(ByVal saveToFolder As String, ByVal saveAsFilename As String, ByRef ws As Worksheet) As Boolean

    If Len(saveToFolder) = 0 Then
        MsgBox "Path is missing!", vbExclamation
        ExportWorksheetToCSV = False
        Exit Function
    End If

    If Len(Dir(saveToFolder, vbDirectory)) = 0 Then
        MsgBox saveToFolder & " does not exist!", vbExclamation
        ExportWorksheetToCSV = False
        Exit Function
    End If
    
    If Right(saveToFolder, 1) <> "\" Then
        saveToFolder = saveToFolder & "\"
    End If

    Dim data As Variant
    data = ws.UsedRange.Value
    
    Dim filenum As Long
    filenum = FreeFile()
    
    Open saveToFolder & saveAsFilename For Output As #filenum
    
        Dim r As Long
        Dim c As Long
        For r = LBound(data) To UBound(data)
            For c = LBound(data, 2) To UBound(data, 2)
                If c < UBound(data, 2) Then
                    Print #filenum, data(r, c) & ",";
                ElseIf r < UBound(data) Then
                    Print #filenum, data(r, c)
                Else
                    Print #filenum, data(r, c);
                End If
            Next c
        Next r
    
    Close #filenum
    
    ExportWorksheetToCSV = True
    
End Function

Then you can replace your For Each/Next loop with the following...

    For Each xWs In xWb.Worksheets
        If (xWs.Name <> "Summary") And (xWs.Name <> "Export") And (xWs.Name <> "UAT Record") Then
            If Not ExportWorksheetToCSV(FolderName, xWs.Name & ".csv", xWs) Then Exit Sub
        End If
    Next xWs

Hope this helps!

 

[ramkrau]

As @Marc L has already pointed out, you can use the Open statement to create your CSV files. For example, first add the following code to your project by copying it into a regular module...

Function ExportWorksheetToCSV(ByVal saveToFolder As String, ByVal saveAsFilename As String, ByRef ws As Worksheet) As Boolean

    If Len(saveToFolder) = 0 Then
        MsgBox "Path is missing!", vbExclamation
        ExportWorksheetToCSV = False
        Exit Function
    End If

    If Len(Dir(saveToFolder, vbDirectory)) = 0 Then
        MsgBox saveToFolder & " does not exist!", vbExclamation
        ExportWorksheetToCSV = False
        Exit Function
    End If
   
    If Right(saveToFolder, 1) <> "\" Then
        saveToFolder = saveToFolder & "\"
    End If

    Dim data As Variant
    data = ws.UsedRange.Value
   
    Dim filenum As Long
    filenum = FreeFile()
   
    Open saveToFolder & saveAsFilename For Output As #filenum
   
        Dim r As Long
        Dim c As Long
        For r = LBound(data) To UBound(data)
            For c = LBound(data, 2) To UBound(data, 2)
                If c < UBound(data, 2) Then
                    Print #filenum, data(r, c) & ",";
                ElseIf r < UBound(data) Then
                    Print #filenum, data(r, c)
                Else
                    Print #filenum, data(r, c);
                End If
            Next c
        Next r
   
    Close #filenum
   
    ExportWorksheetToCSV = True
   
End Function

Then you can replace your For Each/Next loop with the following...

    For Each xWs In xWb.Worksheets
        If (xWs.Name <> "Summary") And (xWs.Name <> "Export") And (xWs.Name <> "UAT Record") Then
            If Not ExportWorksheetToCSV(FolderName, xWs.Name & ".csv", xWs) Then Exit Sub
        End If
    Next xWs

Hope this helps!

I mean... it's just plain and simple... you are truly a God amongst us all!!!

This worked perfectly! In fact, the time it takes for this to process is so small that the first time I ran the script, I thought it didn't work at all when, in fact, it just was hyper-efficient.

I cannot begin to properly show my gratitude for this. You legit saved me from a migraine, probably 20+ hours of work, and helped me move a major project along... hell, you may have saved me my job with this one! I plan to study the code and do a LOT of googling to learn exactly how this functions.

Thank you so much, Domenic!

 

[Domenic]

That's great, it's nice to know that it has really helped you. I'm glad I could help, and thanks very much for your feedback.

Cheers!