Document Location Macro


Board Regular
Jun 7, 2014
Need help in getting the location of saved documents using macro.
Say I have Book1 and Book2.
Book1 will be save for example in My Documents with Name as Book1 then the location of that saved document will be written in Range A1 of Book2.


Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
This from ronDeBruins site

Sub Test_File_Exist_With_Dir()
    Dim FilePath As String, TestStr As String
    FilePath = "D:\Temp\bob.xlsm" 'change to suit
    TestStr = ""
    On Error Resume Next
    TestStr = Dir(FilePath)
    On Error GoTo 0
    If TestStr = "" Then
        MsgBox "File doesn't exist"
        Range("A1").Value = FilePath
    End If
End Sub
Upvote 0
Hi Michael,
The macro code would be placed in Book2 and will start with saving Book1 in a certain location on where Book2 is saved.
Example is Book2 is saved in My Documents, then when you open Book 2, the macro code will create a new workbook say Book1 and will saved it on the same filepath where Book2 is saved and in Range A1 of Book2, the filepath of Book1 will be written.
The "File doesn't exist" will not happen because a new workbook will always be created and saved on same path the Book2 is saved.
But Book2 will not always be in My Documents. It can be saved in any folder.
Upvote 0
this is the ideal code to happen in Book2:
Sub Book2Macro()
'It will create a new workbook
'It will saved the created workbook in the same filepath the Book2 is saved with the filename as changing
    ActiveWorkbook.SaveAs Filename:="C:\Users\magmo\Documents\Book1.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
It will write the filepath of Book1 in RangeA1 of Book2
    Range("A1").Value = "C:\Users\magmo\Documents\Book1.xlsx"
End Sub
Upvote 0
hmm..I found a way using different columns where:
Column A - document location of Book2
Column B - filename (in the example it is "Book1")
Column C - =A1&B1&".pdf" (as I also need to save pdf format)
Column D - =A1&B1&".xlsx" (as I need to save it also in Excel format)
Then just replace the filename of Book2 to nothing.
Thus the code below works:
Sub Macro15()
' Macro15 Macro

fname = ActiveWorkbook.Name


    Range("A1").Value = ThisWorkbook.FullName
    Cells.Replace What:="Book2.xlsm", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
i = Range("C1").Value
w = Range("D1").Value

    ActiveWorkbook.SaveAs Filename:=w, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        i, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    ActiveWorkbook.Close SaveChanges:=True
                Set fname = Nothing

End Sub
Book1 should be activated first as it will be set as the activeworkbook.
With this, the name of the saved file (which is Book1.xlsx) is now in Column D which I can just rearrange too.
If there's a way to simplify this, then the better. Thanks!
Last edited:
Upvote 0
Ok, then I was obviously misunderstanding your requirements....glad it worked for you ...(y)
Upvote 0

Forum statistics

Latest member

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
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 "".
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