How to loop multiple file imports into Access 5

[Quintios]

I'm sure this has been asked before but I couldn't find it in the search. I have a few hundred individual text files that I want to import into Access. They are all in one directory and they are named differently (obviously!) but of course have the same file extension.

How do I get Access to loop through all the files and import them and not attempt to import a file twice? Deleting the files as they are imported is just fine; making backups as a part of the process is not a problem.

Thanks in advance for your help!

Onwards,

Q-

 

[jfgambit]

Assuming all the text files are delimited you can use the following:

Private Function GetFileNames()
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFill As String, strFolderPath As String

strFolderPath = "h:\workfile\textfile\"
Set objFS = CreateObject("Scripting.FileSystemObject&quot Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
DoCmd.TransferText acImportDelim, , "YourTargetTableNameHere", strFolderPath & objF1.Name, True
End If
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

End Function

HTH


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[Quintios]

Sweet! Can't wait to try it out!

I'm quite familiar with the 'for..each' loops, but I never quite know what object to use with them...

Thanks!

Onwards,

Q-

 

[Quintios]

How would I do this and incorporate the FileDialog function that is available with Office XP?

I have the following code, but I don't know how to return the result to a string to use in your code:

Set dlgOpenFolder = Application.FileDialog(msoFileDialogFolderPicker)

Don't really know where to go from here. I did a search through this forum but everyone is recommending to use the common dialog box code or simply saying "use FileDialog". Nothing specific (that I found) on selecting a *folder*...

Thanks in advance!

Onwards,

Q-

 

[Quintios]

I think I might have it here, I'm testing it right now:

Private Function GetFileNames()
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFill As String, strFolderPath As String
Dim dlgOpenFolder As FileDialog

Set dlgOpenFolder = Application.FileDialog(msoFileDialogFolderPicker)

dlgOpenFolder.Show

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = dlgOpenFolder
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
   If Right(objF1.Name, 3) = "txt" Then
   DoCmd.TransferText acImportDelim, , "YourTargetTableNameHere", strFolderPath & objF1.Name, True
End If
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing
Set dlgOpenFolder = Nothing

End Function

Onwards,

Q-

 

[Quintios]

keywords: dialog box dialogbox FileOpen DialogFileOpen Folder Open FolderOpen DialogFolderOpen Import Files Importfiles Import Multiple Files


Actually, that didn't work.

Here is my final solution, complete with progress bar! This will allow you to import any number of TEXT files (specified in the code marked '.Filter') into an Access database. Somebody give me a star!

Private Sub cmdImportLogs_Click()
Dim fd As FileDialog
Dim strImportTable As String
Dim strImportSpec As String
Dim vrtSelectedItem As Variant 'Variable to contain selected path of the file(s)
Dim intTotal As Integer
Dim intCount As Integer
Dim strMsg As String
Dim strTitle As String
Dim strResponse As String
Dim intProgress As Integer          ' Used for Progress Bar
Dim varRetVal As Variant            ' Used for Progress Bar

strImportTable = "tblPlayerDataImport"
strImportSpec = "Player_Import_Spec"

'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.
With fd

'Allow the user to select multiple files.
.AllowMultiSelect = True

.Filters.Add "Text Files", "*.txt", 1
.Title = "Select All Player Logs to import..."

'Use the Show method to display the File Picker dialog box and return the user's action.
'If the user presses the action button...
If .Show = -1 Then

'Start the count for the progress bar and check to make sure the
'appropriate number of files have been selected
intTotal = .SelectedItems.Count

'Set up the dialog box before finally importing the files
strMsg = "A total of " & intTotal & " files will be imported"
strTitle = "Total Files To Be Imported"
strResponse = MsgBox(strMsg, vbOKCancel, strTitle)

'Check to make sure the proper number of files will be imported
If strResponse = vbCancel Then
    MsgBox "I will exit and not import the files.", vbOKOnly
    Exit Sub
Else
End If

'Initialize the progress meter.
intCount = 0
varRetVal = SysCmd(acSysCmdInitMeter, "Importing Files...", intTotal)

'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems

'Increment the progress bar
varRetVal = SysCmd(acSysCmdUpdateMeter, intCount)

'Import the file using the assigned ImportSpec and ImportTable
DoCmd.TransferText acImportDelim, strImportSpec, strImportTable, vrtSelectedItem, False

'Increment the counter
intCount = intCount + 1

Next
'If the user presses Cancel...
Else
    
    MsgBox "No Files were imported!", vbOKOnly, "Player Logs Import Status"

End If
End With

'Kill the progress bar
varRetVal = SysCmd(acSysCmdRemoveMeter)

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

varRetVal = SysCmd(acSysCmdClearStatus)

End Sub

Onwards,

Q-

 

[jfgambit]

Q...

Since you posted the question, you can give yourself the star...




"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[Quintios]

I can't...

Hopefully someone somewhere someday will be able to use the code.

But thanks for everyone's help. You guys always do a great job of either completely solving my problem or putting me in the right direction.

I love tek-tips!

Onwards,

Q-