PDA

View Full Version : SaveCopyAs not working.



klandreth
08-09-2012, 07:00 AM
Hello again.

I've scoured the Internet for examples of how to save / make a backup copy of the current workbook. However, my code is either generating an error ("1004) or simply not working (no file is saved).

What did I miss? :banghead:

Sub Consolidate()

Dim exApp As Excel.Application
Dim exDoc As Excel.Workbook

Dim sCurrentFileName, sBackupFileName, sCurrentDateTime As String

Application.DisplayStatusBar = True

sCurrentFileName = ActiveWorkbook.Name
sCurrentDateTime = Format (Date, "yyyymmdd") & "_" & Format (Time, "hhmmss")

sBackupFileName = sCurrentDateTime & "_" & sCurrentFileName

ActiveWorbook.SaveCopyAs (sBackupFileName)

MsgBox ("Backup File is " & sBackupFileName)

Application.StatusBar "Backup File is " & sBackupFileName

End Sub

Kenneth Hobs
08-09-2012, 07:22 AM
Please use VBA code tags between the pasted code.

Add a drive and path:
sBackupFileName = Thisworkbook.Path & "\" & sCurrentDateTime & "_" & sCurrentFileName

klandreth
08-09-2012, 10:08 AM
Thanks! I tried that but it didn't work either.

I failed to mention I had added a piece of code:

sBackupPath = ActiveWorkbook.Path & "\Backups\"

to which I appended the filename.

I'm not getting the 1004 error anymore; it's just not saving (creating) the file.

:dunno

Kenneth Hobs
08-09-2012, 11:39 AM
You might try posting your code so that we can see more. Be sure that you want ActiveWorkbook.name and not ThisWorkbook.name.

You might try using DIR() to see if the backup folder exits.

ActiveWorkbook must have been saved or it will have a name like Book1 rather than Book1.xlsm.

Sub Macro1()
Dim dp As String, fn As String, tf As Boolean
Dim sCurrentDateTime As String

tf = Application.DisplayStatusBar
fn = ActiveWorkbook.Name
sCurrentDateTime = Format(Date, "yyyymmdd") & "_" & Format(Time, "hhmmss")

dp = ActiveWorkbook.Path & "\BackUp\"
If Dir(dp, vbDirectory) = "" Then
MsgBox dp, vbCritical, "Path Does Not Exist - Macro Ending"
Exit Sub
End If

fn = dp & Format(Date, "yyyymmdd") & "_" & Format(Time, "hhmmss") & fn

Application.DisplayStatusBar = True
Application.StatusBar = "Copied to: " & fn
ActiveWorkbook.SaveCopyAs fn
MsgBox fn, vbInformation, "File Copied to:"
Application.StatusBar = False
Application.DisplayStatusBar = tf
End Sub