VBA Default Directory for GetOpenFilename accessued from other Office program

I use GetSetting and SaveSetting to store MRU paths for this purpose. That way you can access the paths from any Office app hosting VBA. A default path can be set when your app runs for the first time assuming you don't set the registry setting with an installer.

Example:

Public Const REG_PROD = "My Product Name"
Public Const REG_PREFS = "Preferences"
Public Const REG_PATH = "Excel File Path"

Sub SaveAndGetSetting()
' Save a setting in the VBA area of the Windows Registry using
' AppName, Section, Key, Setting
SaveSetting REG_PROD, REG_PREFS, REG_PATH, Environ("APPDATA") & "\MyApp"
' Get the setting and optionally use a default value if the setting doesn't exist
Debug.Print GetSetting (REG_PROD, REG_PREFS, REG_PATH, Environ("APPDATA") & "\MyApp")
End Sub

Select allOpen in new window

You can then use that path to pre-define the path in your code or within a user folder/file selection dialog box.

You can also see/modify the settings using regedit.exe here:

HKEY_CURRENT_USER\Software\VB and VBA Program Settings\My App Name

JSRWilson - thanks!  Perfect.  Works exactly as I need it to!!  To be dynamic as I required (and as our files are saved), I changed this line:
.InitialFileName = "C:\Users\Optiplex\Desktop\"          'to this:
.InitialFileName = ActivePresentation.Path

Jamie - I'm not seeing this would work within a simple *.pptm file, but the code looks like it was the right direction too.  Thanks for the response.

Thought clearly about my end goal and not just the process I *thought* I wanted to follow.  Quick and elegent solution.

Full code (for posterity)

'PPT Link Updater

'originally from:   http://www.ozgrid.com/forum/showthread.php?t=147605

Sub M1()
    Dim sld As Slide
    Dim sh As Shape
    Dim ExcelFileNew
    Dim ExcelFileOld As String
    Dim intLenFileOld As Integer
    Dim intLenFileNew As Integer
    Dim exl As Object
    Dim strNewDrive, strNewDir As String
    Dim fd As FileDialog
    Set exl = CreateObject("Excel.Application")
    Set fd = PowerPoint.Application.FileDialog(msoFileDialogOpen)


    With fd
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls,*xlsx,*xlsm"
        .InitialFileName = ActivePresentation.Path
        If .Show = True Then strFilename = .SelectedItems(1)
    End With

'MsgBox "Filename= " & Right(strFilename, Len(strFilename) - InStrRev(strFilename, "\"))

    Call stripPath(ExcelFileNew, filenameNew)

    For Each sld In ActivePresentation.Slides
        sld.Select
        For Each sh In sld.Shapes
            If sh.Type = msoLinkedOLEObject Then
                With sh.LinkFormat
                    LinkOld = .SourceFullName
                    Call stripReference(LinkOld, fullpathOld)
                    Call stripPath(fullpathOld, filenameOld)
                   
                    LinkNew = ExcelFileNew & Mid(LinkOld, Len(fullpathOld) + 1, 99)
                   
'  Only replace it if the file name is similar...
'  so "extras" like Cash balances are not replaced by the Trust Fees file
                    If Left(filenameOld, 25) = Left(filenameNew, 25) Then
                        .SourceFullName = LinkNew
                    End If
                End With
            End If
        Next sh
    Next sld

ExitMySub:
End Sub
 '
Sub stripPath(fullPath, filename)
     
     'This will take c:\folder\workbook.xlsx* and provide workbook.xlsx*
    Dim filenamePosition As Long
     
    filenamePosition = InStrRev(fullPath, "\")
    filename = Mid(fullPath, filenamePosition + 1, Len(fullPath) - filenamePosition)
     
End Sub
 '
Sub stripReference(fullReference, filename)
     
     'This will take *workbook.xls!Graphs![workbook.xls]Graphs Chart 1 and provide *workbook.xls
    Dim referencePosition As Long
     
    referencePosition = InStr(1, fullReference, "!")
    filename = Left(fullReference, referencePosition - 1)
     
End Sub
 '
Sub showStrings(ExcelFileNew, filenameNew, LinkOld, fullpathOld, filenameOld, LinkNew)
     
     'This is just a debugging function to display the variables
    MsgBox ("ExcelFileNew: " & ExcelFileNew & vbNewLine _
    & "filenameNew: " & filenameNew & vbNewLine _
    & "LinkOld: " & LinkOld & vbNewLine _
    & "fullpathOld: " & fullpathOld & vbNewLine _
    & "filenameOld: " & filenameOld & vbNewLine _
    & "LinkNew: " & LinkNew)
     
End Sub

Yep I should have pointed out that the "default" was to my own desktop!