Path and File Name

[Ronbo]

I am trying to create the following path and file name;

C:\Documents and Settings\My Documents\John Jul 04
Info\DATA INPUT." _

Cell B1 = Johnson & Paul


I am using:

Dim sName As String, sDate As String

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 & EOM INFO\DATA INPUT." _

But it can not find the file. What is wrong with the
code?

Thanks for any help

 

[Ed]

Couple of thoughts - don't know if any will apply -
-- Isn't "C:\Documents and Settings\My Documents" usually
"C:\Documents and Settings\USERNAME\My Documents"?
-- Your sample file folder name "John Jul 04 Info" has spaces in it.
Does "sName & sDate & EOM INFO" create those spaces"
-- Your sample file folder name "John Jul 04 Info" has "Info". Your
code "sName & sDate & EOM INFO" has "EOM INFO".

You might try creating your file path and name as a String and show it
in a message box to verify it during code creation before using the string
in With ActiveSheet.QueryTables.Add(Connection:= "TEXT; sFile")

HTH
Ed

 

[Guest]

-----Original Message-----
Couple of thoughts - don't know if any will apply -
-- Isn't "C:\Documents and Settings\My Documents" usually
"C:\Documents and Settings\USERNAME\My Documents"?
-- Your sample file folder name "John Jul 04 Info" has spaces in it.
Does "sName & sDate & EOM INFO" create those spaces"
-- Your sample file folder name "John Jul 04 Info" has "Info". Your
code "sName & sDate & EOM INFO" has "EOM INFO".

You might try creating your file path and name as a String and show it
in a message box to verify it during code creation before using the string
in With ActiveSheet.QueryTables.Add(Connection:= "TEXT; sFile")

HTH
Ed




.
1. the EOM INFO is the same

2. USERNAME is the same
2. you are correct i need to see what the code is
producing and I have tried MsgBox but I get -
C:\Documents and Settings\UserName\My Documents\sName &
sDate & EOM INFO\DATA INPUT...

How do you get the string in a MsgBox rather than the
alpha sName?

 

[Ed]

2. you are correct i need to see what the code is
producing and I have tried MsgBox but I get -
C:\Documents and Settings\UserName\My Documents\sName &
sDate & EOM INFO\DATA INPUT...

How do you get the string in a MsgBox rather than the
alpha sName?

Your posted code is:
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\My
Documents\sName & sDate & EOM INFO\DATA INPUT." _

I would do:
Dim strFile As String
strFile = "C:\Documents and Settings\My Documents\" & _
sName & sDate & _
"EOM INFO\DATA INPUT." <<(is that period a typo?)
(in the real code, do you have a .txt or .doc?)

MsgBox strFile

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" strFile, Destination:= etc. . . .

Step through using F8; if the message box comes up incorrect, stop
the code and fix the string.

HTH
Ed