VB in MS Excel - Filebrowse option

[DannyAuber]

I'm writing some VB in Excel

I want to prompt the user for a filename, so I want an Inputbox with a "Browse" option where they can browse to a file, and then hit OK - the inputbox will then return the path and file

No idea how to do it - doesn't seem to be a feature of inputbox.

Can someone help ?

thanks in advance

dan

 

[Michael42]

Use the Common Controls Dialog (CMDialog). It is a vbx file that must be loaded into you project (should be by default).

Also, you can use windows API to do the same if you do not want to use the vbx.

Here is an example I have used using the vbx:

On Error GoTo err_mnu_insert_file_click

'Confirm
mMsg = MsgBox("Before importing a file from other Word Processing software you must" & mNL & "save your document as an ASCII text file." & Chr(10) & Chr(10) & "Do you still wish to continue?", MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2, "Import?&quot
If mMsg = IDNO Then
Exit Sub
End If

'Init Common Dialog to Get File
Dim mImportFile As String
MDIForm1!CMDialog1.DialogTitle = "Select ASCII Text File to Import"
MDIForm1!CMDialog1.Flags = OFN_FILEMUSTEXIST + OFN_HIDEREADONLY
MDIForm1!CMDialog1.Filename = "*.txt"
MDIForm1!CMDialog1.Filter = "All Files(*.*)|*.*|Text(*.txt)|*.txt"
MDIForm1!CMDialog1.FilterIndex = 2
MDIForm1!CMDialog1.CancelError = True
MDIForm1!CMDialog1.Action = 1

'Extract File Name and Path
mImportFile$ = MDIForm1!CMDialog1.Filename

'Validate
If mImportFile = "" Then
mMsg = MsgBox("Cannot Import because file not chosen.", MB_ICONINFORMATION, "Choose File&quot
Exit Sub
End If

'Confirm Chosen Import File
mMsg = MsgBox("Import: " & mImportFile, MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2, "Import?&quot
If mMsg = IDNO Then
Exit Sub
End If

mHEStatus% = HELoadDoc(HighEdit1.hWnd, mImportFile$, FILEFORMAT_ANSI) 'FILEFORMAT_RTF
If mHEStatus = True Then ' Import Operation OK
mMsg = MsgBox("Import of file successful.", MB_ICONINFORMATION, "Import Status&quot
End If


Exit Sub


err_mnu_insert_file_click:
Select Case Err
Case 32755 ' Common Dlg CANCEL Error
Exit Sub
Case Else
mMsg = MsgBox(Error & mNL & "Error number: " & Str$(Err) & mNL & "Form: " & Me.Tag & mNL & "Sub: " & "mnu_insert_file_click", MB_ICONINFORMATION, "Internal Error&quot
Resume Next
End Select

 

[Michael42]

Ooops just noticed "VB IN EXCEL". I assumed it was for VB3 as this is a VB3 forum.

You will have to use a Windows API call OR the CommonControl ActiveX version in running win9x/NT/2k/XP

Hope this helped,

Michael

 

[MiggyD]

if it's Excel2k you should be able to Right-click the Toolbox and add 'Microsoft Common Controls xxxx'.

once added to toolbox add it to the form.

Try this:

create a new form, add a textbox (large one), add a common dialog control, and 2 command buttons. Set Textbox1.multiline = true. C/p the following and run the code.


Private Sub CommandButton1_Click()
CommonDialog1.ShowOpen
If CommonDialog1.FileName <> Empty Then
TextBox1.Text = CommonDialog1.FileName
End If
End Sub

Private Sub CommandButton2_Click()
End
End Sub


HTH.
--MiggyD