Help with text file import

dantheman9

Board Regular
Joined
Feb 5, 2011
Messages
175
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;

Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

dantheman9

Board Regular
Joined
Feb 5, 2011
Messages
175
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
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What do you get if you record a macro while adding the query manually? What does rFname contain?
 

dantheman9

Board Regular
Joined
Feb 5, 2011
Messages
175

ADVERTISEMENT

manual recording;

Code:
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;

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

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
From what you say, this should work:

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

dantheman9

Board Regular
Joined
Feb 5, 2011
Messages
175
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!! :oops:
so it now works fine!!

thanks again for your time on this
.
Dan
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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
Top