QueryTable

[jmvanhilten]

Hi,

I have some code to read in several textfiles in specific sheets in a workbook. The problem is weird.
-First, If the name of the textfiles is hardcoded everything works fine.
-Second, if the name of the textfiles is read out fo the excell-sheet, it sometimes works and sometimes it doesn't.

Public Sub FixedWidthImport()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ThePath As String
Dim TheName As String
Dim Stat As String
 
' read several cells for .txt file location and name
Worksheets("MacroVariables").Activate
ThePath = ActiveSheet.Range("$C$11")
TheName = ActiveSheet.Range("$C$12")
 
'call sub to read file
Stat = "Static01"
Call Static01(ThePath, TheName, Stat)
 
End Sub
 
Public Sub Static01(ThePath, TheName, Stat)
 
Dim Name1 As String
Dim Name2 As String
 
 Worksheets("Static01").Activate
 ActiveSheet.Columns("A:I").Select
 Selection.ClearContents
 
Name1 = ThePath & "\Output\" & TheName & "_" & Stat & ".out"
Name2 = TheName & "_" & Stat & ""
 
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Name1, Destination:=ActiveSheet.Range("$A$3"))
        .Name = Name2
        .PreserveFormatting = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePlatform = 437
        .TextFileStartRow = 11
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileFixedColumnWidths = Array(5, 5, 10, 10, 10, 10, 10, 10)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=True
     End With
 
    Worksheets("MacroVariables").Range("F6:R6").Copy
    ActiveSheet.Range("A1:M1").Select
    ActiveSheet.Paste
 
 End Sub

Anyone any idea?

 

[Andrew Poulsom]

Welcome to the Board.

In what way doesn't it work (sometimes)?

Why the $ signs in?

ThePath = ActiveSheet.Range("$C$11")
TheName = ActiveSheet.Range("$C$12")

Try:

ThePath = ActiveSheet.Range("C11").Value
TheName = ActiveSheet.Range("C12").Value

 

[jmvanhilten]

I get a runtime error '1004', which says excell cannot access the text file. I'm 100% sure the file exists and there are no mistypes in the name.
If I debug it jumps to .Refresh BackgroundQuery. So, the wholen errormessage doesn't say so much.

Funny thing is that the code worked 2 or 3 times, and suddenly it does not work anymore.

When I hardcode Name1 and Name2 in the code, everything works fine.