VBA SaveFile

[Stromma]

Hi All

I have created a backup that makes a copy and save it to a disc. How can i at the same time make a copy that's always are stored in the same path where the user keep the original workbook and name it with ex: Sheets("Test").Range("A1") + Sheets("Test").Range("C3")?

Roger

 

[CraigM]

Here is some code for you to look at:

Sub SaveAsCode()
Dim sFileName As String, sFilePath As String
Dim oFileScript As Object

sFileName = Sheets("Test").Range("A1") & Sheets("Test").Range("C3") & ".xls"
sFilePath = ThisWorkbook.Path & "\"

Set oFileScript = CreateObject("Scripting.FileSystemObject")
If oFileScript.fileexists(sFilePath & sFileName) Then
MsgBox "A file by the name " & sFileName & " already exists in " & sFilePath _
& vbCrLf & "Choose another name"
Application.Dialogs(xlDialogSaveAs).Show (sFileName)
Else
ThisWorkbook.SaveAs (sFilePath & sFileName)
End If

Set oFileScript = Nothing
End Sub


I think this does what you want, but I also included a check that a file doesn't already exist with the name you specify. If it does the above code will prompt the user for another name.

 

[erik.van.geit]

Hi, Roger,

I think you need this

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & yourfilename

regards,
Erik

 

[Stromma]

Thanks Erik & Craig!

It works exactly as it supposed to!

/Roger

 

[erik.van.geit]

you're welcome,
kind regards,
Erik