Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path and File Name
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path and File Name
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 "Ronbo" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path and File Name
-----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 "Ronbo" wrote in message ... 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 . 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Path and File Name
wrote in message
... <<snip 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File Path Too Long? Not Anymore! Check out Long Path Tool | Excel Discussion (Misc queries) | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
Excel updating from XML file - file path specific? | Excel Discussion (Misc queries) | |||
How set file open path to filepath of file opened with Explorer ? | Excel Programming |