How can I save the data in an XLSM workbook to CSV without closing the XLSM?

Question

I'm saving the data in a worksheet of an XLSM workbook to a CSV file. That part works file. But when I save to CSV, it closes the XLSM file. I'd like to leave the XLSM file open. Is there a way to do that and close the CSV file all under one macro?

This is what I have thus far:

'Since this macro ended without error, save the workbook as a CSV file
ActiveWorkbook.SaveAs Filename:=strPathName & strCSVName, _
    FileFormat:=xlCSVMSDOS, CreateBackup:=False

'Set an object for the CSV file
Set objCsvFile = ActiveWorkbook

'The Save to CSV closed the workbook.  Open the XLSM workbook
Workbooks.Open strPathName & "\" & strXlsmName

If I try to close the CSV file now, the macro abruptly halts.

Thanks for looking at this.

What if you make a copy of the xlsm, open that, and save as csv? This way your original xlsm is untouched. — Ampersand
I am not sure how you want to export an entire Excel file as one CSV file. Normally, people create one CSV file for each sheet (assuming that only one table is on that sheet and it is top-left-corner of that table is aligned with A1). In this case you could simply use this solution: stackoverflow.com/a/25387016/1153513 (using .Copy instead of .Move). But if that's not an option you can go with @Ampersand proposal. Anyway, you need to have (in the end) at least one .xlsm file open and running (to host the VBA code). — Ralph
I'm only exporting the active sheet from the workbook to export to the CSV. That part works well. I had not considered Ampersand's approach. I'll use that. Thanks for your help. — user3138025

Parfait Parfait · Accepted Answer · 2016-06-14T23:23:12

Consider using Jet/ACE SQL Engine (Windows .dll files) if using Excel for PC. Jet/ACE OLDEB can connect to your workbook as if it is a database and export text files as if they were tables. Do note a scheme.ini file is also created identifying the structure of csv export. CSV delimited is the default.

Sub ExportCSV()
    Dim conn As Object
    Dim strConnection As String, strSQL As String

    Set conn = CreateObject("ADODB.Connection")

    ' OPEN DB CONNECTION
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "Data Source='C:\Path\To\Workbook.xlsm';" _
                       & "Extended Properties=""Excel 8.0;HDR=YES;"";"        
    conn.Open strConnection

    ' EXPORT WORKSHEET TO CSV
    strSQL = " SELECT * " _
              & " INTO [text;HDR=Yes;Database=C:\Path\To\CSV\Destination\Folder;" _
              & "CharacterSet=65001].CSVFile.csv" _
              & "  FROM [WORKSHEET$]"

    ' EXECUTE MAKE-TABLE QUERY
    conn.Execute strSQL

    ' CLOSE CONNECTION
    conn.Close
    Set conn = Nothing

En Sub

How can I save the data in an XLSM workbook to CSV without closing the XLSM?

1 Answers