Saving wb in different format without affecting main workbook

[x080790]

Hi

I am looking for code to save a copy of the current xlsm workbook in xls format with the same name and directory (and/or create it with no Dialog box) and continue working on the xlsm file. The xls file does not need to open.

Here is why: I am sorting addresses on more that 3 levels (2007 feature) but the mapping program only reads non macro enabled formatted workbooks.

Hence both files need to remain in sync but only the xlsm file is used to work on the data. If the "save" operation is not initiated manually with a Macro button, at the latest, upon closing of the xlsm file, a "Save" of the xls should be triggered without affecting the normal save and close of the current xlsm file. Also, the xls version of current file should take place without the dialog message regarding the loss of functionality in xls, xlsx, etc, files... popping up).

I included the following code in the Before_Close phase of the workbook (not sure if that is the right way to do it) and also created a button on a worksheet to occasionally trigger the an update (Save) of the xls file but I don't know enough about VBA.

This code seems to work but it asks for the name of a file (even though it is already there and should be used), warns about overwriting it (even though that is what needs to happen), and then the xlsm closes and the new file becomes active workbook (not the desired one).

Please help. Thanks

Here is the code I came up with so far after many hours of frustration:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fileSaveName As String
Dim sourcewb As String
Dim tempfilename As String

Application.EnableEvents = False
Do
fileSaveName = Application.GetSaveAsFilename(fileFilter:="Excel 2003 Files (*.xls), *.xls")
If fileSaveName <> "False" And Right(fileSaveName, 4) <> ".xls" Then
MsgBox ("FILE NOT SAVED - please save in .xls format")
End If
Loop Until fileSaveName = "False" Or Right(fileSaveName, 4) = ".xls"

If fileSaveName <> "False" Then
ThisWorkbook.SaveAs Filename:=fileSaveName
End If
Cancel = True
Application.EnableEvents = True
End Sub

 

[StuLux]

Welcome to Mr Excel - first post on Christmas day!

To avoid the warning about overwriting you can switch off warnings as below (and switch them back on as well):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fileSaveName As String
Dim sourcewb As String
Dim tempfilename As String
 
Application.EnableEvents = False
Do
fileSaveName = Application.GetSaveAsFilename(fileFilter:="Excel 2003 Files (*.xls), *.xls")
If fileSaveName <> "False" And Right(fileSaveName, 4) <> ".xls" Then
MsgBox ("FILE NOT SAVED - please save in .xls format")
End If
Loop Until fileSaveName = "False" Or Right(fileSaveName, 4) = ".xls"
 
If fileSaveName <> "False" Then
Application.DisplayAlerts = False 
ThisWorkbook.SaveAs Filename:=fileSaveName
End If
Cancel = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[x080790]

Thanks for the prompt response.
It works, no more warning about the loss of features. I still seem to be caught in a loop and the focus goes to the newly saved file which in turn wants to be saved again... Any suggestion about loosing focus of the xlsm file?

 

[StuLux]

No problem - I find that the Mr Excel forum works best if you raise each new problem as a new subject, sorry but I don't have time to look into this one at the moment, Merry Christmas.

 

[Bill Rockenbach]

instead of:

ThisWorkbook.SaveAs Filename:=fileSaveName

try

ThisWorkbook.SaveCopyAs Filename:=fileSaveName

 

[x080790]

Thanks to everyone for your responses.
I had a friend of mine look into this and in case someone needs to do the same thing. (work on Excel 2007, update the file in xls without changing file), in order to access the data in Mappoint 2006 and above, here is the code that works perfectly thanks to my friend Jason:


Sub CreateMappingWorkbook()
Dim SaveName As Variant

With Application 'does not seem to work
.ScreenUpdating = False
.EnableEvents = False
End With

Dim oldPath As String
oldPath = ActiveWorkbook.FullName

Dim chunks() As String
chunks = Split(oldPath, ".")

Dim newPath As String
newPath = chunks(0) + ".xls"


ThisWorkbook.Sheets.Copy

For i = 1 To ActiveWorkbook.Sheets.Count

ActiveWorkbook.Worksheets(i).Cells.FormatConditions.Delete

Next i

On Error Resume Next
Kill (newPath)
On Error GoTo 0

ActiveWorkbook.SaveAs Filename:=newPath, _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges

ActiveWorkbook.Close True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

===========================================================
Then to call it from the before close section
Private Sub Workbook_BeforeClose(Cancel As Boolean)

CreateMappingWorkbook

End Sub

 

[Bill Rockenbach]

Thanks for the feed back