VBA common dialog box to search for Excel file

MrDavidThorn
MrDavidThorn used Ask the Experts™
on
Hi Experts

Been a while since I used the common dialog box and Iv fogot the commands avaiable.
I want a open  file box to pop up and allow the user to search for Excel files (post and pre 2007), I also want the dialog box to open in the my documents folder, I have the code below  - its opening the file open box but not applying the filter (I can see all other files) and its defaulting to a templates location on the c drive


Me.cdExcelFile.DialogTitle = "Select new source file"

Me.cdExcelFile.Flags = cdlOFNFileMustExist
Me.cdExcelFile.Filter = "Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm)*.xls;*.xlsx;*.xlsm"

Me.cdExcelFile.ShowOpen
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Project manager
Commented:
Can you try the following

Me.cdExcelFile.DialogTitle = "Select new source file"

Me.cdExcelFile.Flags = cdlOFNFileMustExist
Me.cdExcelFile.Filter = "Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm)|*.xls"

and

Me.cdExcelFile.Filter = "Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"

and

Me.cdExcelFile.Filter = "Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm)*.xls;*.xlsx;*.xlsm;"
NorieAnalyst Assistant
Commented:
In Excel you can also use GetOpenFilename.

Dim varFiles

'change for OS
ChDir Environ$("USERPROFILE") & "\Desktop"

varFiles = Application.GetOpenFilename("Microsoft Excel Workbooks, *.xls;*.xlsm;*.xlsx", , "Pick a file", , MultiSelect:=True)

Open in new window

If no files are selected varFiles will be False, if one or more files is selected it will be an array.

You can change MultiSelect to False if you only want one file selected.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial