Saving wb in different format without affecting main workbook

x080790

New Member
Joined
Dec 24, 2009
Messages
4
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::confused:

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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
652
Office Version
  1. 365
Platform
  1. Windows
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):


Code:
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

New Member
Joined
Dec 24, 2009
Messages
4
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? :eek:
 

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
652
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 4, 2009
Messages
1,415

ADVERTISEMENT

instead of:
Code:
ThisWorkbook.SaveAs Filename:=fileSaveName

try
Code:
ThisWorkbook.SaveCopyAs Filename:=fileSaveName
 

x080790

New Member
Joined
Dec 24, 2009
Messages
4
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
 

Forum statistics

Threads
1,148,170
Messages
5,745,166
Members
423,929
Latest member
z y

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top