Finding file names

Andrew · 03-07-2005, 02:06 AM

To Whom it may concern,

I need to make a macro which will open a flat file, format it, and count
some cells in a column. I have the code necessary to complete all of the
tasks, except that the file names have something that throws a "spanner" in
the works. The file names follow the same format, which is
"MyFileNameyyyymmddhhmmss", ie. the file name & date & time. I've tried a
few methods I know of to open the file using "FileName:= "MyFileName" &
Format(Date, "yyyymmdd"), but excel obviously can't find the file. What
sort of code can I use to find a file name with the left most text similar
to that above?

TIA
Andrew

Patrick Molloy · 03-07-2005, 05:06 AM

yuo show an example file name with not just the date, but also the time. You
code only adds the date.

So EITHER add & Format$(mytime,"HHMMSS")
OR use a loop to examine files in the target folder
DIR() -- aged it may be, but it works well here...

DIM sFileName as String
sFileName = DIR(MyPath & "MyFileName20050325*.xlx")
do while sFilename <>""
If SFileName = MyFile then
bFound = TRUE
Exit Do
end if

sFileNAme = DIR()

loop

if not bFound then
msgbox "Unable to find " & MyFile
end if

"Andrew" wrote:

> To Whom it may concern,
>
> I need to make a macro which will open a flat file, format it, and count
> some cells in a column. I have the code necessary to complete all of the
> tasks, except that the file names have something that throws a "spanner" in
> the works. The file names follow the same format, which is
> "MyFileNameyyyymmddhhmmss", ie. the file name & date & time. I've tried a
> few methods I know of to open the file using "FileName:= "MyFileName" &
> Format(Date, "yyyymmdd"), but excel obviously can't find the file. What
> sort of code can I use to find a file name with the left most text similar
> to that above?
>
>
> TIA
> Andrew
>
>
>

Andrew · 03-07-2005, 11:06 PM

Patrick,

Thanks for your reply. I'm sorry, but somtimes I get a little excited, & I
forgot to mention that I only want to use the date to find the file. I've
given your second suggestion a try, but I can't seem to get anywhere. Any
further help would be greatly appreciated.

Andrew

From: Patrick Molloy
Date Posted: 3/7/2005 2:49:00 AM

yuo show an example file name with not just the date, but also the time. You
code only adds the date.

So EITHER add & Format$(mytime,"HHMMSS")
OR use a loop to examine files in the target folder
DIR() -- aged it may be, but it works well here...

DIM sFileName as String
sFileName = DIR(MyPath & "MyFileName20050325*.xlx")
do while sFilename <>""
If SFileName = MyFile then
bFound = TRUE
Exit Do
end if

sFileNAme = DIR()

loop

if not bFound then
msgbox "Unable to find " & MyFile
end if

"Andrew" wrote:

Andrew wrote in message ...
To Whom it may concern,

I need to make a macro which will open a flat file, format it, and count
some cells in a column. I have the code necessary to complete all of the
tasks, except that the file names have something that throws a "spanner" in
the works. The file names follow the same format, which is
"MyFileNameyyyymmddhhmmss", ie. the file name & date & time. I've tried a
few methods I know of to open the file using "FileName:= "MyFileName" &
Format(Date, "yyyymmdd"), but excel obviously can't find the file. What
sort of code can I use to find a file name with the left most text similar
to that above?

TIA
Andrew

Dave Peterson · 03-08-2005, 11:06 AM

Did you include the folder and the extension in your filename?

You may want to post the real name that you're trying to find and the code
you've tried.

Andrew wrote:
>
> Patrick,
>
> Thanks for your reply. I'm sorry, but somtimes I get a little excited, & I
> forgot to mention that I only want to use the date to find the file. I've
> given your second suggestion a try, but I can't seem to get anywhere. Any
> further help would be greatly appreciated.
>
> Andrew
>
> From: Patrick Molloy
> Date Posted: 3/7/2005 2:49:00 AM
>
> yuo show an example file name with not just the date, but also the time. You
> code only adds the date.
>
> So EITHER add & Format$(mytime,"HHMMSS")
> OR use a loop to examine files in the target folder
> DIR() -- aged it may be, but it works well here...
>
> DIM sFileName as String
> sFileName = DIR(MyPath & "MyFileName20050325*.xlx")
> do while sFilename <>""
> If SFileName = MyFile then
> bFound = TRUE
> Exit Do
> end if
>
> sFileNAme = DIR()
>
> loop
>
> if not bFound then
> msgbox "Unable to find " & MyFile
> end if
>
> "Andrew" wrote:
>
> Andrew wrote in message ...
> To Whom it may concern,
>
> I need to make a macro which will open a flat file, format it, and count
> some cells in a column. I have the code necessary to complete all of the
> tasks, except that the file names have something that throws a "spanner" in
> the works. The file names follow the same format, which is
> "MyFileNameyyyymmddhhmmss", ie. the file name & date & time. I've tried a
> few methods I know of to open the file using "FileName:= "MyFileName" &
> Format(Date, "yyyymmdd"), but excel obviously can't find the file. What
> sort of code can I use to find a file name with the left most text similar
> to that above?
>
> TIA
> Andrew

--

Dave Peterson

Andrew · 03-09-2005, 02:06 AM

Dave,

I did include the folder name whilst searching for the file, but the file
does not have any extension as it is a flat file. Oh! And I am using Excel
97 at the moment too....

I have tried another piece of code, which seems to come close (I've
separated the folder & file names for testing);
Dim sFileName As String
sFileName = Dir("G:\BUSINESS\a Super Siras\" & "CardOrder20050307*")
Do While sName <> ""
Workbooks.Open FileName:=sFileName
sFileName = Dir()
Loop

I've even tried opening it as a text file
Workbooks.OpenText FileName:=sFileName (and tried Origin:=xlMSDOS)

In either case, the macro can find the file, but cannot open it. It returns
a message highlighting the file name as "CardOrder20050307151307.xls". I've
used some different types of code to open flat files (no extension) before,
and never had this problem. But in the past, the name has been known in one
way or another. In this case, the name is only partially known.

I hope you can help me Dave. If not, the only other thing I can think of is
to get the original file named with only the date. Then I can use the macro
to copy the file, and name the new file with the date & time, open the first
file, gather the info, and then delete it. I'll then be able to follow my
normal process with the new file, and should I have to create a new file for
that day, I'll still be able to use the macro to action it as well.

TIA

Andrew

Dave Peterson wrote in message <[email protected]>...
Did you include the folder and the extension in your filename?

You may want to post the real name that you're trying to find and the code
you've tried.

Andrew wrote:
>
> Patrick,
>
> Thanks for your reply. I'm sorry, but somtimes I get a little excited, &
I
> forgot to mention that I only want to use the date to find the file. I've
> given your second suggestion a try, but I can't seem to get anywhere. Any
> further help would be greatly appreciated.
>
> Andrew
>
> From: Patrick Molloy
> Date Posted: 3/7/2005 2:49:00 AM
>
> yuo show an example file name with not just the date, but also the time.
You
> code only adds the date.
>
> So EITHER add & Format$(mytime,"HHMMSS")
> OR use a loop to examine files in the target folder
> DIR() -- aged it may be, but it works well here...
>
> DIM sFileName as String
> sFileName = DIR(MyPath & "MyFileName20050325*.xlx")
> do while sFilename <>""
> If SFileName = MyFile then
> bFound = TRUE
> Exit Do
> end if
>
> sFileNAme = DIR()
>
> loop
>
> if not bFound then
> msgbox "Unable to find " & MyFile
> end if
>
> "Andrew" wrote:
>
> Andrew wrote in message ...
> To Whom it may concern,
>
> I need to make a macro which will open a flat file, format it, and count
> some cells in a column. I have the code necessary to complete all of the
> tasks, except that the file names have something that throws a "spanner"
in
> the works. The file names follow the same format, which is
> "MyFileNameyyyymmddhhmmss", ie. the file name & date & time. I've tried a
> few methods I know of to open the file using "FileName:= "MyFileName" &
> Format(Date, "yyyymmdd"), but excel obviously can't find the file. What
> sort of code can I use to find a file name with the left most text similar
> to that above?
>
> TIA
> Andrew

--

Dave Peterson

Dave Peterson · 03-09-2005, 11:06 AM

If there's a file with that the same base name, but has an extension of .xls,
excel will open it.

I don't know if there's a workaround for this--except to either add a unique
extension or make sure there's no workbook sharing that file.

Andrew wrote:
>
> Dave,
>
> I did include the folder name whilst searching for the file, but the file
> does not have any extension as it is a flat file. Oh! And I am using Excel
> 97 at the moment too....
>
> I have tried another piece of code, which seems to come close (I've
> separated the folder & file names for testing);
> Dim sFileName As String
> sFileName = Dir("G:\BUSINESS\a Super Siras\" & "CardOrder20050307*")
> Do While sName <> ""
> Workbooks.Open FileName:=sFileName
> sFileName = Dir()
> Loop
>
> I've even tried opening it as a text file
> Workbooks.OpenText FileName:=sFileName (and tried Origin:=xlMSDOS)
>
> In either case, the macro can find the file, but cannot open it. It returns
> a message highlighting the file name as "CardOrder20050307151307.xls". I've
> used some different types of code to open flat files (no extension) before,
> and never had this problem. But in the past, the name has been known in one
> way or another. In this case, the name is only partially known.
>
> I hope you can help me Dave. If not, the only other thing I can think of is
> to get the original file named with only the date. Then I can use the macro
> to copy the file, and name the new file with the date & time, open the first
> file, gather the info, and then delete it. I'll then be able to follow my
> normal process with the new file, and should I have to create a new file for
> that day, I'll still be able to use the macro to action it as well.
>
> TIA
>
> Andrew
>
> Dave Peterson wrote in message <[email protected]>...
> Did you include the folder and the extension in your filename?
>
> You may want to post the real name that you're trying to find and the code
> you've tried.
>
> Andrew wrote:
> >
> > Patrick,
> >
> > Thanks for your reply. I'm sorry, but somtimes I get a little excited, &
> I
> > forgot to mention that I only want to use the date to find the file. I've
> > given your second suggestion a try, but I can't seem to get anywhere. Any
> > further help would be greatly appreciated.
> >
> > Andrew
> >
> > From: Patrick Molloy
> > Date Posted: 3/7/2005 2:49:00 AM
> >
> > yuo show an example file name with not just the date, but also the time.
> You
> > code only adds the date.
> >
> > So EITHER add & Format$(mytime,"HHMMSS")
> > OR use a loop to examine files in the target folder
> > DIR() -- aged it may be, but it works well here...
> >
> > DIM sFileName as String
> > sFileName = DIR(MyPath & "MyFileName20050325*.xlx")
> > do while sFilename <>""
> > If SFileName = MyFile then
> > bFound = TRUE
> > Exit Do
> > end if
> >
> > sFileNAme = DIR()
> >
> > loop
> >
> > if not bFound then
> > msgbox "Unable to find " & MyFile
> > end if
> >
> > "Andrew" wrote:
> >
> > Andrew wrote in message ...
> > To Whom it may concern,
> >
> > I need to make a macro which will open a flat file, format it, and count
> > some cells in a column. I have the code necessary to complete all of the
> > tasks, except that the file names have something that throws a "spanner"
> in
> > the works. The file names follow the same format, which is
> > "MyFileNameyyyymmddhhmmss", ie. the file name & date & time. I've tried a
> > few methods I know of to open the file using "FileName:= "MyFileName" &
> > Format(Date, "yyyymmdd"), but excel obviously can't find the file. What
> > sort of code can I use to find a file name with the left most text similar
> > to that above?
> >
> > TIA
> > Andrew
>
> --
>
> Dave Peterson

--

Dave Peterson

Andrew · 03-10-2005, 12:06 AM

Dave,

This is a problem. Excel seems to find the file I've created, but is having
problems opening it. I can't give the file any extension though, because
after I've processed the file with Excel, I still have other processes to
carry out on it, and the extension effects the final outcome.

I have found some code that can be used to copy & rename files, so I think
that I'll tackle it from that direction.

Thanks for your help anyway. :-)
Andrew

Dave Peterson wrote in message <[email protected]>...

If there's a file with that the same base name, but has an extension of
..xls,
excel will open it.

I don't know if there's a workaround for this--except to either add a unique
extension or make sure there's no workbook sharing that file.

Andrew wrote:
>
> Dave,
>
> I did include the folder name whilst searching for the file, but the file
> does not have any extension as it is a flat file. Oh! And I am using
Excel
> 97 at the moment too....
>
> I have tried another piece of code, which seems to come close (I've
> separated the folder & file names for testing);
> Dim sFileName As String
> sFileName = Dir("G:\BUSINESS\a Super Siras\" &
"CardOrder20050307*")
> Do While sName <> ""
> Workbooks.Open FileName:=sFileName
> sFileName = Dir()
> Loop
>
> I've even tried opening it as a text file
> Workbooks.OpenText FileName:=sFileName (and tried Origin:=xlMSDOS)
>
> In either case, the macro can find the file, but cannot open it. It
returns
> a message highlighting the file name as "CardOrder20050307151307.xls".
I've
> used some different types of code to open flat files (no extension)
before,
> and never had this problem. But in the past, the name has been known in
one
> way or another. In this case, the name is only partially known.
>
> I hope you can help me Dave. If not, the only other thing I can think of
is
> to get the original file named with only the date. Then I can use the
macro
> to copy the file, and name the new file with the date & time, open the
first
> file, gather the info, and then delete it. I'll then be able to follow my
> normal process with the new file, and should I have to create a new file
for
> that day, I'll still be able to use the macro to action it as well.
>
> TIA
>
> Andrew
>
> Dave Peterson wrote in message <[email protected]>...
> Did you include the folder and the extension in your filename?
>
> You may want to post the real name that you're trying to find and the code
> you've tried.
>
> Andrew wrote:
> >
> > Patrick,
> >
> > Thanks for your reply. I'm sorry, but somtimes I get a little excited,
&
> I
> > forgot to mention that I only want to use the date to find the file.
I've
> > given your second suggestion a try, but I can't seem to get anywhere.
Any
> > further help would be greatly appreciated.
> >
> > Andrew
> >
> > From: Patrick Molloy
> > Date Posted: 3/7/2005 2:49:00 AM
> >
> > yuo show an example file name with not just the date, but also the time.
> You
> > code only adds the date.
> >
> > So EITHER add & Format$(mytime,"HHMMSS")
> > OR use a loop to examine files in the target folder
> > DIR() -- aged it may be, but it works well here...
> >
> > DIM sFileName as String
> > sFileName = DIR(MyPath & "MyFileName20050325*.xlx")
> > do while sFilename <>""
> > If SFileName = MyFile then
> > bFound = TRUE
> > Exit Do
> > end if
> >
> > sFileNAme = DIR()
> >
> > loop
> >
> > if not bFound then
> > msgbox "Unable to find " & MyFile
> > end if
> >
> > "Andrew" wrote:
> >
> > Andrew wrote in message ...
> > To Whom it may concern,
> >
> > I need to make a macro which will open a flat file, format it, and count
> > some cells in a column. I have the code necessary to complete all of
the
> > tasks, except that the file names have something that throws a "spanner"
> in
> > the works. The file names follow the same format, which is
> > "MyFileNameyyyymmddhhmmss", ie. the file name & date & time. I've tried
a
> > few methods I know of to open the file using "FileName:= "MyFileName" &
> > Format(Date, "yyyymmdd"), but excel obviously can't find the file. What
> > sort of code can I use to find a file name with the left most text
similar
> > to that above?
> >
> > TIA
> > Andrew
>
> --
>
> Dave Peterson

--

Dave Peterson

Dave Peterson · 03-10-2005, 02:06 PM

Yeah, it's not the Dir() that's getting confused--it's the workbooks.open()
that's confused.

Andrew wrote:
>
> Dave,
>
> This is a problem. Excel seems to find the file I've created, but is having
> problems opening it. I can't give the file any extension though, because
> after I've processed the file with Excel, I still have other processes to
> carry out on it, and the extension effects the final outcome.
>
> I have found some code that can be used to copy & rename files, so I think
> that I'll tackle it from that direction.
>
> Thanks for your help anyway. :-)
> Andrew
>
> Dave Peterson wrote in message <[email protected]>...
>
> If there's a file with that the same base name, but has an extension of
> .xls,
> excel will open it.
>
> I don't know if there's a workaround for this--except to either add a unique
> extension or make sure there's no workbook sharing that file.
>
> Andrew wrote:
> >
> > Dave,
> >
> > I did include the folder name whilst searching for the file, but the file
> > does not have any extension as it is a flat file. Oh! And I am using
> Excel
> > 97 at the moment too....
> >
> > I have tried another piece of code, which seems to come close (I've
> > separated the folder & file names for testing);
> > Dim sFileName As String
> > sFileName = Dir("G:\BUSINESS\a Super Siras\" &
> "CardOrder20050307*")
> > Do While sName <> ""
> > Workbooks.Open FileName:=sFileName
> > sFileName = Dir()
> > Loop
> >
> > I've even tried opening it as a text file
> > Workbooks.OpenText FileName:=sFileName (and tried Origin:=xlMSDOS)
> >
> > In either case, the macro can find the file, but cannot open it. It
> returns
> > a message highlighting the file name as "CardOrder20050307151307.xls".
> I've
> > used some different types of code to open flat files (no extension)
> before,
> > and never had this problem. But in the past, the name has been known in
> one
> > way or another. In this case, the name is only partially known.
> >
> > I hope you can help me Dave. If not, the only other thing I can think of
> is
> > to get the original file named with only the date. Then I can use the
> macro
> > to copy the file, and name the new file with the date & time, open the
> first
> > file, gather the info, and then delete it. I'll then be able to follow my
> > normal process with the new file, and should I have to create a new file
> for
> > that day, I'll still be able to use the macro to action it as well.
> >
> > TIA
> >
> > Andrew
> >
> > Dave Peterson wrote in message <[email protected]>...
> > Did you include the folder and the extension in your filename?
> >
> > You may want to post the real name that you're trying to find and the code
> > you've tried.
> >
> > Andrew wrote:
> > >
> > > Patrick,
> > >
> > > Thanks for your reply. I'm sorry, but somtimes I get a little excited,
> &
> > I
> > > forgot to mention that I only want to use the date to find the file.
> I've
> > > given your second suggestion a try, but I can't seem to get anywhere.
> Any
> > > further help would be greatly appreciated.
> > >
> > > Andrew
> > >
> > > From: Patrick Molloy
> > > Date Posted: 3/7/2005 2:49:00 AM
> > >
> > > yuo show an example file name with not just the date, but also the time.
> > You
> > > code only adds the date.
> > >
> > > So EITHER add & Format$(mytime,"HHMMSS")
> > > OR use a loop to examine files in the target folder
> > > DIR() -- aged it may be, but it works well here...
> > >
> > > DIM sFileName as String
> > > sFileName = DIR(MyPath & "MyFileName20050325*.xlx")
> > > do while sFilename <>""
> > > If SFileName = MyFile then
> > > bFound = TRUE
> > > Exit Do
> > > end if
> > >
> > > sFileNAme = DIR()
> > >
> > > loop
> > >
> > > if not bFound then
> > > msgbox "Unable to find " & MyFile
> > > end if
> > >
> > > "Andrew" wrote:
> > >
> > > Andrew wrote in message ...
> > > To Whom it may concern,
> > >
> > > I need to make a macro which will open a flat file, format it, and count
> > > some cells in a column. I have the code necessary to complete all of
> the
> > > tasks, except that the file names have something that throws a "spanner"
> > in
> > > the works. The file names follow the same format, which is
> > > "MyFileNameyyyymmddhhmmss", ie. the file name & date & time. I've tried
> a
> > > few methods I know of to open the file using "FileName:= "MyFileName" &
> > > Format(Date, "yyyymmdd"), but excel obviously can't find the file. What
> > > sort of code can I use to find a file name with the left most text
> similar
> > > to that above?
> > >
> > > TIA
> > > Andrew
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson

--

Dave Peterson