CODEWITHSUNDEEP
vbaexcelcsvexport-to-csv
Kaito Kid
Kaito Kid

Reputation: 1093

How can I export a closed workbook sheet to a .CSV file?

I am currently working on a set of vba functions that should allow the user to export several sheets from a xls or xlsx file and export them all to different file formats depending on the name of the sheets. It's a very specific procedure using older files.

Some of the sheets in the chosen file should be treated as raw data, and I want to export them as a .CSV file without making any change in the data. If it helps, I don't even need to "see" the data to make IFs or other logical treatments.

I already have a function to get data from a specific cell in a specific sheet from the closed file, but I can't figure out how to get all the data from the sheet without iterating through all the billions of cells that are mostly empty just to check if there is data in the Cell(23785;GP).

Here is the function I already have, which might help understanding the expected behavior.

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
    ByVal WBName As String, wsName As String, cellRef As String) As Variant
    Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & WBName) = "" Then Exit Function
    arg = "'" & wbPath & "[" & WBName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

It is necessary that the other workbook is still closed at the end, and that no changes whatsoever are made to the current one. At the very least, all changes made should be undone so that after choosing the file, the user will only see my confirmation messages and the newly created files on his computer, with no visible difference in his Excel. Since this will be used by people who are neither programmers nor very tech savvy, so any messing up with their current workbook that they could be using for something at the same time will earn us some complaints.

Upvotes: 0

Views: 732

Answers (1)

Eduardo Po&#231;o
Eduardo Po&#231;o

Reputation: 3079

This Sub takes a file string and the sheet name as arguments to create a .CSV file with the extension .csv appended to its previous name, sheet name and extension.

Private Sub export_file_to_csv(src_path, sheet_name)
    Application.DisplayAlerts = False

    Set src_workbook = Workbooks.Open(src_path, , True)
    src_workbook.Sheets(sheet_name).Activate

    dst_path = src_path & " - " & sheet_name & ".csv"
    src_workbook.SaveAs Filename:=dst_path, FileFormat:=xlCSV
    src_workbook.Close

    Application.DisplayAlerts = True
End Sub

You can then call it as many times as needed in another sub.

Sub main_sub()
    export_file_to_csv "C:\Users\username\Desktop\maps.xlsx", "Europe"
    export_file_to_csv "C:\Users\username\Desktop\maps.xlsx", "Asia"
    export_file_to_csv "C:\Users\username\Documents\budget.xls", "Sheet1"
End Sub

Just put this code in another workbook, edit the paths and sheet names of the files that will be exported to CSV and run the main_sub, which should appear on the macro menu. The original files will be kept untouched, as they were open read-only.

Hope it helps!

Upvotes: 1

Related Questions