ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Opening a file with date extension (https://www.excelbanter.com/excel-programming/362033-opening-file-date-extension.html)

JohnUK

Opening a file with date extension
 
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

Bob Phillips[_14_]

Opening a file with date extension
 
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




Ivan Raiminius

Opening a file with date extension
 
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


JohnUK

Opening a file with date extension
 
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





JohnUK

Opening a file with date extension
 
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



Ivan Raiminius

Opening a file with date extension
 
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


Bob Phillips[_14_]

Opening a file with date extension
 
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







JohnUK

Opening a file with date extension
 
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



Ivan Raiminius

Opening a file with date extension
 
Hi John,

sorry, one ommition, should be as follows:
set wb = workbooks.open (sfilename)

Regards,
Ivan


JohnUK

Opening a file with date extension
 
Getting closer. I have now got:

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
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
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

It opens up the file a treat (Also thanks to Bob - your code worked a treat
as well)

But - the problem I have now is:

The code doesnt recognise the file once its opened. I even tried putting in:
Windows("sfilename").Activate

John

"Ivan Raiminius" wrote:

Hi John,

sorry, one ommition, should be as follows:
set wb = workbooks.open (sfilename)

Regards,
Ivan



JohnUK

Opening a file with date extension
 
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







Bob Phillips[_14_]

Opening a file with date extension
 
Replace the windows with the wb variable

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
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
wb.Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
wb.Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
wb.Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
Getting closer. I have now got:

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
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
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

It opens up the file a treat (Also thanks to Bob - your code worked a

treat
as well)

But - the problem I have now is:

The code doesnt recognise the file once its opened. I even tried putting

in:
Windows("sfilename").Activate

John

"Ivan Raiminius" wrote:

Hi John,

sorry, one ommition, should be as follows:
set wb = workbooks.open (sfilename)

Regards,
Ivan





JohnUK

Opening a file with date extension
 
Sorry Bob, I am becoming a pain arnt I
I now have :
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
wb("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
wb("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate

in the code, and still it doesnt work

"Bob Phillips" wrote:

Replace the windows with the wb variable

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
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
wb.Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
wb.Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
wb.Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
Getting closer. I have now got:

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
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
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

It opens up the file a treat (Also thanks to Bob - your code worked a

treat
as well)

But - the problem I have now is:

The code doesnt recognise the file once its opened. I even tried putting

in:
Windows("sfilename").Activate

John

"Ivan Raiminius" wrote:

Hi John,

sorry, one ommition, should be as follows:
set wb = workbooks.open (sfilename)

Regards,
Ivan






JohnUK

Opening a file with date extension
 
Ok - I have it. I replaced the:
wb("Competitor Pricing Monitor Update.*.xls").Activate
With:
wb.Activate
Thanks you both Ivan & Bob for your help and patience.
Very much appreciated
All the best
John

"JohnUK" wrote:

Sorry Bob, I am becoming a pain arnt I
I now have :
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
wb("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
wb("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate

in the code, and still it doesnt work

"Bob Phillips" wrote:

Replace the windows with the wb variable

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
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
wb.Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
wb.Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
wb.Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"JohnUK" wrote in message
...
Getting closer. I have now got:

Application.ScreenUpdating = False
Dim wb As Workbook
sPath = Application.DefaultFilePath & "\" & sFile
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
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Windows("Price Monitor.xls").Activate
Sheets("Update File").Visible = True
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = True
Sheets("Update File").Select
Columns("D:F").Select
Selection.Copy
Windows("Price Monitor.xls").Activate
Sheets("Update File").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("A1").Select
Sheets("Update File").Visible = False
Windows("Competitor Pricing Monitor Update.*.xls").Activate
Sheets("Update File").Visible = False
Sheets(".").Select
Windows("Price Monitor.xls").Activate
Sheets("Input").Select
Range("H9").Select
Msg = "OPERATION COMPLETE"

It opens up the file a treat (Also thanks to Bob - your code worked a

treat
as well)

But - the problem I have now is:

The code doesnt recognise the file once its opened. I even tried putting

in:
Windows("sfilename").Activate

John

"Ivan Raiminius" wrote:

Hi John,

sorry, one ommition, should be as follows:
set wb = workbooks.open (sfilename)

Regards,
Ivan







All times are GMT +1. The time now is 11:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com