Copying multiple files from Windows explorer into MS Access

[Gina]

Hi,

I was wondering if there was a way of allowing MS Access to select multiple
files located on my PC in Windows explorer and insert them in a table in my
Access database, each file in a separate record.

Using the dialog box I am able to select one file at a time. This is
cumbersome. I need to select multiple files. This is what I have so far.

Dim strFilename As String
Dim HFileName As Variant
Dim oDialog As Object
Set oDialog = [Forms]![frmCheckForTables]!xDialog.Object

With oDialog
.DialogTitle = "Please Select Data File"
.FileName = ""
.Filter = "All(*.*)|*.*"
.FilterIndex = 1
.ShowOpen
If Len(.FileName) > 0 Then
[Forms]![frmCheckForTables]![DBPath] = .FileName
Else
Exit Sub
End If
End With



I may be asking for a lot. If you could give me a lead, I would be
extremely grateful.

Regards,
Gina

 

[Dirk Goldgar]

Hi,

I was wondering if there was a way of allowing MS Access to select
multiple files located on my PC in Windows explorer and insert them
in a table in my Access database, each file in a separate record.

Using the dialog box I am able to select one file at a time. This is
cumbersome. I need to select multiple files. This is what I have so
far.

Dim strFilename As String
Dim HFileName As Variant
Dim oDialog As Object
Set oDialog = [Forms]![frmCheckForTables]!xDialog.Object

With oDialog
.DialogTitle = "Please Select Data File"
.FileName = ""
.Filter = "All(*.*)|*.*"
.FilterIndex = 1
.ShowOpen
If Len(.FileName) > 0 Then
[Forms]![frmCheckForTables]![DBPath] = .FileName
Else
Exit Sub
End If
End With



I may be asking for a lot. If you could give me a lead, I would be
extremely grateful.

I don't know if that's the CommonDialog Control you're using or what,
but if you call the Windows API FileOpen dialog directly, you can set
options that will allow it to return multiple files. Code to call the
dialog may be found here:

www.mvps.org/access/api/api0001.htm

but the code needs to be tweaked to tell the dialog to allow the
selection of multiple files, and then you have to parse the returned
string to split out the individual files. When you allow multi-select,
the value returned by the dialog (if not null) consists of multiple
items separated by null characters (Chr(0)). The first item is the
folder path, followed by the first file, then the second file (if any),
etc.

 

[JR]

Try this:

Public Sub OpenMultiFilesDialog(ByRef userCanceled As
Boolean, _
ByRef FileNames() As String, ByRef path As String)

Dim theCommonDialog As ComDlg
Dim l As Long

Set theCommonDialog = New ComDlg
theCommonDialog.AllowMultiSelect = True
'theCommonDialog.Flags = cdlOFNLongNames Or
cdlOFNOverwritePrompt Or cdlOFNAllowMultiselect Or
cdlOFNExplorer
theCommonDialog.Directory = CurDir()
' display Open common dialog box.
If (theCommonDialog.ShowOpen) Then
userCanceled = False
path = CheckPathTerm(theCommonDialog.Directory)
ReDim FileNames(0 To theCommonDialog.FileTitles.Count -
1)
For l = 0 To theCommonDialog.FileTitles.Count - 1
FileNames(l) = theCommonDialog.FileTitles(l + 1)
Next
Else
userCanceled = True
path = ""
Erase FileNames
End If
Set theCommonDialog = Nothing
End Sub