Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks!! :) It was for a colleague of mine but he said in the end he used the 'paste special' feature. This would work too though so I think he'll do this from now on. –  Richard Hedges Jul 6 '12 at 9:56
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.