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

ramkrau

New Member
Joined
Jan 9, 2019
Messages
14
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!!!!!!!

VBA Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,248
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,725
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...

VBA Code:
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...

VBA Code:
    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!
 
Solution

ramkrau

New Member
Joined
Jan 9, 2019
Messages
14
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...

VBA Code:
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...

VBA Code:
    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!!! :biggrin:

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,725
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! :)
 

Forum statistics

Threads
1,141,575
Messages
5,707,176
Members
421,495
Latest member
jono_oh

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
Top