Context: My goal is to update links between PPT and Excel, and have the user select the new file to link to. We have monthly files with just the numbers changing (eg, table sizes are all fixed).
I'm trying to set the default directory that Excel shows when using the GetOpenFilename. The trick is my code is in PowerPoint, and I am opening the Excel file with the CreateObject("Excel.Application"). The code I tried is here, but when it opens the GetOpenFilename, it goes only to MyDocuments:
Set exl = CreateObject("Excel.Application")
ChDrive Left(ActivePresentation.Path, 2)
strNewDrive = Left(ActivePresentation.Path, 2)
strNewDir = ActivePresentation.Path
ExcelFileNew = exl.Application.GetOpenFilename(, , "Select Excel File")
' The rest of the code is a bit clumsy, but attached.