How to open a dialog to select files

I'm new to VBA and was wondering if someone could help me understand how to write code to open up a listbox that contains a directory path to search out a file to use later in VBA script for retrieval. Basically im having the user select a file and retrieve data from it and have VBA execute commands. Can anyone help this newbie.

 

Private Sub TextBox5_Change()
' check to see if file exists and then make the insert button enabled

Dim strMyfile As String

strMyfile = Dir(TextBox5.Text)


This should get you started. this will browse to a dir and get the select
filename and path.
you just need to change whichever textbox you want. this is set to TextBox5.

you will need to add a Microsoft command dialog control to your userform.
that is located under
the toolbox button. if it is not one of the displayed features then right
click in listed controls and you should see a note for
additional controls. look for the command dialog and select checkbox.

Hope this helps
John Coon

Private Sub cmdBrowse_Click()
On Error GoTo ErrHandler

' empty the filename in the dialog box
CommonDialog1.FileName = ""
CommonDialog1.MaxFileSize = 32000
CommonDialog1.Filter = "Drawing Files (*.dwg)|*.dwg"
CommonDialog1.DefaultExt = "*.dwg"
CommonDialog1.FilterIndex = 1
CommonDialog1.DialogTitle = "Select Drawing"
' Set Cancel to True.
CommonDialog1.CancelError = True

CommonDialog1.ShowOpen
TextBox5.Text = CommonDialog1.FileName


Exit Sub
ErrHandler:
' User pressed Cancel button.

Exit Sub

End Sub

 

Where can i learn about the textbox5 like you suggest. the book i have doesn't really say much about the syntax functionality of it. I have book called VBA for AutoCAD 2004

 

Search the newsgroups for CommonDialog.cls by Frank Oquendo.

Tim Riley

 

I guess i didn't stress how much of a newbie i am....i can't seem to get this to work for me. Im hoping someone can write a small example for me showing it working and then i know i will understand. Its just hard with limited knowledge trying to interpret whats going on and then putting it to work. all i need to do is open a dialog to open an .xls file so i can excute already set up commands in autocad. any help would be greatly appreciated. im working on decreasing my ignorance, but without a teacher its quite difficult

 

I posted a zip file in the custer-files for you.
getfilename-test.zip

Hope this helps
John Coon

 

Thanks alot for all the help. I've got it now