Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open / .Open Text - How do you stop the .xls addition?
Hi everyone.
I'm trying to make a function that automatically reads in our archived data, when requested by the user. The archives' filenames are in the format "aba.csv.2003_07_29," where 2003_07_29 is the date of the archive. Although I think this in an awful naming convention, I have no choice on this matter, and must find a way to deal with it. I have tried opening these files with: Workbooks.OpenText Filename:="aba.csv." & reyear & "_" & remonth & "_" & reday, Comma:=True and with the Workbooks.Open method, but both INSIST on adding the ".xls" to the end. This gives me "aba.csv.2003_07_29.xls" which does not return the correct file (obviously). Can I force Excel to not add the .xls extension on, and just use the filename as I have coded it? This is really annoying (I think), I'm really not a fan of functions/methods that just make assumptions like this...Any help would be MOST appreciated, as I've been working on this one problem for days...I've actually finished the whole function, which seems to work, but can't test because the simplest part of the whole thing keeps not working...I really appreciate any help you guys can provide, you've been a ton of help in the past. Thanks! Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open / .Open Text - How do you stop the .xls addition?
Hi Rob
Thanks very much for your response. Strangely enough, it seems to work when I change to the directory in advance, rather than including the full path in the open statement. I guess I should have tried this before posting. Nevertheless, I am most appreciative of your assistance with this! David -----Original Message----- Hi Dave, I've tried the following in all current versions of Excel where the file named "TestData.csv.2003_30_07" is located in the current directory, and it seems to work correctly for me. Sub OpenTextFile() Dim szYear As String Dim szMonth As String Dim szDay As String szYear = "2003" szMonth = "30" szDay = "07" Workbooks.OpenText _ Filename:="TestData.csv." & szYear & "_" & szMonth & "_" & szDay, _ Comma:=True, FieldInfo:=Array(Array(1, 1), Array (2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1)) End Sub -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Dave" wrote in message ... Hi everyone. I'm trying to make a function that automatically reads in our archived data, when requested by the user. The archives' filenames are in the format "aba.csv.2003_07_29," where 2003_07_29 is the date of the archive. Although I think this in an awful naming convention, I have no choice on this matter, and must find a way to deal with it. I have tried opening these files with: Workbooks.OpenText Filename:="aba.csv." & reyear & "_" & remonth & "_" & reday, Comma:=True and with the Workbooks.Open method, but both INSIST on adding the ".xls" to the end. This gives me "aba.csv.2003_07_29.xls" which does not return the correct file (obviously). Can I force Excel to not add the .xls extension on, and just use the filename as I have coded it? This is really annoying (I think), I'm really not a fan of functions/methods that just make assumptions like this...Any help would be MOST appreciated, as I've been working on this one problem for days...I've actually finished the whole function, which seems to work, but can't test because the simplest part of the whole thing keeps not working...I really appreciate any help you guys can provide, you've been a ton of help in the past. Thanks! Dave . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Open / .Open Text - How do you stop the .xls addition?
See one other response to your other post.
Dave wrote: Hi Rob Thanks very much for your response. Strangely enough, it seems to work when I change to the directory in advance, rather than including the full path in the open statement. I guess I should have tried this before posting. Nevertheless, I am most appreciative of your assistance with this! David -----Original Message----- Hi Dave, I've tried the following in all current versions of Excel where the file named "TestData.csv.2003_30_07" is located in the current directory, and it seems to work correctly for me. Sub OpenTextFile() Dim szYear As String Dim szMonth As String Dim szDay As String szYear = "2003" szMonth = "30" szDay = "07" Workbooks.OpenText _ Filename:="TestData.csv." & szYear & "_" & szMonth & "_" & szDay, _ Comma:=True, FieldInfo:=Array(Array(1, 1), Array (2, 1), _ Array(3, 1), Array(4, 1), Array(5, 1)) End Sub -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Dave" wrote in message ... Hi everyone. I'm trying to make a function that automatically reads in our archived data, when requested by the user. The archives' filenames are in the format "aba.csv.2003_07_29," where 2003_07_29 is the date of the archive. Although I think this in an awful naming convention, I have no choice on this matter, and must find a way to deal with it. I have tried opening these files with: Workbooks.OpenText Filename:="aba.csv." & reyear & "_" & remonth & "_" & reday, Comma:=True and with the Workbooks.Open method, but both INSIST on adding the ".xls" to the end. This gives me "aba.csv.2003_07_29.xls" which does not return the correct file (obviously). Can I force Excel to not add the .xls extension on, and just use the filename as I have coded it? This is really annoying (I think), I'm really not a fan of functions/methods that just make assumptions like this...Any help would be MOST appreciated, as I've been working on this one problem for days...I've actually finished the whole function, which seems to work, but can't test because the simplest part of the whole thing keeps not working...I really appreciate any help you guys can provide, you've been a ton of help in the past. Thanks! Dave . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when i open an Excell file 3 workbooks open.Why? | Excel Discussion (Misc queries) | |||
how do i stop 16 workbooks from opening when I open excel 2007? | Excel Discussion (Misc queries) | |||
When I open Excel, workbooks open automatically. How can I stop t | Excel Discussion (Misc queries) | |||
copy text between 2 open workbooks via macro | Excel Worksheet Functions | |||
How do I stop Excel from closing the open file each time I open a. | Setting up and Configuration of Excel |