Excel sheet with macros - just save the data?

Question

I've got an Excel sheet with some macros built in to grab some data from a database. I'd like to save the Excel sheet and send it to someone, whilst still retaining the data. However because it uses the macros, the data gets lost when I send the file as it can't connect to the database.

Is there a way I can save/send this file without the macros in place so the data is all static?

Answer 1

Saving as a .csv file should keep only the datavalues of the cells and nothing else (not even formulas). Afterwards, you can save as an .xls file from your new .csv file if you prefer that format.

Answer 2

Here is code that will save the file for you:

Option Explicit

Sub SaveValuesToDisconnectedFile()
    Sheets("Sheet1").Copy
    Sheets("Sheet1").Name = "DataFromDB"
    ActiveSheet.UsedRange.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.SaveAs Filename:="C:\Temp\Offline Data.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Replace "Sheet1" with the name of the sheet that contains your data. You can also replace the path to wherever you want the file saved.

Note that if you run this twice, the file will already exist, so you'll get prompted to either cancel or overwrite the existing file.