Document Location Macro

[austinandrei]

Hi,
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.

austin

 

[Michael M]

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"
    Else
        Range("A1").Value = FilePath
    End If
End Sub

 

[austinandrei]

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.

 

[austinandrei]

this is the ideal code to happen in Book2:

Sub Book2Macro()
'It will create a new workbook
    Workbooks.Add
'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
    ActiveWindow.Close
It will write the filepath of Book1 in RangeA1 of Book2
    Range("A1").Value = "C:\Users\magmo\Documents\Book1.xlsx"
End Sub

 

[austinandrei]

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


    Windows("Book2.xlsm").Activate


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


    Windows(fname).Activate
    ActiveWorkbook.SaveAs Filename:=w, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        i, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
    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!

 

[Michael M]

Ok, then I was obviously misunderstanding your requirements....glad it worked for you ...