Hi,
I have a workbook that opens a file, imports data and closes it down again.
The problem I have or will have:
The file that holds the data will change the name by having a date added, so
this will now mean I will have to manualy open the file and minus off the
date so that I can import.
Is there a way the macro can ignore the date so that it would still open the
file and import the data?
Again - your support is greatly appreciated
John

You could do a Dir before opening to get the full file name

sFilename = Dir("C:\Test\My Import File*.xls"

note that this will return the first found if there are many.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
Hi,
I have a workbook that opens a file, imports data and closes it down
again.
The problem I have or will have:
The file that holds the data will change the name by having a date added,
so
this will now mean I will have to manualy open the file and minus off the
date so that I can import.
Is there a way the macro can ignore the date so that it would still open
the
file and import the data?
Again - your support is greatly appreciated
John

Hi,

Dir doesn't return the full path, so you have to use something like
this:

sPath = "C:\test"
sFilename = "My Import File*.xls"
if len(dir(spath & "\" & sfilename))=0 then
msgbox "No file"
else
sFilename = spath & "\" & dir(spath & "\" & sfilename)
end if

Regards,
Ivan

Hi Ivan,
Thanks for coming in as well.
I have tried your code, but what extra piece of code do I need to actualy
open the file once found?
John

"Ivan Raiminius" wrote:

Hi,

Dir doesn't return the full path, so you have to use something like
this:

sPath = "C:\test"
sFilename = "My Import File*.xls"
if len(dir(spath & "\" & sfilename))=0 then
msgbox "No file"
else
sFilename = spath & "\" & dir(spath & "\" & sfilename)
end if

Regards,
Ivan

Hi John,

sPath = "C:\test"
sFilename = "My Import File*.xls"
if len(dir(spath & "\" & sfilename))=0 then
msgbox "No file"
else
sFilename = spath & "\" & dir(spath & "\" & sfilename)

dim wb as workbook
set wb = workbooks.open sfilename

end if

You can move "dim wb as workbook" into top of the procedure, if you
prefer this location or to the top of the module to be able to work
with wb in other procedures within the module.

Regards,
Ivan

Ivan,
This what I have so far:

Dim wb As Workbook
spath = Application.DefaultFilePath
sfilename = "Competitor Pricing Monitor Update.*.xls"
If Len(Dir(spath & "\" & sfilename)) = 0 Then
MsgBox "No file"
Else
sfilename = spath & "\" & Dir(spath & "\" & sfilename)

'set wb = workbooks.open sfilename

End If

The 'set wb = workbooks.open sfilename' has turned red

Not much good at this am I?

What am I missing?

"Ivan Raiminius" wrote:

Hi John,

sPath = "C:\test"
sFilename = "My Import File*.xls"
if len(dir(spath & "\" & sfilename))=0 then
msgbox "No file"
else
sFilename = spath & "\" & dir(spath & "\" & sfilename)

dim wb as workbook
set wb = workbooks.open sfilename

end if

You can move "dim wb as workbook" into top of the procedure, if you
prefer this location or to the top of the module to be able to work
with wb in other procedures within the module.

Regards,
Ivan

mmm,
Thanks Bob
I dont even know how to apply it. I tried putting it into the code and
turned red??
How about if I had the file already open, for example:
Windows("Price Update 22-May-06.xls").Activate
But the code would ignore the 22-May-06?
John

"Bob Phillips" wrote:

You could do a Dir before opening to get the full file name

sFilename = Dir("C:\Test\My Import File*.xls"

note that this will return the first found if there are many.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
Hi,
I have a workbook that opens a file, imports data and closes it down
again.
The problem I have or will have:
The file that holds the data will change the name by having a date added,
so
this will now mean I will have to manualy open the file and minus off the
date so that I can import.
Is there a way the macro can ignore the date so that it would still open
the
file and import the data?
Again - your support is greatly appreciated
John

John,

Assuming that you have that directory active, all you need is something like

sFile = Dir("Price Update*,xls")
If sFile < "" Then
Workbooks.Open(sFile)
End If

If the directory is not active, you need something like

Dim sPath As String
Dim sFile As String

sPath = "C:\MyTest\"
sFile = Dir(sPath & "Price Update*.xls")
If sFile < "" Then
Workbooks.Open (sFile)
End If


If you have it already open, why do you need to open it?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
mmm,
Thanks Bob
I dont even know how to apply it. I tried putting it into the code and
turned red??
How about if I had the file already open, for example:
Windows("Price Update 22-May-06.xls").Activate
But the code would ignore the 22-May-06?
John

"Bob Phillips" wrote:

You could do a Dir before opening to get the full file name

sFilename = Dir("C:\Test\My Import File*.xls"

note that this will return the first found if there are many.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
Hi,
I have a workbook that opens a file, imports data and closes it down
again.
The problem I have or will have:
The file that holds the data will change the name by having a date
added,
so
this will now mean I will have to manualy open the file and minus off
the
date so that I can import.
Is there a way the macro can ignore the date so that it would still
open
the
file and import the data?
Again - your support is greatly appreciated
John

Many thanks Bob your code worked a treat.
I thought as a last resort I would go for the file already open option. But
then having said that I am having trouble with the already open file being
recognised by the code. - See my reply to Ivan

John
"Bob Phillips" wrote:

John,

Assuming that you have that directory active, all you need is something like

sFile = Dir("Price Update*,xls")
If sFile < "" Then
Workbooks.Open(sFile)
End If

If the directory is not active, you need something like

Dim sPath As String
Dim sFile As String

sPath = "C:\MyTest\"
sFile = Dir(sPath & "Price Update*.xls")
If sFile < "" Then
Workbooks.Open (sFile)
End If


If you have it already open, why do you need to open it?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
mmm,
Thanks Bob
I dont even know how to apply it. I tried putting it into the code and
turned red??
How about if I had the file already open, for example:
Windows("Price Update 22-May-06.xls").Activate
But the code would ignore the 22-May-06?
John

"Bob Phillips" wrote:

You could do a Dir before opening to get the full file name

sFilename = Dir("C:\Test\My Import File*.xls"

note that this will return the first found if there are many.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
Hi,
I have a workbook that opens a file, imports data and closes it down
again.
The problem I have or will have:
The file that holds the data will change the name by having a date
added,
so
this will now mean I will have to manualy open the file and minus off
the
date so that I can import.
Is there a way the macro can ignore the date so that it would still
open
the
file and import the data?
Again - your support is greatly appreciated
John