Setting default file path from Access for Excel GetOpenFileName

Question

I'm opening a csv file in Excel with an Access macro. I'm doing this to get the file name and then use the TransferText function to import the file into an Access table.

I would like to specify a default path to eliminate some of the navigation for the end user. I've tried ChDrive and ChDir but The GetOpenFileName still takes me to a personal drive on the network.

Below is the code I'm using. Any suggestions or advice would be greatly appreciated/ Thanks in advance.....

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
            
ChDrive "V:"                                                                               
ChDir "V:\Accounting\Projects\In Work\Project Database\"
    
RawData = ""
RawData = ExcelApp.Application.GetOpenFilename              
    
Set ExcelApp = Nothing

DoCmd.TransferText acImportDelim, , "tbl_Stage", RawData, True

david david · Accepted Answer · 2021-11-11T00:10:37

You need to change the drive and folder of the Excel object. At the moment, you're changing the drive and folder of the Access object.

Excelapp.DefaultFilePath = "V:\Accounting\Projects\In Work\Project Database\"

Unless you are using a very old version of Excel, you can use the FileDialog object for more control:

Set FD = Excelapp.FileDialog(msoFileDialogFilePicker)
With FD
    .InitialFileName = ActiveDocument.Path
    .Show
End With

exFile = FD.SelectedItems(1)

Setting default file path from Access for Excel GetOpenFileName

1 Answers