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

Question

How can I Export a sheet or table or selection to another file format without changing the currently active document ?

Our primary data operation environment is in Excel, where extensive use of formatting and formulas is used to get our desired results. The results are then saved as .CSV for interoperability with other programs.

When "File >> Save As >> Save as type >> CSV" is used the .csv file is created and the active document is changed to saved_file.csv. There are some warnings about losing functionality and formatting with this new format, but otherwise the user interface is unchanged. There are bold and italics, different fonts and sizes, background cell fills, defined table ranges and so on. All the formulas in the cells remain intact.

This is a disaster waiting to happen. The user is gets back to work, lulled into thinking everything is normal, soon forgets the current format doesn't support any magic, and carries on doing Important Stuff(tm). Lunch time arrives, they close Excel, slap Yes twice to annoying dialogs that aren't-read-because-they-see-them-all-the-time, and head out the door. ...After lunch user comes back and realizes all the work between initial Save As and Exit is gone.

"File >> Export", unfortunately, is just another navigation route to "Save As", also changing the active document to "some_file.csv".

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

How can this be done? And added to menu or ribbon for one-click execution?

How can it be shared with everyone in the organisation? (without asking individual users to open a VBA editor etc.)

Answer 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

1. Download the Add-In and save it to under C:\Users\%USERNAME%\AppData\Roaming\Microsoft\AddIns
2. 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

3. 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

Answer 2

Teach the user to open a copy of that data.

When selecting an Office document, you can right-click the icon, and click New. This creates a new document/sheet/presentation, based on the original. The users can export from that, just by doing a Save As

I hope you also have good backups though if you're this worried...