File Dialog Using API

[Huckle]

I have users using the run-time version of access which does not allow the
newer (access 2002 and up) FileDialog functions.

I found great information about using a direct call to the API from
http://www.mvps.org/access/api/api0001.htm on another post in this forum.

Unfortunately, the code is a little over my head but I was able to tweek it
open a selected file.

Is there any way to allow it to select mulitple files? This is a fairly
essential part of my program as users typically have to open about 5
different files at one time.

PS: Thanks to everyone in the forum, this has been an amazing resource!

 

[Biz Enhancer]

Hi Huckle,

You could trap the values from selected files into a recordset and then loop
through the recordset using FollowHyperlink command to open each file.

Are the files going to be the same ones all the time or are the users likely
to have to select a different set each time?

Regards,

Nick.

 

[Tom Wickerath]

Hi Huckle,

Your question prompted me to do a Google Advanced Group search, because I've
been bugged by this very issue myself. I think I found a solution for you,
posted by "DomThePom", here:

http://groups.google.com/group/microsoft.public.access.modulesdaovba/msg/59405535b47dc0d7

I found that the example functions for multiselect returned the folder and
then a space delimited list of files selected within the same folder. For
example:

G:\Test MyFile.txt AnotherFile.txt YetAnotherFile.txt

Okay, but what if one has spaces in the folder path, or in the file names?
That makes it a bit more difficult to parse out. For example:

G:\Test My File.txt Another File.txt Yet Another File.txt

I modified two of the functions, and so far it seems to work okay, but
admittedly I've only done minimal testing:

To the function: Function dhFileDialog

I added two lines of code near the bottom of this procedure:

' Return the result
If (ofn.lngFlags And OFN_ALLOWMULTISELECT) = 0 Then
dhFileDialog = dhTrimNull(ofn.strFile)
Else
dhFileDialog = ofn.strFile
dhFileDialog = drRightTrimNull(dhFileDialog) '<---Added this
dhFileDialog = Replace(dhFileDialog, vbNullChar, ",") '<---Added this
End If
Else
dhFileDialog = Null
End If
End Function


I also modified the function: GetAccessDBName
This function can be used to test the procedure from the Immediate Window.
For example: ?GetAccessDBName ("Hello Dolly")


Function GetAccessDBName(ByVal strTitle As String) As String
On Error GoTo ProcError

'calls common dialog for test file with title strtitle

Dim strInitDir As String
Dim strfilter As String

strfilter = "Text files" & vbNullChar & "*.txt" & vbNullChar & vbNullChar

strInitDir = GetPath(CurrentDb.Name)

' Same as above, but allow multiselect
GetAccessDBName = Nz(dhFileDialog(strInitDir, _
strfilter, _
0, _
"txt", , _
strTitle, , , _
dhOFN_OPENEXISTING Or OFN_ALLOWMULTISELECT Or OFN_EXPLORER), "")

ProcExit:
Exit Function
ProcError:
MsgBox Error(Err)
Resume ProcExit
End Function


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Krzysztof Pozorek [MVP]]

I have users using the run-time version of access which does not allow the
newer (access 2002 and up) FileDialog functions.

I found great information about using a direct call to the API from
http://www.mvps.org/access/api/api0001.htm on another post in this forum.

Unfortunately, the code is a little over my head but I was able to tweek
it
open a selected file.

Is there any way to allow it to select mulitple files? This is a fairly
essential part of my program as users typically have to open about 5
different files at one time.

PS: Thanks to everyone in the forum, this has been an amazing resource!

Exist very simple method that allow multiselect (in older versions of Access
too), here it:

Dim sFile As String, sFilter As String
WizHook.Key = 51488399
sFilter = "Access file (*.mdb)"
WizHook.GetFileName 0, "", "", "", _
sFile, "", sFilter, 0, 0, 8, True
MsgBox "You choose file(s): " & sFile

Kris [MVP], Poland
www.access.vis.pl

 

[Douglas J. Steele]

I don't understand why you'd make those code changes, Tom. The function
GetMultipleFiles returns an array of the files selected.

When using multiselect, what's returned is the folder name, followed by a
Null character, followed by the name of the first file, followed by a Null
character and so on until the last file name, which is followed by two Null
characters.

Personally, I'd change the drRightTrimNull function to remove the pair of
Null characters from the end:

Function drRightTrimNull(ByVal strValue As String) As String
' Removes all characters to right of pair of Null characters

Dim lngI As Long

lngI = InStr(strValue, vbNullChar & vbNullChar)

if lngI > 0 Then
drRightTrimNull = Left$(strValue, lngI - 1)
Else
drRightTimeNull = strValue
End If

End Function

I'd add another declaration to GetMultipleFiles:

Dim varReturned As Variant

and then change the code after the call to dhFileDialog to

If IsNull(varFiles) = True Then
GetMultipleFiles = Null
Else
varFiles = drRightTrimNull(varFiles)

' Split what was returned into an array
varReturned = Split(varFiles, vbNullChar)

' Determine the number of files we're dealing with.
intFileCount = UBound(varReturned) - LBound(varReturned) + 1

' If just 1 file then simple assignment
If intFileCount = 1 Then
ReDim strArrFiles(0)
strArrFiles(0) = drRightTrimNull(Trim(varFiles))
Else
' Redim an array of filenames
ReDim strArrFiles(intFileCount - 1)

' Populate the array of file names
For intI = 1 To intFileCount
strArrFiles(intI - 1) = varReturned(0) & varReturned (intI - 1)
Next intI
End If
GetMultipleFiles = strArrFiles
End If

 

[Huckle]

Wow! You are all amazing. I need to parse through all this, but Krzysztof's
solution seems to work with my Access 2000 and in the user's run-time
environment as well.

Does anyone have any direction on where to learn more about WizHook and this
particular one? I'm haven't been able to find much about it and would like
to know what other options it has, such as changing the title of the dialog
box, etc.

I will need to combine the parsing of the returned list of files using Tom
and Douglas' code snippets. This will all take some time, but again- Thanks!

 

[Krzysztof Pozorek [MVP]]

(...)

Does anyone have any direction on where to learn more about WizHook and
this
particular one? I'm haven't been able to find much about it and would
like
to know what other options it has, such as changing the title of the
dialog
box, etc.

WizHook is rather undocumented. But below is a bit more complex description:

Function accDialog(Open_or_save As Boolean)
On Error Resume Next
Dim lRet As Long, lView As Long, lFlags As Long
Dim sInitialDir As String, sFile As String, sFilter As String
WizHook.Key = 51488399 'secret WizHook key
lView = 0 '3 - List view, 0 - Detail view
lFlags = &H40 + &H8 '&H8 = multiselect(!)
sInitialDir = "C:\Databases\"
sFilter = "Access (*.mdb, *.mde, *.mdw, *.asp)|All files (*.*)"
lRet = WizHook.GetFileName(hWndAccessApp, "", "My Caption", "OK", _
sFile, sInitialDir, sFilter, 0, lView, lFlags, Open_or_save)
' "OK" will appear on button of choice of file
If lRet = -302 Then 'user pressed Cancel
accDialog = Null
Else 'user pressed OK
accDialog = sFile
End If
End Function

Kris [MVP], Poland
www.access.vis.pl

 

[Huckle]

Thanks again to everyone for all the help. After combining all the ideas,
this is the code I came up with. I'm not very experienced, so my code is a
little ugly. I will try to clean it up more later.

'**********
Private Sub cmdTest_Click()
On Error GoTo ErrorCmdTest

Dim sFile As String
Dim sFileOriginal As String
Dim sFilter As String
Dim varList As Variant
Dim intListCount As Integer

sFile = "H:\Engin\_Projects\_Current"
sFileOriginal = sFile 'Use later to determine if canceled
WizHook.Key = 51488399
sFilter = "All Files (*.*)"
WizHook.GetFileName 0, "", "Select File(s) To Open (ctrl)", "", sFile, "",
sFilter, 0, 0, 8, True
'GetFileName (hwndOwner As Long, AppName As String, DlgTitle As String,
OpenTitle As String, File As String, InitialDir As String, Filter As String,
FilterIndex As Long, View As Long, flags As Long, fOpen As Boolean) As Long
FilterIndex As Long, Long View As the flags As Long, fOpen As Boolean) As Long

sFile = Trim(sFile)

If sFile = sFileOriginal Then
'No files were selected (happens when user selects cancel from dialog box)
GoTo ExitCmdTest
End If

varList = Split(sFile, Chr(9)) 'GetFileName seperates with tabs

intListCount = UBound(varList) - LBound(varList) + 1

If intListCount = 0 Then
MsgBox "Error, intListCount = 0)"
Exit Sub
Else
For intListCount = 1 To intListCount
Application.FollowHyperlink varList(intListCount - 1), , True, False
Next intListCount
End If

ExitCmdTest:
Exit Sub

ErrorCmdTest:
MsgBox Err.Description
Resume ExitCmdTest

End Sub
'************

 

[Douglas J. Steele]

To be honest, I'd recommend against using WizHook, since I don't believe
it's available in newer versions of Access, meaning you'll have problems
upgrading in the future.

Using the API is far more reliable.

 

[Tom Wickerath]

Hi Doug,

I don't understand why you'd make those code changes, Tom. The function
GetMultipleFiles returns an array of the files selected.

I guess I didn't see the GetMultipleFiles function. The function I
volunteered, "GetAccessDBName" (which would have been more appropriately
named "GetTextFileNames"), did not use the GetMultipleFiles function at all.

Personally, I'd change the drRightTrimNull function to remove the pair of
Null characters from the end:

I made your suggested change, but discovered a compile error in the process:

drRightTimeNull = strValue

should have been:

drRightTrimNull = strValue

No big deal, but I thought I would bring this to your attention.

However, I still cannot get your suggested version to work. When the very
last statement in GetMultipleFiles is executed, ie. the line that reads "End
Function", I am getting a run-time error 13: type mismatch. Do you know why
this is so?

Also, the intFileCount is correct *only* if one selects one file:
' Determine the number of files we're dealing with.
intFileCount = UBound(varReturned) - LBound(varReturned) + 1
Debug.Print "intFileCount = " & intFileCount

If I select just one file, I get: intFileCount = 1
printed to the Immediate Window. However, if I select n files, I get:
intFileCount = n+1

In both cases, I get the run-time error. Can you take a second look at your
suggested revisions?


Thanks,

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tony Toews [MVP]]

Your question prompted me to do a Google Advanced Group search, because I've
been bugged by this very issue myself. I think I found a solution for you,
posted by "DomThePom", here:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/msg/59405535b47dc0d7

Thanks. Blogged.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tom Wickerath]

Hi Tony,

Rather than blog that old posting, perhaps we should see if we can get Arvin
to update the sample on the MVPS web site?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tony Toews [MVP]]

Rather than blog that old posting, perhaps we should see if we can get Arvin
to update the sample on the MVPS web site?

Trouble is that's copyrighted code by the authors of the Developers
Handbook. Also I'm sure sample code exists elsewhere. Yup, at
http://vbnet.mvps.org/index.html?code/comdlg/fileopendlg.htm.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tom Wickerath]

Hi Doug,

I decided to re-visit this issue myself. It turns out that I was getting the
same run-time error 13 with the original code. (I had not discovered this
earlier, because the first time I attempted to run the GetMultipleFiles
function, I did so with all of the modifications that you had suggested).

Finally, I clicked on the Help button for the error dialog, and towards the
end I realized what the problem was:

"At run time, this error typically indicates that a Variant used in an
expression has an incorrect subtype, or a Variant containing an array appears
in a Print # statement."

I was using this from the Immediate Window to test:

?GetMultipleFiles("txt", "Hello")

which would attempt to print the results of an array. I should have been
using this, instead:

GetMultipleFiles "txt", "Hello"

So, just for fun, I went ahead and added a few lines of code to the end of
the procedure to print out the selected files to the Immediate Window:

GetMultipleFilesDougSteele = strArrFiles

'Print results to Immediate Window
For intI = LBound(strArrFiles) To UBound(strArrFiles)
Debug.Print intI, strArrFiles(intI)
Next intI

End If

End Function


You can disregard my earlier request to take a second look at your suggested
revisions.


Thanks,

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

 

[Douglas J. Steele]

Oops, slight error. It should be

For intI = 1 To (intFileCount - 1)
strArrFiles(intI - 1) = varReturned(0) & varReturned (intI)
Next intI