How to Export -- not Save As -- to another format?

Solution 1:

What we need is to export some data to another file type and keep the active document "our-real-data.xlsx".

CSV_tools.xla

I've put together a quick VBA Add-In which exports as CSV since you mentioned it. I tried to use as less code as possible so new users can understand it more easily.

What does it

Add-in asks where to save the CSV. Default folder is the folder where the XLS is saved in
Add-in automatically detects if a comma or semicolon should be used as separator regarding to your regional system settings
All values of the currently active Excel sheet are exported to a new CSV file which is named after the XLS file
The existing XLS does not get touched in any way

Installation

Download the Add-In and save it to under C:\Users\%USERNAME%\AppData\Roaming\Microsoft\AddIns
Open Excel and activate it under
- Excel 2003: Menu bar → Tools → Add-ins
- Excel 2007: Excel Options → Add-Ins → Go To
- Excel 2010-13: File tab → Options → Add-Ins → Go To
Restart Excel and look at your menu bar

VBA code

Function ExportCSV()

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    On Error Resume Next
    ChDrive (ActiveWorkbook.Path)
    ChDir (ActiveWorkbook.Path)
    strName = fso.GetBaseName(ActiveWorkbook.Name)
    On Error GoTo 0

    strFilename = Application.GetSaveAsFilename(strName, "CSV Files,*.csv", 1)
    delimiter = Application.International(xlListSeparator)
    If Not strFilename = False Then

        Open strFilename For Append As #1

        For Each rngRow In ActiveSheet.UsedRange.Rows
            arr2D = rngRow
            arr1D = Application.Index(arr2D, 1, 0)
            strRow = Join(arr1D, delimiter)
            Print #1, strRow
        Next

        Close #1
    End If

End Function

Automatic updates for different users

Once every user has enabled the Add-in in their Excel (cannot be automated), you can copy new .xla file version via network share, Robocopy or similar tools. But this should be a separate question since there are too many different ways to solve the issue.

For the VBA geeks, the most interesting part is

arr2D = rngRow
arr1D = Application.Index(arr2D, 1, 0)
strRow = Join(arr1D, delimiter)

which converts a range (a single row in our case) to a 2D array, then to a 1D array and finally to a single string using the Join() function