Need Help Importing Multiple TxT Files (1 Viewer)

[DQuick]

Hi all,

I have been trying to get this code working for a few days and have not been able to solve it. :banghead:

Currently I have a code that imports a single text file into a table without problems.
I need to be able to selecte multiple text files and import them at once.

Here is my current working code.....

Private Sub Import_Click()
 
Dim strFilter As String
   Dim strInputFileName As String
strFilter = ahtAddFilterItem(strFilter, "Text File (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
                                    OpenFile:=True, _
                                    Filter:=strFilter, _
                                    DialogTitle:="Please select new file...", _
                    Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
 
DoCmd.TransferText acImportDelim, "Import Specs", "Data Table", strSaveFileName, True, ""

Thanks for any help.

 

[jdraw]

have not been able to solve it.

What exactly is it NOT doing?
Do you get an error message?

 

[DQuick]

What exactly is it NOT doing?
Do you get an error message?

Well, with the current code I am only able to select one file.
I haven't been able to find the code I need to import more than one.

 

[jdraw]

You might wish to research/google vba loops.
see http://www.learn.net.au/vba/vba-loops
Your code only asks for 1 import.

 

[DQuick]

I am unable to visit that site while at work.

Please excuse my ignorance but which part of the script only asks for 1 import?

 

[jdraw]

DoCmd.TransferText acImportDelim, "Import Specs", "Data Table", strSaveFileName, True, ""

I'm not sure where you got the code you displayed, but you didn't show the whole procedure.

You need a loop structure to do some process a number of times.

 

[DQuick]

So I was able to find this code from here..
http://www.utteraccess.com/forum/Doug-Steele-s-djsTest-t1918803.html


I am getting a compile error here:
"If Len(Dir(varInputFiles(lngLoop)) > 0 Then"

Private Sub Import_Click()
 
Dim lngLoop As Long
Dim strFilter As String
Dim strTable1 As String
Dim varInputFiles As Variant
 
  strTable1 = "Data Table"
  strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
  varInputFiles = ahtCommonFileOpenSave( _
                                    OpenFile:=True, _
                                    Filter:=strFilter, _
                                    DialogTitle:="Please select file...", _
                    Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
 
[COLOR=green]' Import file(s).[/COLOR]
  For lngLoop = LBound(varInputFiles) To UBound(varInputFiles)
     If Len(Dir(varInputFiles(lngLoop)) > 0 Then
      DoCmd.TransferText acImportDelim, "Import Specs", strTable1, varInputFiles(lngLoop), False
    End If
  Next lngLoop
End Sub

 

[DQuick]

I was able to get it worked out.
After reviewing it this morning, I realized I forgot a “)”



Working code.

Private Sub Import_Data_Click()

Dim lngLoop As Long
Dim strFilter As String
Dim strTable1 As String
Dim varInputFiles As Variant
  
  lngFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_NOCHANGEDIR Or ahtOFN_EXPLORER Or _
                ahtOFN_ALLOWMULTISELECT Or ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY
  
  strTable1 = "Data Table"
 
  strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
  varInputFiles = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select new files...", _
                Flags:=lngFlags)
  
  For lngLoop = LBound(varInputFiles) To UBound(varInputFiles)
    If Len(Dir(varInputFiles(lngLoop))) > 0 Then
      DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, varInputFiles(lngLoop), False
    End If
  Next lngLoop
 
MsgBox "Import Complete", vbInformation, "Complete"
End Sub

 

[DQuick]

I just realized this only allows the import of more than one file.
If I select a single file I receive the following error....

Run-time Error '13':
Type mismatch

For lngLoop = LBound(varInputFiles) To UBound(varInputFiles)

 

[pr2-eugin]

Why don't you change it to For Each instead of For someCounter = someValue?

Private Sub Import_Data_Click()

    Dim strFilter As String
    Dim strTable1 As String
    Dim varInputFiles As Variant[COLOR=Red][B], varCtr As Variant[/B][/COLOR]
  
    lngFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_NOCHANGEDIR Or ahtOFN_EXPLORER Or _
                ahtOFN_ALLOWMULTISELECT Or ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY
  
    strTable1 = "Data Table"
     
    strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
    varInputFiles = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select new files...", _
                Flags:=lngFlags)

    For [COLOR=Red][B]Each varCtr In[/B][/COLOR] varInputFiles
        If Len(Dir([COLOR=Red][B]varCtr[/B][/COLOR])) > 0 Then
          DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, [COLOR=Red][B]varCtr[/B][/COLOR], False
        End If
    Next [COLOR=Red][B]varCtr[/B][/COLOR]

    MsgBox "Import Complete", vbInformation, "Complete"
End Sub

NOTE: Not Tested !

 

[DQuick]

Still hanging on:

"For Each varCtr In varInputFiles"

Why don't you change it to For Each instead of For someCounter = someValue?

Private Sub Import_Data_Click()
 
    Dim strFilter As String
    Dim strTable1 As String
    Dim varInputFiles As Variant[COLOR=red][B], varCtr As Variant[/B][/COLOR]
 
    lngFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_NOCHANGEDIR Or ahtOFN_EXPLORER Or _
                ahtOFN_ALLOWMULTISELECT Or ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY
 
    strTable1 = "Data Table"
 
    strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
    varInputFiles = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select new files...", _
                Flags:=lngFlags)
 
    For [COLOR=red][B]Each varCtr In[/B][/COLOR] varInputFiles
        If Len(Dir([COLOR=red][B]varCtr[/B][/COLOR])) > 0 Then
          DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, [COLOR=red][B]varCtr[/B][/COLOR], False
        End If
    Next [COLOR=red][B]varCtr[/B][/COLOR]
 
    MsgBox "Import Complete", vbInformation, "Complete"
End Sub

NOTE: Not Tested !

 

[pr2-eugin]

Hanging with the same error? Have you debugged the error?

 

[DQuick]

Hanging with the same error? Have you debugged the error?

Yes still the same error and when I click debug, it highlights:
"For Each varCtr In varInputFiles"


I am still somewhat new to VBA

 

[DQuick]

Your code works if I select more than one file, but still gets stuck if I only select a single file.

 

[pr2-eugin]

Try this..

Private Sub Import_Data_Click()
    Dim strFilter As String
    Dim strTable1 As String
    Dim varInputFiles As Variant, varCtr As Variant
  
    lngFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_NOCHANGEDIR Or ahtOFN_EXPLORER Or _
                ahtOFN_ALLOWMULTISELECT Or ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY
  
    strTable1 = "Data Table"
     
    strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
    varInputFiles = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select new files...", _
                Flags:=lngFlags)

    [B][COLOR=Blue]If lngFlags And ahtOFN_ALLOWMULTISELECT Then
        If IsArray(varInputFiles) Then[/COLOR][/B]
            For Each varCtr In varInputFiles
                If Len(Dir(varCtr)) > 0 Then
                  DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, varCtr, False
                End If
            Next varCtr
        [COLOR=Blue][B]Else
            DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, varInputFiles, False
        End If
    Else
        DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, varInputFiles, False
    End If[/B][/COLOR]
    
    MsgBox "Import Complete", vbInformation, "Complete"
End Sub

 

[DQuick]

You Sir are the best!

Thank you so much.

Try this..

Private Sub Import_Data_Click()
    Dim strFilter As String
    Dim strTable1 As String
    Dim varInputFiles As Variant, varCtr As Variant
 
    lngFlags = ahtOFN_FILEMUSTEXIST Or ahtOFN_NOCHANGEDIR Or ahtOFN_EXPLORER Or _
                ahtOFN_ALLOWMULTISELECT Or ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY
 
    strTable1 = "Data Table"
 
    strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
    varInputFiles = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select new files...", _
                Flags:=lngFlags)
 
    [B][COLOR=blue]If lngFlags And ahtOFN_ALLOWMULTISELECT Then[/COLOR][/B]
[B][COLOR=blue]       If IsArray(varInputFiles) Then[/COLOR][/B]
            For Each varCtr In varInputFiles
                If Len(Dir(varCtr)) > 0 Then
                  DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, varCtr, False
                End If
            Next varCtr
        [COLOR=blue][B]Else[/B][/COLOR]
[B][COLOR=blue]           DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, varInputFiles, False[/COLOR][/B]
[B][COLOR=blue]       End If[/COLOR][/B]
[B][COLOR=blue]   Else[/COLOR][/B]
[B][COLOR=blue]       DoCmd.TransferText acImportDelim, "Txt Import specs", strTable1, varInputFiles, False[/COLOR][/B]
[B][COLOR=blue]   End If[/COLOR][/B]
 
    MsgBox "Import Complete", vbInformation, "Complete"
End Sub

 

[pr2-eugin]

Glad its working for you ! Good luck !

 

[DQuick]

Now I need to start learing what all this means and then next time I won't need so much assistance.

 

[pr2-eugin]

Please don't be offended !

Rule 101 of Using Code from Internet - Until you are 100% comfortable of understanding of what the code does, use it without any modifications.

As days go on you will be able to figure out what part of code is doing what and then you can modify the code to suit your needs. In the link for the API:http://access.mvps.org/access/api/api0001.htm, there is a small function TestIt() where it exactly shows how to play around with the function. I just looked over that and then modified it accordingly.

 

[DQuick]

I do understand what the end result is, I just dont understand all of the bits in it and what each line means.

Thanks for the info!

Please don't be offended !

Rule 101 of Using Code from Internet - Until you are 100% comfortable of understanding of what the code does, use it without any modifications.

As days go on you will be able to figure out what part of code is doing what and then you can modify the code to suit your needs. In the link for the API:http://access.mvps.org/access/api/api0001.htm, there is a small function TestIt() where it exactly shows how to play around with the function. I just looked over that and then modified it accordingly.