FileDialog vs. API and Multi-Select

[Guest]

Hello All,

I need the functionality of the FileDialog (only in OLB 10.0) in Access
2000 (only OLB 9.0). I have seen the great code to call the OpenFile API and
have gotten it to work. Problem is I cannot multi-select like I can by using
FileDialog.

Question, can I either A)Get Filedialog Functionality in Access 2000 or
B)Get Multi-Select Functionality via the API.

Here is the code in Access 2002 that works that I need to replicate the
ability in Access 2000. Frequently this module imports 50+ different
spreadsheet files so you can see why not having multi-select is a pain.

Sub ImportSurvey()
Dim MyFiles As FileDialog

Set MyFiles = Application.FileDialog(msoFileDialogFilePicker)
MyFiles.Show

For Each Item In MyFiles.SelectedItems
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Functions", Item, True, "FunctionData"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"ProcessApplications", Item, True, "ApplicationData"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Dependencies", Item, True, "DependencyData"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Processes", Item, True, "ProcessData"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Intangibles", Item, True, "IntangibleData"
MsgBox Item & " Imported"
Next Item

End Sub

 

[Douglas J Steele]

Using the code in http://www.mvps.org/access/api/api0001.htm you need to
pass ahtOFN_ALLOWMULTISELECT as a flag when calling the routine. You also
need to change the definition of the TrimNull function to something like:

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer

TrimNull = strItem
For intPos = Len(strItem) To 1 Step -1
If Mid(strItem, intPos, 1) = vbNullChar Then
TrimNull = Left(strItem, intPos - 1)
Exit For
End If
Next intPos

End Function

This is because what's returned is a string containing the name of the
folder, followed by a Null character (vbNullChar, or Chr(0)), followed by
the name of the first file, followed by a Null character, followed by the
name of the second file, followed by a Null character and so on until the
name of the last file, which is followed by 2 Null characters.

To retrieve the values from that string, you need to use the Split function:

Dim intLoop As String
Dim strFolder As String
Dim strReturn As String
Dim varFiles As Variant

strReturn = ahtCommonFileOpenSave(....)

If Len(strReturn) > 0 Then
varFiles = Split(strReturn, vbNullChar)
If UBound(varFiles) > 1 Then
strFolder = varFiles(0)
For intLoop = 1 To UBound(varFiles)

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Functions", _
strFolder & varFiles(intLoop), True, "FunctionData"
' etc.
Next intLoop
End If
End If

Note: that's untested air-code. I can't remember whether or not strFolder
will include the terminating \ it needs (you'll need to check, and add one
if necessary). You probably should also have a check inside the For intLoop
to ensure that there's actually something in varFiles(intLoop): somehow I
suspect that it's going to report at least one more file than really exists.
(It might be that it should be UBound(varFiles) - 1 instead of
UBound(varFiles) in the loop)

 

[Guest]

Thanks that makes sense and I will give it a try. FileDialog sure is nice,
wish it was around in Access 2000 lol.

 

[Douglas J. Steele]

The API approach is much more reliable. As well, I don't believe the
FileDialog can actually do File Save.