Help with text file import

[dantheman9]

HI I importing some text files as part of a bigger Sub, I have all the path imfomation for each text file from other parts of the Sub (which include a textbox for the folder path and then the file name is formed earlier in the Sub).

The sub always stops at the text import, I can't work how to get it working..relevent code below;

rFname = Userform1.Fwatch & stripedt  '[COLOR=red]where userform1.Fwatch is folder 'path[/COLOR]
[COLOR=#ff0000]'stripedt is the file name with txt extention on.[/COLOR]

NewShtt.Activate

With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & rFname _  
        , Destination:=Range("$A$1"))
         .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = d
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(9, 1, 1, 9, 1, 1, 9, 9, 9)
        .TextFileTrailingMinusNumbers = True
        [COLOR=red].Refresh BackgroundQuery:=False      ' code breaks here
[/COLOR]    End With

Im sure it must be something simple in the TEXT line but can't work it out?

Thanks

Dan

 

[Andrew Poulsom]

If you:

MsgBox rFname

does it show what you expect?

 

[dantheman9]

Hi Andrew,

thanks for getting back, yes the msgbox gives the full path name as I would expect.....
could it be that for the Connection TEXT line it might have " in the worng place (from using macro recorder this line usually appear as
"TEXT;C:\blah\blah\blah.txt"

but i can't seem to edit that line correctly/ not sure if the changes are editing that line right

 

[Andrew Poulsom]

What do you get if you record a macro while adding the query manually? What does rFname contain?

 

[dantheman9]

manual recording;

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\venue\result\LwtmenA Results.txt", Destination:=Range("$A$1"))
        .Name = "LwtmenA Results"
        .FieldNames = True

rFname in my code gives C:\venue\result\LwtmenA Results.txt
I also tried adding into that String the TEXT; bit before hand, but it still didn't like it.

rFname is made from Fwatch (which is a copy from the textbox - C:\venue\result\)
and then stripedt which is formed from the following;

striped = Strip(sFileName, "Splits Data.txt")
stripedt = striped & " Results.txt"

Not sure if that should have any effect on it anyhow?

 

[Andrew Poulsom]

From what you say, this should work:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & rFname, Destination:=Range("$A$1"))

 

[dantheman9]

Andrew,

many thanks for your time, I Have cracked it!
I looked a bit more closely at the other bit to make the stripedt section and noticed I had formed a double space in the file name!!
so it now works fine!!

thanks again for your time on this
.
Dan