Solved: Importing External Data

[BexleyManor]

Hi Folks,

I have a workbook that imports data from a text file in a network folder.

Currently the code uses:
ActiveSheet.QueryTables.Add(Connection:= followed by the path to a particular file.

My problem is the text file with the data changes each day so my code falls over as it looks for the previous day's file. I'm therefore wondering if there is any way of letting the user choose the file path during the code execution??

Hope this makes sense?? :doh:

[austenr]

Bexley,

If you are looking for a dialogue box so the user can choose the file, this should help you.

strTextFile = Application.GetOpenFilename("Text Files (*.txt, *.txt")

You will also have to declare strTextFile as a variant like this:

dim srtTextFile as Variant

Not sure if this is what you are looking for or not.

[BexleyManor]

Hi.

Yes! That's exactly the functionality I was looking for however the strtextfile information doesn't seem to get passed to the Query??

[austenr]

Can you post what you are working on?

[BexleyManor]

Certainly, here's where I'm at... I guess I probably don't need half that code !!


Sub Dig_Data()
Dim txtfile As Variant
txtfile = Application.GetOpenFilename("Text Files (*.txt, *.txt")
ActiveWorkbook.Worksheets.Add

With ActiveSheet.QueryTables.Add(Connection:="TEXT;txtfile", Destination:=Range("A1"))
.Name = txtfile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.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, 1, 1, 1)
.TextFileFixedColumnWidths = Array(11, 13, 15, 14, 15, 13, 8, 8, 26, 13, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False <<<< Here I get the error flag up??
End With
End Sub

[Norie]

You need to add the file name/path to the code.

Can you post an example of the code with a hardcoded file name/path?

[austenr]

The original text file could also be of some use. Could you post a short version (sanitized of course).

[BexleyManor]

Hi Norie, Thanks so much for your help on my last post.

Ok, you say..
You need to add the file name/path to the code.

But the file name changes every day, hence my requirement for the user to be able to pick up the file. Does this make sense?? I do ramble from time to time!! ha ha ha

[BexleyManor]

Oh, It will take a while to sanitize the txt file, it's a beast of a file!!

[Norie]

Could you please post the code with a hard-coded filename?

I realise the filename is changing, but I need to see how the filename is incorporated in the code to show you how to incorporate the changing filename.

By the way there's no real need to see the actual file, but it might help.:)

[BexleyManor]

Yeah, I see what you mean. It's been a long week and I'm frazzled !!

ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;S:\DataDump\User2\Profile1ae43\04 08 06 sl4.txt", _
Destination:=Range("A1"))

[Norie]

Try this.


Dim txtfile As Variant
txtfile = Application.GetOpenFilename("Text Files (*.txt, *.txt")
ActiveWorkbook.Worksheets.Add

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & txtfile, Destination:=Range("A1"))

[BexleyManor]

Yes Norie, spot on my friend!!

Funnily enough I came back to this about an hour ago and just sat there looking at it feeling useless. On a whim I put the & just as you'd indicated and it worked a treat!!

Thanks for your help on this one guys, it's great to have such nice folk to bounce problems off. Much appreciated.