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

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


.

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