Newbie: Problem changing VB in file backup macro

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
I wonder if you are able to help me please?

I am trying to automatically back up a file called Running Log.xls and was kindly given the code below to input in Immediate. The problem that I have though, is that when I save any other Excel files I open, the Running Log file opens up as well and the macro below is run.

Would you be kind enough to amend the data below or tell me what I should do so that the macro only runs when I close the Running Log file?

Many thanks!


Sub SaveWorkbookBackup()
Dim awb As Workbook, BackupFileName As String, i As Integer, OK As Boolean
If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
Set awb = ActiveWorkbook
If awb.Path = "" Then
Application.Dialogs(xlDialogSaveAs).Show
Else
BackupFileName = awb.FullName
i = 0
While InStr(i + 1, BackupFileName, ".") > 0
i = InStr(i + 1, BackupFileName, ".")
Wend
If i > 0 Then BackupFileName = Left(BackupFileName, i - 1)
BackupFileName = BackupFileName & ".bak"
OK = False
On Error GoTo NotAbleToSave
With awb
Application.StatusBar = "Saving this workbook..."
.Save
Application.StatusBar = "Saving this workbook backup..."
.SaveCopyAs BackupFileName
OK = True
End With
End If
NotAbleToSave:
Set awb = Nothing
Application.StatusBar = False
If Not OK Then
MsgBox "Backup Copy Not Saved!", vbExclamation, ThisWorkbook.Name
End If
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Is this called from some other event macro? Which module is this stored in -- Thisworkbook?
 
Upvote 0
Just Jon

Thanks ever such a lot for replying, and so soon...

The macro is stored in All Open Workbooks - hope that's what you wanted to know...

Hope you can help!
 
Upvote 0
What do you have to do in order for it to run? It has to be called somehow. Do you know if there are any event macros stored in the same place as this one, or in another class module?
 
Upvote 0
Hi Jon

For the macro to run, all I am doing is saving the file. My objective is to back up the file for Running Log.xls ONLY, but if I've opened another file and keyed 'Save' the Running Log file opens and it saves that as well!

I have the following macros running in Module 10 of Running Log:


Sub DailyTrackingStartofMonth()
'
' DailyTrackingStartofMonth Macro
' Macro recorded 29/06/2003 by Paul
'

'

ActiveWindow.ScrollRow = 1
End Sub
Sub WeeklyTrackingTotals()
'
' WeeklyTrackingTotals Macro
' Macro recorded 29/06/2003 by Paul
'

'

ActiveWindow.ScrollRow = 237
End Sub
Sub WeeklyTrackingLast6Weeks()
'
' WeeklyTrackingLast6Weeks Macro
' Macro recorded 29/06/2003 by Paul
'

'

ActiveWindow.ScrollRow = 237
End Sub


The code I posted previously is in Module 12 of Running Log.

(BTW, I have a total of 14 modules listed in Running Log. I have double clicked on the other 12 modules and they are all empty - how can I delete these?).

Hope this helps!
 
Upvote 0
There's got to be an event macro floating around somewhere that is calling this macro on close events, probably with your running file name hard coded in it -- that might explain why that file opens when another closes... trying to figure out how to find it...
 
Upvote 0
Jon

It's only Save events which trigger the macro...

BTW, are all those redundant Modules numbers doing any harm or slowing things unnecessarily maybe - how do I get rid of them?

Thanks once again for all your trouble.
 
Upvote 0
Hi ZOSO,

To remove a Module, from the VBA Editor, right click the Module you want to Delete. Choose "Remove Module..". then choose "No" to "Do you want to export.." Your Module will be gone.

Datsmart
 
Upvote 0
Datsmart

You've been really helpful, many thanks! (the reason I couldn't figure that one out myself was because I was trying to delete the modules in the Edit Macro rather than Visual Basic Editor, and the Remove option wasn't listed!!!). :coffee:
 
Upvote 0
Hey Zoso,

It seems like you're going to a lot of extra effort to save a backup of your file when Excel has a File Backup feature. Goto Save As-->Tools-->Select "Always Create Backup".

As for the additional modules that you have, if they contain no code, they don't take up "that much" space, but you should get rid of 'em as the recorder will generate a new one each time you open Excel and record. It can get confusing if you're trying to keep track of everything.

HTH,

Smitty
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top