Reference a Dir path located in another workbook.

[15309mng]

Hi all
I would like to know the syntax for referencing a directory path which is stored in another workbook.
I have built a spreadsheet for my own use, but would like to make it available to other users. The intention is for them to populate specific cells with their own information regarding the name & location of a second worksheet. Part of the code references my filepath and need to be able to extract their info for the code to execute properly.

i'm not 100% on how to tag my code... hoping this works!

Sub MT02_SaveAs()
    Application.DisplayAlerts = False
    ChDir "C:\SAP\MATERIAL TRACKING"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\SAP\MATERIAL TRACKING\Materials Tracking Report " & Format(Now, "dd-mm-yyyy  hh.mm.ss") & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False   
    Application.DisplayAlerts = True
End Sub

The data is stored in the following:

Workbooks("Run MT Report.xlsx").Sheets(1).Range ("C6")

Any help much appreciated
Thanks and regards
Mark

 

[SirJB7]

Hi, 15309mng!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, check the following code of a function to return the path:

Option Explicit

Function GetThatSqueezyPath() As String
    ' constants
    ' if blank, actual path; if not, partial qualify "\" or fully "x:..."
    Const ksPath = ""
   
    Const ksWB = "Run MT Report.xlsx"
    Const kiWS = 1
    Const ksRng = "C6"
    Const ksBackSlash = "\"
    ' declarations
    Dim I As Integer, bOpen As Boolean, sPath As String
    ' start
    bOpen = False
    ' process
    For I = 1 To Workbooks.Count
        If Workbooks(I).Name = ksWB Then Exit For
    Next I
    If I > Workbooks.Count Then
        bOpen = True
        If ksPath = "" Then
            sPath = ThisWorkbook.Path
        Else
            If Left(ksPath, 1) = ksBackSlash Then
                sPath = ThisWorkbook.Path & ksPath
            Else
                sPath = ksPath
            End If
        End If
        Workbooks.Open sPath & Application.PathSeparator & ksWB
    End If
    sPath = Workbooks(I).Worksheets(kiWS).Range(ksRng).Value
    ' end
    If bOpen Then Workbooks(I).Close False
    GetThatSqueezyPath = sPath
End Function

You should call it somehow like this:
Workbooks.Open GetThatSqueezyPath & Application.PathSeparator & <your filename>

Just advise if any issue.

Regards!

PS: Tag worked fine, thank you for taking care of it.

 

[15309mng]

Well done Sir! I might have worked that out if I lived to 400. Thanks for your efforts, fabulous.
Regards Mark

 

[SirJB7]

Hi, 15309mng!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!