Import Data

[Ronbo]

I am trying to import data through "Get External Data"
using a macro that changes the file name according to the
month and year.

Using the following code it always shows and error
on .Refresh BackgroundQuery:=False and I have tried True
but neither work.


sName = (Left(Range("B1"), 4))
sDate = "" & Format(DateSerial(Year(Date), Month
(Date) - 1, 1), " mmm yy ")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\My
Documents\sName & sDate & INFO\DATA INPUT." _
, Destination:=Range("A1"))
.Name = "SALES."
'details
.Refresh BackgroundQuery:=False
End With
End Sub

Cell B1 = Johnson & Paul
Date = 8/31/2004

So the file name would be "John Aug 04 Info"

Path and file should be - C:\Documents and Settings\My
Documents\John Aug 04 Info

Any ideas as to what I am doing wrong??

Thanks for any help.

 

[Guest]

-----Original Message-----

I am trying to import data through "Get External Data"
using a macro that changes the file name according to the
month and year.

Using the following code it always shows and error
on .Refresh BackgroundQuery:=False and I have tried True
but neither work.


sName = (Left(Range("B1"), 4))
sDate = "" & Format(DateSerial(Year(Date), Month
(Date) - 1, 1), " mmm yy ")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\My
Documents\sName & sDate & INFO\DATA INPUT." _
, Destination:=Range("A1"))
.Name = "SALES."
'details
.Refresh BackgroundQuery:=False
End With
End Sub

Cell B1 = Johnson & Paul
Date = 8/31/2004

So the file name would be "John Aug 04 Info"

Path and file should be - C:\Documents and Settings\My
Documents\John Aug 04 Info

Any ideas as to what I am doing wrong??

Thanks for any help.

.
See "Path and File Name" question for a simplier question