Browse for file

[john-86]

Hello all,
I was wondering if it were possible to, within a procedure, call a "browse for file" box. I would like the end user to be able to pick the spreadsheet from which they import the data using this highly recognisable way. If there is a way you can do this, is there also a way where you can restrict the file types available to .xls documents?

Kind regards,
John

[matthewspatrick]

You could use this function to get a full file name and path. If the user fails to pick a file, the function returns an empty string. Requires a reference to the Office library.



Function PickFile()

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.Title = "Choose file to import"
.AllowMultiSelect = False
.Filters.Add "Excel Workbooks", "*.xls"
.InitialView = msoFileDialogViewDetails
.InitialFileName = "c:\"
If .Show Then
PickFile = .SelectedItems(1)
Else
PickFile = ""
End If
End With

End Function

[john-86]

Patrick, thanks for the code! That's excatly what I was looking for... One quick question, do you happen to know what reference I need? I tried adding Microsoft Office 10.0 Object Library, but that doesn't seem to cut it. It throws up an error on the following line of code:

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Says the method isn't supported or words to that effect, so I'm guessing it's a reference problem.

Many, many thanks:bow:
John

[matthewspatrick]

John,

That's odd--I used Office 10.0 and Access 10.0 to develop it!

[Justinlabenne]

The zip attachment contains a module you can import into your Access project from the vbe. It is quite a bit of code but works across versions and requires no references to be set for use.

[john-86]

Hi Patrick, I use Access 9.0 and Office 10.0. That's maybe why. Does it not also depend on the priority of reference?

I have
VBA
Microsoft Access 9.0 Obj Lib
Microsoft DAO 3.6 Obj Lib
Microsoft Office 10.0 Obj Lib
etc.

Wow, Justin. Thank you very, very much! It's a (in my experience [limited]) formidible piece of code. Much appreciated :) I'll test it asap.

Kind regards,
John

Just after having a quick look at things. What do I need to pass to the function?

Thanks

[matthewspatrick]

Justin's code is going to be more robust. I am guessing that the v9 for Access library and v10 for Office library is creating a conflict. Can you get them to the same version?

[mvidas]

Similar to Justin's, you can get the standard Open dialog using:Option Explicit
Public Declare Function GetOpenFileNameB Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Long
'Public Declare Function GetSaveFileNameB Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
(pOpenfilename As OPENFILENAME) As Long
Public Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Public Enum EOpenFile 'you can use these as the .flags! &H80200 for multiselect
OFN_READONLY = &H1
OFN_OVERWRITEPROMPT = &H2
OFN_HIDEREADONLY = &H4
OFN_NOCHANGEDIR = &H8
OFN_SHOWHELP = &H10
OFN_ENABLEHOOK = &H20
OFN_ENABLETEMPLATE = &H40
OFN_ENABLETEMPLATEHANDLE = &H80
OFN_NOVALIDATE = &H100
OFN_ALLOWMULTISELECT = &H200
OFN_EXTENSIONDIFFERENT = &H400
OFN_PATHMUSTEXIST = &H800
OFN_FILEMUSTEXIST = &H1000
OFN_CREATEPROMPT = &H2000
OFN_SHAREAWARE = &H4000
OFN_NOREADONLYRETURN = &H8000&
OFN_NOTESTFILECREATE = &H10000
OFN_NONETWORKBUTTON = &H20000
OFN_NOLONGNAMES = &H40000
OFN_EXPLORER = &H80000
OFN_NODEREFERENCELINKS = &H100000
OFN_LONGNAMES = &H200000
End Enum
Sub GOFExample()
Dim vFile As String
vFile = GetOpenFileName()
If vFile <> "" Then MsgBox vFile
End Sub
Public Function GetOpenFileName(Optional ByVal vFileFilter As String, Optional ByVal _
vWindowTitle As String, Optional ByVal vInitialDir As String, Optional ByVal _
vInitialFileName As String) As String
Dim OFN As OPENFILENAME, RetVal As Long
OFN.lStructSize = Len(OFN)
OFN.hwndOwner = 0
OFN.hInstance = 0
OFN.lpstrFile = IIf(vInitialDir = "", Space$(254), vInitialDir & Space$(254 - Len(vInitialDir)))
OFN.lpstrInitialDir = IIf(vWindowTitle = "", CurDir, vInitialDir)
OFN.lpstrTitle = IIf(vWindowTitle = "", "Select File", vWindowTitle)
OFN.lpstrFilter = IIf(vFileFilter = "", "All Files (*.*)" & Chr(0) & "*.*", _
Replace(vFileFilter, ",", Chr$(0)))
OFN.nMaxFile = 255
OFN.lpstrFileTitle = Space$(254)
OFN.nMaxFileTitle = 255
OFN.flags = 0
RetVal = GetOpenFileNameB(OFN)
If RetVal Then GetOpenFileName = Trim$(OFN.lpstrFile)
End FunctionMatt

[Justinlabenne]

To test my code, this will display what you pick in a message box:

Sub tester()
MsgBox fBrowseFor(BIF_RETURNONLYFSDIRS, CSIDL_DRIVES, True)
End Sub


And then use it in your app kind of like this:
Sub tester2()
Dim szPath As String
szPath = fBrowseFor(BIF_RETURNONLYFSDIRS, CSIDL_DRIVES, True)
If Len(szPath) > 0 Then
MsgBox szPath
Else
MsgBox "Cancel Pressed"
End If
End Sub

You need to test for the cancel press by determining if a zero length string was returned by the fBrowseFor function.

[john-86]

Endless thank yous Justin. That's a nice piece of code. I'm, unfortunately, having a problem getting it to run. It seems to dislike This part of the code:


Public Function fBrowseFor(ByVal BrowseFor As BrowseType, _
ByVal RootFolder As FolderType, _
Optional bCenter As Boolean) As String
Dim nNullPos As Integer
Dim lpIDList As Long
Dim lres As Long
Dim lRootID As Long
Dim szPath As String
Dim BInfo As BrowseInfo

'Foolproof way to find the main Access window handle
Dim AppWnd As Long
If Val(Application.Version) >= 10 Then
AppWnd = Application.hWndAccessApp
Else
AppWnd = GetAccesshWnd()
End If


It seems to have problems with the if condition. I get an error message telling me "method or data member not found" and highlights the ".Version" section of the if condition. Any ideas?

Thanks:bow:
John

[Justinlabenne]

Try this, that version test isn't really needed anyway.

[john-86]

Hi Justin,
that works a lot better now. I really appreciate you helping me out like this! Sorry to trouble you again, but what modifications do I make to search for a file as opposed to a folder?

Kind regards,
John

[Imdabaum]

I don't know if I need to post this in a different thread, but since I found my solution here, I thought I would ask another question reguarding this.

Now that I can open a file browser... is there anyway I can limit the browser to only select from files that exist?

Basiclly, I have a main page with sub form that allows you to upload photos these photos are handled by code to be placed into a folder represented by the ID number of the project. There is an image on the main page that I want users to be able to click in order to choose a display image that auto-loads. Right now I have it so when you click the image frame, it opens to the ID folder, but was wondering if there was a way to make sure they selected from that folder so we don't mix project images.