| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening a file with date extension
Hi John,
sorry, one ommition, should be as follows: set wb = workbooks.open (sfilename) Regards, Ivan |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determine Excel file version with no file extension. | Excel Discussion (Misc queries) | |||
| file format or file extension is not valid...error message | Excel Discussion (Misc queries) | |||
| Additional file with no extension created during File Save As proc | Excel Discussion (Misc queries) | |||
| I need to download an exel spreadsheet file. (file extension :xls) | Excel Discussion (Misc queries) | |||
| Opening CSV file causes date differences | Excel Programming | |||
Linear Mode
