Loop to process multiple selected files

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I have a module which imports text files into a database, but I need
to be able to select multiple files to be processed, this is what I
have so far


Public filename As String
Sub SelectFile()
Dim fd As FileDialog
Dim objfl As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.ButtonName = "Select"
.AllowMultiSelect = True
.Filters.Add "Text Files", "*.txt;*.txt", 1
.Title = "Choose your files"
.Show

For Each objfl In .SelectedItems
filename = objfl
Next objfl

If .SelectedItems.Count > 0 Then
For i = 1 To .SelectedItems.Count
Call OpenText 'this is what imports the text
Next i
End If
End With
End Sub
I think I need to combine the For and If statements to make it work,
it will import the first file selected and if I choose two files it
will repeat the import of the first file selected. Any help is greatly
appreciated
Richard
 
You didn't show the 'OpenText' procedure BUT...
it looks like you need to tell the 'OpenText' procedure which file to process.

'- - - - - - - - - - - - -
For i = 1 To .SelectedItems.Count
Call OpenText( .SelectedItems(i))
Next i
'- - - - - - - - - - - - -
Sub OpenText(strFile as string)
'process strFile
End Sub
'- - - - - - - - - - - - -
 
I only have A2K, so I can't test this.

Looking at http://support.microsoft.com/kb/288543

I have modified you procedure:
----Untested----
'-----------------------------------------------------
Option Compare Database
Option Explicit

Sub SelectFile()
Dim fd As FileDialog
Dim objfl As Variant

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Use a With...End With block to reference the FileDialog object.
'(Access XP and higher)
With fd

.ButtonName = "Select"
.AllowMultiSelect = True
.Filters.Add "Text Files", "*.txt;*.txt", 1
.Title = "Choose your files"
.Show

If .SelectedItems.Count > 0 Then

'Step through the FileDialogSelectedItems collection.
For Each objfl In .SelectedItems
Call OpenText 'this is what imports the text
Next objfl

End If

End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub
'-----------------------------------------------------


HTH
 
Back
Top