Solved: duplicate file & strip code from workbook

[dlh]

I'm trying to create a "snapshot" macro that creates a copy of the current workbook, pasting values in place of formulas and removing all VBA code & UserForms.

The challenge I'm facing is that I'm working in Excel 2007 but I need this macro to work equally well in Excel 2003. And all resulting files need to be readable in 2003, too. (I'm working partially blind since I cannot test anything with 2003.)

Ideally I'd just add a new workbook, insert the appropriate number of sheets, copy over everything I need by values, and save it. That way I wouldn't have to worry about figuring out how to write code which deletes itself. But in 2007, new workbooks default to a 2007 format, so the result wouldn't be readable by 2003. And if I wrote code to explicitly SaveAs in 2003 format, then the format identifier (a new feature in 2007) wouldn't be recognized and the code would break when run by Excel 2003.

I though I might use a shell command to duplicate my current file, open the duplicate file and strip code from that one, but there has to be an easier way. Right?

[Bob Phillips]

I am lost as to what the problem is saving it as a 97-2003 file?

[dlh]

The code must run under 2003 as well as 2007. Wouldn't I get a compile error in 2003 if I tried using the new-in-2007 designation for a 97-2003 file type?

[Bob Phillips]

I may be being thick here, but I have been using 2007 for over 2 years now, and I have no idea what you mean by .. the new-in-2007 designation ...

[dlh]

Aha. I think I've found my answer. The problem was I couldn't use the new xlFileFormat variable in SaveAs to represent the 97-2003 file type. When run under 2003, FileFormat:=xlExcel8 wouldn't be recognized.

Useful links that helped me to understand:
http://www.rondebruin.nl/saveas.htm
http://www.vbaexpress.com/forum/showthread.php?t=20220
http://www.vbaexpress.com/forum/showthread.php?t=13700

Tentative Solution: Don't use xlFileFormat variables. Just use their integer values.

So here's my macro. Since I don't have an installation of Excel 2003, could someone confirm that this does in fact work when run in that version?


Option Explicit
Sub Publish()
Dim NewFilename As String
Dim PublishedBook As Workbook
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim FormatCode As Long

Application.EnableEvents = False
Application.ScreenUpdating = False

NewFilename = Application.GetSaveAsFilename( _
InitialFileName:="Incident Report " & Format(Date, "yyyy-mm-dd"), _
Title:="Publish Report As") & "xls"
If Dir(NewFilename) <> "" Then 'file already exists
If MsgBox(Prompt:="File already exists. Overwrite?", Buttons:=vbYesNo) = vbNo Then
Application.ScreenUpdating = True
Application.EnableEvents = True
End
End If
End If

Set PublishedBook = Application.Workbooks.Add
For Each FromSheet In ThisWorkbook.Worksheets
If FromSheet.Visible = xlSheetVisible Then
FromSheet.Copy After:=PublishedBook.Sheets(PublishedBook.Sheets.Count)
Set ToSheet = PublishedBook.Worksheets(FromSheet.Name)
ToSheet.Cells.Copy
ToSheet.Cells.PasteSpecial Paste:=xlPasteValues
ToSheet.Activate
ActiveWindow.DisplayHeadings = False
End If
Next FromSheet
Application.DisplayAlerts = False
PublishedBook.Worksheets("Sheet1").Delete
Application.DisplayAlerts = True

If Val(Application.Version) < 12 Then
'running Excel 97-2003
FormatCode = -4143 'xlWorkbookNormal
Else
'running Excel 2007
FormatCode = 56 'xlExcel8
End If
Application.DisplayAlerts = False
PublishedBook.SaveAs Filename:=NewFilename, FileFormat:=FormatCode
Application.DisplayAlerts = True
PublishedBook.Close

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub