Solved: VB code to save file to app directory?

[speedy dave]

Hi guys, could someone please enlighten me on the code to save the workbook to the current workbooks location.

I have a workbook that has a macro button that saves the current workbook as "filename & Date & time.xlsm"

I need it to be able to save the updated workbook to the same location that the original workbook is in, regardless of the folders name that it is saved in.

What i mean is that if the workbook is saved to "c:\terry\files , the code will save the updated workbook to c:\terry\files,
BUT
if Anne saves the workbook onto her computer in, say, c:\anne\files, then the code will save the updated workbook on Annes computer to c:\anne\files

This way, it does not matter where anybody saves the original workbook on their computer, the macro does not need to be changed to suit that computer.
You could just have a folder on the desktop with the original workbook in it. All subsequent "macro" saves will put the updated workbooks into that same folder on the desktop.

Thanks in advance
Speedy Dave

[lucas]

Here's an example that saves a little different filename than yours but you should be able to get the idea:

[VBA] ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & Sheets("Details").Range("R2")[/VBA]

just add your filename, date and time instead of sheets....etc.

I don't have 2007 so I can't test this for you. Works in 2003

[speedy dave]

Thanks Steve, brilliant!
Worked a treat !

Actual code below.........

[VBA]
Sub PrintReceipt()
MsgBox "Note: The current Spreadsheet has been automatically saved "
ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & "SafetyBay rent_" & Format(Date, "yyyy-mm-dd") & Format(Time, "__hh.mm") & ".xlsm"

' to print work sheet as a pdf file
Sheets("Receipt").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ActiveWorkbook.Path & "\" & "SafetyBay rent_" & Format(Date, "yyyy-mm-dd") & Format(Time, "__hh.mm") & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

'
' PrintReceipt Macro
' This macro prints the assigned worksheet to the systems default printer
'
' this string prints out the "named" work sheet (ie "Receipt") to the default printer
With Sheets("Receipt")
.PageSetup.PrintArea = "$b$2:$i$16"
.PrintOut
End With
'This string shuts down the open workbook
Application.Quit
'This string will close the active workbook
ThisWorkbook.Close savechanges:=True 'true ???

End Sub
[/VBA]
As you can see, saves the file with a new name of date and time,
Saves a copy as a .PDF
Prints a range
then quits

Thanks again
Speedy !

Edit: VBA tags added to code.

[lucas]

I just noticed your post count. Welcome to the forum.

Couple of tips to make it better for you here.

When you post code, select it and hit the vba button to format it for the forum. I edited your post #3

You can mark your thread solved using the thread tools at the top of the page.

That saves regulars looking to help from reading an entire thread just to find it's been solved.

[speedy dave]

Thanks for the advice Steve !

[speedy dave]

What do I need to add to this line of code ....

ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & Sheets("Details").Range("R2")

To add the saved file to a sub folder of the app root folder ??

Thanks
Speedy

[mdmackillop]

Answered here