How to import a file where the file name is in an Excel workbook

[BoonBeats]

Hi,
I've been trying to get my head around this but have hit a brick wall.
So here's my question.

I'm writing a Macro to import a particular text file daily. Now the thing is the file name is in a cell on sheet1 cell "I8" on an excel workbook.

Can someone help me with the coding of this please.

the peice of code below is what i have so far but it isn't working.

Sheets("Data").Select
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;G:\Common\Joey\QHFSGEO_T" & Sheet1!I8 & ".TXT", Destination:=Range("$A$1"))

 

[ravishankar]

Hi
try these codes

Dim a As String
a = "TEXT;G:\Common\Joey\QHFSGEO_T\" & Sheets("Sheet1").Range("I8") & ".TXT"
With ActiveSheet.QueryTables.Add(Connection:= _
a, Destination:=Range("$A$1"))

Ravi

 

[BoonBeats]

Hey,

That worked perfectly fine! Many Thanks!

Hi
try these codes

Dim a As String
a = "TEXT;G:\Common\Joey\QHFSGEO_T\" & Sheets("Sheet1").Range("I8") & ".TXT"
With ActiveSheet.QueryTables.Add(Connection:= _
a, Destination:=Range("$A$1"))

Ravi