Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force current directory
Hi All.......
I am using the following code to open a non-excel file so I can manipulate and extract the data from it I need. I would like to force Excel to look in the same directory that the file I'm using to open the non-excel file came from........rather than some directory that some default figures I should use........problem is, the file "is not found" in that default directory. Note, the actual file has no extention. Workbooks.OpenText Filename:="q261pxda.*", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Any help would be much appreciated, TIA Vaya con Dios, Chuck, CABGx3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force current directory
Hi Don......
Thanks for the response, but I can't figure out what to do with those phrases. When I just add them to my macro, they only pop-up a window telling me the path, or Cur Dir.........what I'm really looking for is a line of code to force Excel to goto the Current Directory to find the file I'm telling it to open, whatever that Current Directory may be, and overriding the "Default file Location:" setting in Excel's options........I dunno how clear that is, it reads funny.......it may not even be possible. Chuck "CLR" wrote: Hi All....... I am using the following code to open a non-excel file so I can manipulate and extract the data from it I need. I would like to force Excel to look in the same directory that the file I'm using to open the non-excel file came from........rather than some directory that some default figures I should use........problem is, the file "is not found" in that default directory. Note, the actual file has no extention. Workbooks.OpenText Filename:="q261pxda.*", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Any help would be much appreciated, TIA Vaya con Dios, Chuck, CABGx3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force current directory
sPath = CurDir
if Right(sPath,1) < "\" then sPath = sPath & "\" End if Workbooks.OpenText Filename:=sPath & "q261pxda.*", -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Don...... Thanks for the response, but I can't figure out what to do with those phrases. When I just add them to my macro, they only pop-up a window telling me the path, or Cur Dir.........what I'm really looking for is a line of code to force Excel to goto the Current Directory to find the file I'm telling it to open, whatever that Current Directory may be, and overriding the "Default file Location:" setting in Excel's options........I dunno how clear that is, it reads funny.......it may not even be possible. Chuck "CLR" wrote: Hi All....... I am using the following code to open a non-excel file so I can manipulate and extract the data from it I need. I would like to force Excel to look in the same directory that the file I'm using to open the non-excel file came from........rather than some directory that some default figures I should use........problem is, the file "is not found" in that default directory. Note, the actual file has no extention. Workbooks.OpenText Filename:="q261pxda.*", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Any help would be much appreciated, TIA Vaya con Dios, Chuck, CABGx3 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force current directory
Hi Tom.......
Thanks for the response but I still can't get it working.....here's where I put your code, but it still keeps looking to the default directory as set in Tools Options......what did I do wrong? 'Open the new q261 file from IS 'this first part intends to set up the Current Directory as default for the Open spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If Workbooks.OpenText Filename:=spath & "q261pxda.", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) thanks again, Chuck "Tom Ogilvy" wrote: sPath = CurDir if Right(sPath,1) < "\" then sPath = sPath & "\" End if Workbooks.OpenText Filename:=sPath & "q261pxda.*", -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Don...... Thanks for the response, but I can't figure out what to do with those phrases. When I just add them to my macro, they only pop-up a window telling me the path, or Cur Dir.........what I'm really looking for is a line of code to force Excel to goto the Current Directory to find the file I'm telling it to open, whatever that Current Directory may be, and overriding the "Default file Location:" setting in Excel's options........I dunno how clear that is, it reads funny.......it may not even be possible. Chuck "CLR" wrote: Hi All....... I am using the following code to open a non-excel file so I can manipulate and extract the data from it I need. I would like to force Excel to look in the same directory that the file I'm using to open the non-excel file came from........rather than some directory that some default figures I should use........problem is, the file "is not found" in that default directory. Note, the actual file has no extention. Workbooks.OpenText Filename:="q261pxda.*", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Any help would be much appreciated, TIA Vaya con Dios, Chuck, CABGx3 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force current directory
That is the way it would be set up.
If it is not going to the correct directory then the correct directory must not be the current directory. You need to query Curdir to see what it is returning. spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If ans = msgbox("Is this correct" & vbNewLine & _ sPath & "q261pxda." , vbYesNo) if ans = vbNo then exit sub Workbooks.OpenText Filename:=spath & "q261pxda.", _ If Curdir is not the correct string, then what property will give you the correct string? If you know the correct path then (assume it is "C:\Myfolder\MyFiles\" ) then you could do sPath = "C:\Myfolder\MyFiles\" Workbooks.OpenText Filename:=spath & "q261pxda.", _ -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Tom....... Thanks for the response but I still can't get it working.....here's where I put your code, but it still keeps looking to the default directory as set in Tools Options......what did I do wrong? 'Open the new q261 file from IS 'this first part intends to set up the Current Directory as default for the Open spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If Workbooks.OpenText Filename:=spath & "q261pxda.", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) thanks again, Chuck "Tom Ogilvy" wrote: sPath = CurDir if Right(sPath,1) < "\" then sPath = sPath & "\" End if Workbooks.OpenText Filename:=sPath & "q261pxda.*", -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Don...... Thanks for the response, but I can't figure out what to do with those phrases. When I just add them to my macro, they only pop-up a window telling me the path, or Cur Dir.........what I'm really looking for is a line of code to force Excel to goto the Current Directory to find the file I'm telling it to open, whatever that Current Directory may be, and overriding the "Default file Location:" setting in Excel's options........I dunno how clear that is, it reads funny.......it may not even be possible. Chuck "CLR" wrote: Hi All....... I am using the following code to open a non-excel file so I can manipulate and extract the data from it I need. I would like to force Excel to look in the same directory that the file I'm using to open the non-excel file came from........rather than some directory that some default figures I should use........problem is, the file "is not found" in that default directory. Note, the actual file has no extention. Workbooks.OpenText Filename:="q261pxda.*", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Any help would be much appreciated, TIA Vaya con Dios, Chuck, CABGx3 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force current directory
Hi Tom......
My Excel is set through Tools Options to have the "Default File Loction:" as C\My Documents. If I start my MasterFile by going into WindowsExplorer and double-clicking on my MasterFile name, then when I run the macro, it fails to find the q261pxda.* file because it looks in the c:\My Documents directory. If I start Excel and then go open my MasterFile, then the macro works and it finds the q261pxda.* file in the same directory that I opened my MasterFile from.........that is why I wanted the VBA to determine where my MasterFile opens from and look there as the first directory to find q261pxda.*, because others will use the MasterFile and I will not know how their Excel's are set........I don't want to hard-code the location in, because it will change on their machines, but will always be the same directory that the MasterFile is in.........hope that makes sense. Chuck "Tom Ogilvy" wrote: That is the way it would be set up. If it is not going to the correct directory then the correct directory must not be the current directory. You need to query Curdir to see what it is returning. spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If ans = msgbox("Is this correct" & vbNewLine & _ sPath & "q261pxda." , vbYesNo) if ans = vbNo then exit sub Workbooks.OpenText Filename:=spath & "q261pxda.", _ If Curdir is not the correct string, then what property will give you the correct string? If you know the correct path then (assume it is "C:\Myfolder\MyFiles\" ) then you could do sPath = "C:\Myfolder\MyFiles\" Workbooks.OpenText Filename:=spath & "q261pxda.", _ -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Tom....... Thanks for the response but I still can't get it working.....here's where I put your code, but it still keeps looking to the default directory as set in Tools Options......what did I do wrong? 'Open the new q261 file from IS 'this first part intends to set up the Current Directory as default for the Open spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If Workbooks.OpenText Filename:=spath & "q261pxda.", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) thanks again, Chuck "Tom Ogilvy" wrote: sPath = CurDir if Right(sPath,1) < "\" then sPath = sPath & "\" End if Workbooks.OpenText Filename:=sPath & "q261pxda.*", -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Don...... Thanks for the response, but I can't figure out what to do with those phrases. When I just add them to my macro, they only pop-up a window telling me the path, or Cur Dir.........what I'm really looking for is a line of code to force Excel to goto the Current Directory to find the file I'm telling it to open, whatever that Current Directory may be, and overriding the "Default file Location:" setting in Excel's options........I dunno how clear that is, it reads funny.......it may not even be possible. Chuck "CLR" wrote: Hi All....... I am using the following code to open a non-excel file so I can manipulate and extract the data from it I need. I would like to force Excel to look in the same directory that the file I'm using to open the non-excel file came from........rather than some directory that some default figures I should use........problem is, the file "is not found" in that default directory. Note, the actual file has no extention. Workbooks.OpenText Filename:="q261pxda.*", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Any help would be much appreciated, TIA Vaya con Dios, Chuck, CABGx3 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force current directory
Assuming the code is in the Masterfile:
spath = ThisWorkbook.Path If Right(spath, 1) < "\" Then spath = spath & "\" End If Workbooks.OpenText Filename:=spath & "q261pxda.", _ -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Tom...... My Excel is set through Tools Options to have the "Default File Loction:" as C\My Documents. If I start my MasterFile by going into WindowsExplorer and double-clicking on my MasterFile name, then when I run the macro, it fails to find the q261pxda.* file because it looks in the c:\My Documents directory. If I start Excel and then go open my MasterFile, then the macro works and it finds the q261pxda.* file in the same directory that I opened my MasterFile from.........that is why I wanted the VBA to determine where my MasterFile opens from and look there as the first directory to find q261pxda.*, because others will use the MasterFile and I will not know how their Excel's are set........I don't want to hard-code the location in, because it will change on their machines, but will always be the same directory that the MasterFile is in.........hope that makes sense. Chuck "Tom Ogilvy" wrote: That is the way it would be set up. If it is not going to the correct directory then the correct directory must not be the current directory. You need to query Curdir to see what it is returning. spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If ans = msgbox("Is this correct" & vbNewLine & _ sPath & "q261pxda." , vbYesNo) if ans = vbNo then exit sub Workbooks.OpenText Filename:=spath & "q261pxda.", _ If Curdir is not the correct string, then what property will give you the correct string? If you know the correct path then (assume it is "C:\Myfolder\MyFiles\" ) then you could do sPath = "C:\Myfolder\MyFiles\" Workbooks.OpenText Filename:=spath & "q261pxda.", _ -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Tom....... Thanks for the response but I still can't get it working.....here's where I put your code, but it still keeps looking to the default directory as set in Tools Options......what did I do wrong? 'Open the new q261 file from IS 'this first part intends to set up the Current Directory as default for the Open spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If Workbooks.OpenText Filename:=spath & "q261pxda.", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) thanks again, Chuck "Tom Ogilvy" wrote: sPath = CurDir if Right(sPath,1) < "\" then sPath = sPath & "\" End if Workbooks.OpenText Filename:=sPath & "q261pxda.*", -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Don...... Thanks for the response, but I can't figure out what to do with those phrases. When I just add them to my macro, they only pop-up a window telling me the path, or Cur Dir.........what I'm really looking for is a line of code to force Excel to goto the Current Directory to find the file I'm telling it to open, whatever that Current Directory may be, and overriding the "Default file Location:" setting in Excel's options........I dunno how clear that is, it reads funny.......it may not even be possible. Chuck "CLR" wrote: Hi All....... I am using the following code to open a non-excel file so I can manipulate and extract the data from it I need. I would like to force Excel to look in the same directory that the file I'm using to open the non-excel file came from........rather than some directory that some default figures I should use........problem is, the file "is not found" in that default directory. Note, the actual file has no extention. Workbooks.OpenText Filename:="q261pxda.*", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Any help would be much appreciated, TIA Vaya con Dios, Chuck, CABGx3 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to force current directory
How unbelieveably simple .....(when one knows how)...........
Thank you very very much Tom, your solution to my problem works perfectly. I appreciate your time and patience. Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote in message ... Assuming the code is in the Masterfile: spath = ThisWorkbook.Path If Right(spath, 1) < "\" Then spath = spath & "\" End If Workbooks.OpenText Filename:=spath & "q261pxda.", _ -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Tom...... My Excel is set through Tools Options to have the "Default File Loction:" as C\My Documents. If I start my MasterFile by going into WindowsExplorer and double-clicking on my MasterFile name, then when I run the macro, it fails to find the q261pxda.* file because it looks in the c:\My Documents directory. If I start Excel and then go open my MasterFile, then the macro works and it finds the q261pxda.* file in the same directory that I opened my MasterFile from.........that is why I wanted the VBA to determine where my MasterFile opens from and look there as the first directory to find q261pxda.*, because others will use the MasterFile and I will not know how their Excel's are set........I don't want to hard-code the location in, because it will change on their machines, but will always be the same directory that the MasterFile is in.........hope that makes sense. Chuck "Tom Ogilvy" wrote: That is the way it would be set up. If it is not going to the correct directory then the correct directory must not be the current directory. You need to query Curdir to see what it is returning. spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If ans = msgbox("Is this correct" & vbNewLine & _ sPath & "q261pxda." , vbYesNo) if ans = vbNo then exit sub Workbooks.OpenText Filename:=spath & "q261pxda.", _ If Curdir is not the correct string, then what property will give you the correct string? If you know the correct path then (assume it is "C:\Myfolder\MyFiles\" ) then you could do sPath = "C:\Myfolder\MyFiles\" Workbooks.OpenText Filename:=spath & "q261pxda.", _ -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Tom....... Thanks for the response but I still can't get it working.....here's where I put your code, but it still keeps looking to the default directory as set in Tools Options......what did I do wrong? 'Open the new q261 file from IS 'this first part intends to set up the Current Directory as default for the Open spath = CurDir If Right(spath, 1) < "\" Then spath = spath & "\" End If Workbooks.OpenText Filename:=spath & "q261pxda.", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) thanks again, Chuck "Tom Ogilvy" wrote: sPath = CurDir if Right(sPath,1) < "\" then sPath = sPath & "\" End if Workbooks.OpenText Filename:=sPath & "q261pxda.*", -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi Don...... Thanks for the response, but I can't figure out what to do with those phrases. When I just add them to my macro, they only pop-up a window telling me the path, or Cur Dir.........what I'm really looking for is a line of code to force Excel to goto the Current Directory to find the file I'm telling it to open, whatever that Current Directory may be, and overriding the "Default file Location:" setting in Excel's options........I dunno how clear that is, it reads funny.......it may not even be possible. Chuck "CLR" wrote: Hi All....... I am using the following code to open a non-excel file so I can manipulate and extract the data from it I need. I would like to force Excel to look in the same directory that the file I'm using to open the non-excel file came from........rather than some directory that some default figures I should use........problem is, the file "is not found" in that default directory. Note, the actual file has no extention. Workbooks.OpenText Filename:="q261pxda.*", _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)) Any help would be much appreciated, TIA Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
current directory | Excel Discussion (Misc queries) | |||
Current Directory | Excel Discussion (Misc queries) | |||
How do I force an Excel macro to ask me which file and directory? | Excel Discussion (Misc queries) | |||
changing current directory to that of the current open file | Excel Programming | |||
Set The Directory to the Current WB Path | Excel Programming |