Problem With Adding Multiple Text File Queries

E

ERLoft

Board Regular
Joined
Feb 24, 2006
Messages
193
I am importing data from three different text file imports. I had been doing this with manual selection of the files with no issues, but wanted to automate the process further and set up code to import the newest file in each of the target folders. However, while I can run any one of these three imports independently, when I try to run them back to back, I get a Run-Time Error 1004 - Excel cannot find the text file to refresh this external data range on the second of three imports - on the .Refresh BackgroundQuery:=False. I would say this is because no data is actually added to the workbook on the second import, but cannot figure out why the first one imports and the second one does not. The first one works fine - regardless of which order I call these subs in. Any help would be greatly appreciated. Here are the three subs:

VBA Code: 
Sub AutoImportHF()

Dim TargetFile As String
   
ChDir HFDir
    
If RunY = vbYes Then
    MyPath = HFDir
     
    MyFile = Dir(MyPath & "*.txt", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        MyFile = Dir
    Loop

    fName = LatestFile
    TargetFile = HFDir & LatestFile
Else
    fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    TargetFile = fName
End If

Sheets("HF Import").Activate
    Range("A2:Z500").ClearContents
    Range("A2").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TargetFile, Destination:=Range("$A$2"))
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    If Range("A2").Value = 1 Then MsgBox ("There's an error in the HF Import.  Please delete row 2 on the HF import and correct the formula in cell " & _
        "A2 on the Booking Pace Export page.  You'll then need to manually copy and paste the booking pace export into the report.")

End Sub

VBA Code: 
Sub AutoImportSegmentation()

Dim TargetFile As String
   
ChDir DSDir
    
If RunY = vbYes Then
    MyPath = DSDir
     
    MyFile = Dir(MyPath & "*.txt", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
     Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        MyFile = Dir
    Loop

    fName = LatestFile
    TargetFile = DSDir & LatestFile
Else
    fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    TargetFile = fName
End If

Sheets("Segment Import").Activate
    Range("A1:Z500").ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TargetFile, Destination:=Range("$A$1"))
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

VBA Code: 
Sub AutoImportDailyClosing()

Dim TargetFile As String
   
ChDir DCDir
    
If RunY = vbYes Then
    MyPath = DCDir
    MyFile = Dir(MyPath & "*.txt", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop

    fName = LatestFile
    TargetFile = DCDir & LatestFile
Else
    fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    TargetFile = fName
End If

Sheets("Daily Closing Import").Activate
    Range("A1:Z500").ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TargetFile, Destination:=Range("$A$1"))
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
On further testing, all three imports work fine back to back to back if the state of RunY = vbNo, in which case the user is prompted to manually select the import file. I've verified that the code to select the most recent file in the folder targets the exact same file as would be selected manually though. Either way, the value of TargetFile is identical. Which makes even less sense why it would error out when the file is auto selected but not manually selected...
 
Upvote 0
Never did hear any info on why this didn't work, but in case another member searches for something like this, I came up with a completely different approach to import the data. Rather than use a connection or query for the data, I changed to opening and parsing the text tile directly into Excel first, then copying and pasting the data into the target worksheet before closing the text file. No issues with running this back to back to back. Bear in mind for any referencing this that there's a lot of Public variables in play here that aren't defined in the code shown.

VBA Code: 
Sub AutoImportHF()

Dim TargetFile As String
Dim fnamewb As Workbook
   
ChDir HFDir
    
If RunY = vbYes Then
    MyPath = HFDir
     
    MyFile = Dir(MyPath & "*.txt", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop
    TargetFile = HFDir & LatestFile
Else
    TargetFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
End If


Workbooks.OpenText Filename:= _
    TargetFile _
    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
    , 1), Array(16, 1)), TrailingMinusNumbers:=True
        
Cells.Select
Cells.Copy
Set fnamewb = ActiveWorkbook

Workbooks(MultiToolName).Activate
Sheets("HF Import").Activate
Range("A1").PasteSpecial
Application.CutCopyMode = False

fnamewb.Close SaveChanges:=False

If Range("A1").Value = 1 Then MsgBox ("There's an error in the HF Import.  Please delete row 2 on the HF import and correct the formula in cell " & _
    "A2 on the Booking Pace Export page.  You'll then need to manually copy and paste the booking pace export into the report.")

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Copying file path from a cell?
Replies
2
Views
225
Dave_george
D
E
  • Question
Open vba in current sheet
Replies
2
Views
300
Excelexcel86
E
K
  • Question
How to get all .XML files from a specific folder and attached in email and send it in VBA for a specific date (today for example)
Replies
2
Views
428
Fluff
Fluff
J
  • Solved
Check Filename Before Opening
Replies
5
Views
517
JLouis
J
H
  • Solved
Macro to convert Files into CSV Files-limit selection when browsing
Replies
1
Views
234
howard
H

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top