Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / VBScript
Article

A Filebrowser for VBA

Rate me:
Please Sign up or sign in to vote.
4.79/5 (17 votes)
3 Oct 2008MIT2 min read 273.1K   6.3K   45   31
An Excel demo of how to build a File Browser in an Office application
Sample Image - FileBrowse.gif

Introduction

Here is an example of how you can build a common file browser in an Office application. I won't explain all about the Windows API functions used because it would be too much for this article. I made this for Excel, but you can use it with all VBA-aware applications. When you open the Excel file and look at the whole project in the VBViewer (ALT+F11), you will see that the main work is done by the Module called FileBrowser. You can export it and import it to another project.

Explanations of the Code

VBScript
Declare Function th_apiGetOpenFileName Lib "comdlg32.dll" 
    Alias "GetOpenFileNameA" (OFN As thOPENFILENAME) As Boolean
Declare Function th_apiGetSaveFileName Lib "comdlg32.dll" 
    Alias "GetSaveFileNameA" (OFN As thOPENFILENAME) As Boolean
Declare Function CommDlgExtendetError Lib "commdlg32.dll" () As Long

The functions of GetOpenFileName are the secret to building this interface. For detailed information about these functions, please read an API guide. Note: OFN is the Container that allows you to browse files.

VBScript
Function StartIt()
    Dim strFilter As String
    Dim lngFlags As Long
    strFilter = thAddFilterItem(strFilter, "Excel Files (*.xls)", "*.XLS")
    strFilter = thAddFilterItem(strFilter, "Text Files(*.txt)", "*.TXT")
    strFilter = thAddFilterItem(strFilter, "All Files (*.*)", "*.*")
    Startform.filenameinput.Value = thCommonFileOpenSave(InitialDir:=
        "C:\Windows", Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, 
        DialogTitle:="File Browser")
   Debug.Print Hex(lngFlags)
End Function

This is the main function. The thAddFilterItems are shown in the drop-down menu to select the different file types. If you want to add other items, e.g. Word Files (*.doc), copy and paste the line and fill in what you like. What's important is the InitialDir, which gives the path at which to start. The function thCommonFileOpenSave rules the dialog and returns as a string containing the selected file. This string is given to the textbox filenameinput of Startform. So, now you have the control to work with a file in a form chosen by the user. The function thCommonFileOpenSave looks difficult, but most of the time it is only setting properties of the OFN object.

VBScript
If OpenFile Then fResult = th_apiGetOpenFileName(OFN) 
Else fResult = th_apiGetSaveFileName(OFN)
If fResult Then
    If Not IsMissing(Flags) Then Flags = OFN.Flags
        thCommonFileOpenSave = TrimNull(OFN.strFile)
    Else
        thCommonFileOpenSave = vbNullString
End If

This is the important part of the function thCommonFileOpenSave. The first line decides if you want to save or open a file, and calls the functions needed. The if-block statement defines the return value of the function. Either it returns OFN.strFile, which equals the name of the chosen file or a null string. The function TrimNull only kills Nullchars.

I hope the rest of the Module code is clear, but don't ask me about the setting of the hex-values. I found it in an API guide and I don't know what it does particularly. Also, please don't write about my bad English. I'm a student of physics and not a linguistic genius. Have fun with it.

History

  • 14th October, 2001: Initial post
  • 3rd October, 2008: Download file updated

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Business Analyst Die Mobiliar
Switzerland Switzerland
Bachelor in Computer Science.
Works as a Software Engineer (C#) for Mettler Toledo in Switzerland.
Develops software for formulation and SQC.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 936206427-May-14 0:28
Member 936206427-May-14 0:28 
GeneralMy vote of 5 Pin
javcube5-Sep-12 7:51
javcube5-Sep-12 7:51 
GeneralMettler Toledo DLL Pin
Ernesto Herrera9-Feb-09 8:07
Ernesto Herrera9-Feb-09 8:07 
GeneralRe: Mettler Toledo DLL Pin
Hasler Thomas10-Feb-09 22:00
professionalHasler Thomas10-Feb-09 22:00 
GeneralRe: Mettler Toledo DLL Pin
Torrock25-Sep-09 14:07
Torrock25-Sep-09 14:07 
AnswerRe: Mettler Toledo DLL Pin
Hasler Thomas27-Sep-09 22:57
professionalHasler Thomas27-Sep-09 22:57 
GeneralGreat post Pin
pacroman25-Nov-08 10:45
pacroman25-Nov-08 10:45 
GeneralGreat, clear and easy to use Pin
karljohnson7-May-08 13:42
karljohnson7-May-08 13:42 
GeneralUse In Excel Spreadsheet Pin
RM676613-Jul-06 5:18
RM676613-Jul-06 5:18 
GeneralIt Actually works in Outlook ! Pin
lucaso27-Jan-06 13:42
lucaso27-Jan-06 13:42 
QuestionHow to customize the dialog box? Pin
Member 229506325-Sep-05 20:43
Member 229506325-Sep-05 20:43 
Hi Hasler Thomas
Hi everyone

I've downloaded your sample code and found it very interesting and helpful.
Thank you for the code. However, I want to customize the "Open file" dialog box. I need to open the newest file in a folder which has thousands of files. Normally, the folder is sorted by filename which make it diffilcult to find the newly born file saved to that folder. I want to sort that folder by date (descending) so that the newest-created file will come out on top. It is easy to do so in Explorer. But in VB, the template of Explorer is not applied. I know I need to use API "GetOpenFileName" as you did in your code but I don't know how to flag it. Can you help me?

QuestionOpening more than one file? Pin
macot3927-Jun-05 12:10
macot3927-Jun-05 12:10 
AnswerRe: Opening more than one file? Pin
Hasler Thomas8-Jun-05 1:41
professionalHasler Thomas8-Jun-05 1:41 
GeneralRe: Opening more than one file? Pin
Hasler Thomas8-Jun-05 1:58
professionalHasler Thomas8-Jun-05 1:58 
GeneralRe: Opening more than one file? Pin
macot3928-Jun-05 6:26
macot3928-Jun-05 6:26 
GeneralVSS Folder browser Pin
Antony M19-Mar-04 3:01
Antony M19-Mar-04 3:01 
GeneralPlug-in for MS Outlook. please help Pin
Atif Bashir30-Jul-03 18:45
Atif Bashir30-Jul-03 18:45 
GeneralSpeed Pin
Lars Wiberg9-Jan-03 3:57
Lars Wiberg9-Jan-03 3:57 
GeneralRe: Speed Pin
Hasler Thomas16-Jan-03 1:14
professionalHasler Thomas16-Jan-03 1:14 
Generalcode in access Pin
27-Jun-02 3:11
suss27-Jun-02 3:11 
QuestionRe: code in access Pin
themanof8330-Sep-08 5:31
themanof8330-Sep-08 5:31 
AnswerRe: code in access Pin
Hasler Thomas2-Oct-08 5:09
professionalHasler Thomas2-Oct-08 5:09 
AnswerRe: code in access Pin
Hasler Thomas2-Oct-08 23:45
professionalHasler Thomas2-Oct-08 23:45 
QuestionWhat about GetOpenFilename method? Pin
20-Jun-02 13:38
suss20-Jun-02 13:38 
AnswerRe: What about GetOpenFilename method? Pin
Hasler Thomas27-Aug-02 1:14
professionalHasler Thomas27-Aug-02 1:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.