Email Password Remember me?  Lost your password?

• Download source files and demo spreadsheet - 61.25 KB

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

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.

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.

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

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

You must Sign In to use this message board.

Per page 102550

FirstPrevNext

Mettler Toledo DLL Ernesto Herrera 9:07 9 Feb '09   Hi, I would like to know if exists some dll for to read and configure the weight screen from Mettler Toledo brand. Regards Sign In·View Thread·PermaLink 1.00/5 Re: Mettler Toledo DLL Hasler Thomas 23:00 10 Feb '09   Hi, there is no .dll for the static scales. If you want to send and receive data from a static MT scale's you need to implement the SICS protocol. But then you must pay licence fees for it. Sorry but that's a proprietary thing we earn money with. Regards Thomas By-the-way: For the industrial scales, metal detectors and XRay's we have just developed a new kind of protocol based on XML-RPC. Will be out this year. Sign In·View Thread·PermaLink Re: Mettler Toledo DLL Torrock 15:07 25 Sep '09   What the hell good is a scale that hooks up to a computer if you dont offer a way to extract the data.... Who cares if it displays in hyper terminal if the user still has to read the value and type it into a seperate program they can just as easyily read the weight off the scale itself without needing to hook it up to a PC. What a scam... Sign In·View Thread·PermaLink 1.00/5 Re: Mettler Toledo DLL Hasler Thomas 23:57 27 Sep '09   Hi, it is your right to think that a good scale needs a public library to hook it up to a PC. On the other side you must see, that 99% of our customers are from the food and pharmaceutical industry. That means our scales itself costs as much as a car. They are not made for your kitchen at home. And we have no bathroom scales e.g. As usual in industry software is a closed source thing. In industry the "leaders" are always afraid that something like you desribe in your post can happen. We spend a lot of time on implementing licencing scenarios, encryption and obfuscation of code. But if you really think that this a weakness of Mettler Toledo scales, then feel free to report this to our CEO or someone else of the leadership team. The name of the persons in the leadership team can be found at: http://ch.mt.com/ch/en/home/site_content/aboutus/management.html[^] Have a nice day. Thomas Sign In·View Thread·PermaLink Great post pacroman 11:45 25 Nov '08   Really great post, clear and easy to use Thank's a lot Sign In·View Thread·PermaLink Great, clear and easy to use karljohnson 14:42 7 May '08   Thanks a lot, you just saved me a few hours of hard work Cheers Sign In·View Thread·PermaLink 5.00/5 Use In Excel Spreadsheet RM6766 6:18 13 Jul '06   Thanks for showing your code. I have been trying to incorporate this code into an Excel spreadsheet as a means to prefilter files by file type and name thereby minimizing a user from selecting the wrong file. This code prefilters the file selection nicely, however, when a file is selected to be opened nothing happens. What am I doing wrong? Thanks Sign In·View Thread·PermaLink 5.00/5 It Actually works in Outlook ! lucaso 14:42 27 Jan '06   Great, been struggling with this problem for a few days. Because I send a lot mails with large attachments, I need to know what I sent someone, to be exact I need to register the full pathname of the file, just to keep up with revisions. That way I don't have to keep the attachments in my mails. Same goes with recieved attachments, I can now just register the path I put it in. I'll be happy to get rid of all these attachments, they are just clogging my system, a .pst with 2.5 GB attachments in it...... Happy I found this place... Sign In·View Thread·PermaLink How to customize the dialog box? Doan Phu Huyen 21:43 25 Sep '05   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? Sign In·View Thread·PermaLink 1.33/5 Opening more than one file? macot392 13:10 7 Jun '05   Your code allow to open only one file at a time. What could I change to open more than one file? Your help will be really appreciate, I'm not an expert in VB. Thanks Martin Côté Sign In·View Thread·PermaLink Re: Opening more than one file? Hasler Thomas 2:41 8 Jun '05   Hello, I haven't worked on this code for a long time. In fact the last three or four years I just used Java and C / C++. But I will have a look if I can help you. (Have to reread the MS Developer API) Thomas Sign In·View Thread·PermaLink Re: Opening more than one file? Hasler Thomas 2:58 8 Jun '05   Hello folks: for adding multiple section in the dialog, set the flag thOFN_ALLOWMULTISELECT in the function GetOpenFile(...) froorider :-> Sign In·View Thread·PermaLink Re: Opening more than one file? macot392 7:26 8 Jun '05   I've found that the function GetOpenFile() has no utility in the code. The Start_it() seem to do the samething. Now, I'm able to open a dialog with multi-selection activated. But, what I want to do now... is to print the name of each selected files in different cell of an excel worksheet. What I've obtained now is only one string containing all selected files separated by a space. Worksheets("proposal").Range("test") = thCommonFileOpenSave(InitialDir:="C:\", Filter:=strFilter,_ FilterIndex:=1, Flags:=lngFlags, DialogTitle:="File Browser") Do you have a trick to put each file to a single variable or in a arra and not all in the same package. Thanks again Sign In·View Thread·PermaLink 1.00/5 VSS Folder browser Antony M 4:01 19 Mar '04   Hi, i would like to implement a folder browser for VSS using VB6.(similar 2 the explorer in windows) how do i go abt doing it?. Antony Sign In·View Thread·PermaLink 2.00/5 Plug-in for MS Outlook. please help Atif Bashir 19:45 30 Jul '03   Hi Dear Hasler Thomas I've to make a plug-in for MS Outlook. This plug-in will take a particular date, and will delete all the attchments of the e-mails which are older than that specific date. Kindly guide me for the task. How can I start the task and which programming envirornment is best. Can I use VBA as best tool for the task? Provide/guide me some sample code if possible. I'll be very grateful to you. Waiting for your kind response. Atif Watch Your Thoughts for they will become your actions. Watch Your Actions for they will become your habits. Watch Your Habits for they will become your beliefs. Watch Your Beliefs for they will determine your destiny. Sign In·View Thread·PermaLink 1.00/5 Speed Lars Wiberg 4:57 9 Jan '03   It takes some time to show the dialog box reached by the Browse-button, no to long but I would like access the dialog box as fast as one can other dialog boxes, for example Open..., Save... Any sugestions? Lars Wiberg Sign In·View Thread·PermaLink Re: Speed Hasler Thomas 2:14 16 Jan '03   The problem is, that VB - Code is not a native Code and has to be interpreted first. That's why it is slower. If you would write this things in C++ and complie it to native Bytecode it would be much faster. That's why i.e. Java-Applikations are so slow on your machine. I guess it's not possible to make it faster. the froorider Sign In·View Thread·PermaLink code in access ami 4:11 27 Jun '02   i am trying to use this code in access but it is not working the browse box some times opens and some times not can any one help Sign In·View Thread·PermaLink 1.50/5 Re: code in access themanof83 6:31 30 Sep '08   I am getting the same thing happeninig in Access 2000!!! It seems to be that when I first load up a form and click the Open cmd (which runs the code) it doesn't work first time and I'm given a err 94 invalid use of null. But if I then go into design view and look at the code then return to form view it works perfectly........hmmmmmm. Any ideas? Sign In·View Thread·PermaLink Re: code in access Hasler Thomas 6:09 2 Oct '08   Hello, unfortunatly I do not have an Access 2000. I have only an Access 2003. Until now I haven't test the .bas - module in Access, but I'm trying to give you an answer. First I must have a look at this "old" code Until then I've found a little sample for Access 2000 using the same API on MSDN: Verwendung der GetOpenFilename-Methode der Comdlg32.dll[^] This should work for you. And it is time that I do some "refactorings" on this article. Regards Sign In·View Thread·PermaLink Re: code in access Hasler Thomas 0:45 3 Oct '08   I've send an updated sample (Excel and Access) to CodeProject. The only thing I ve found was in the GetOpenFile Function of the module there were useless EndIf's. Perhaps this caused your errors. But I wasn't able to reproduce your errors. Can I mail you the Access-sample? Then you could check if this still appears. Sign In·View Thread·PermaLink 2.00/5 What about GetOpenFilename method? Anonymous 14:38 20 Jun '02   Couldn't you just use the GetOpenFilename method in Excel? Sign In·View Thread·PermaLink 2.00/5 Re: What about GetOpenFilename method? Hasler Thomas 2:14 27 Aug '02   Hy Anonymous, you can just use the GetOpenFilename method. But it is made by the Office programmers themselves and you can only use the functionality they gave to it. For being honest it makes the same thing in the background like my code do, but mine you can change as you want, and give it more functionality. Greets Tom Sign In·View Thread·PermaLink 2.00/5 Re: What about GetOpenFilename method? Larry White 10:14 14 Mar '06   Good day, I am trying to use your code from "A Filebrowser for VBA" for capturing a filename including path into a SQL database field. I am using a Microsoft Access Project as the front-end. I associated the code with the OnGotFocus event property and receiving this error: Only comments may appear after End Sub, End Function, or End Property. I changed the form and field name to my form and added 2 additional file types for variables. LWhite Sign In·View Thread·PermaLink Nice Code, shame that you ripped it off! Anonymous 11:33 17 Feb '02   Dear Thomas, Very nice code, I think it's missing the following though: ' Code courtesy of: ' Microsoft Access 95 How-To ' Ken Getz and Paul Litwin ' Waite Group Press, 1996 {next time at least rename the functions} No_Sprockets Sign In·View Thread·PermaLink 2.00/5

Last Visit: 10:55 22 Nov '09     Last Update: 10:55 22 Nov '09 12 Next »