Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBScript help

Posted on 2008-11-13
6
Medium Priority
?
271 Views
Last Modified: 2012-05-05
Hi:

In a folder I have Excel files named with dates like:
myfilename_11012008,xls
myfilename_11022008.xls
---
myfilename_11122008.xls

These files have a Workbook_Open() procedure  to do some task and save itself automatically when the workbook is opened.

I need a vb script that'll be run daily to:
1. figure out the file name of yesterday's file.
2. save that file as myfilename_<today's date>.xls (This shouldn't be a problem even if the original file is opened/being viewed by another user, I think?)
3. open it - so the vba code I wrote will do the updates and save itself.

I need to finish this quickly but unfortunately I am new to vb script. Please help.
0
Comment
Question by:sdc248
  • 4
  • 2
6 Comments
 
LVL 1

Expert Comment

by:lewismay
ID: 22958437
Hello,

The following code should work for you if you change the paths. You may want to add some error trapping in case the file can not be found.

Hope this works for you, enjoy
Dim shell, file_path, old_file_name, new_file_name
 
Set shell = CreateObject("WScript.Shell")
 
file_path = "c:\yourdir\"
old_file_name = "myfilename_" & get_date(-1) & ".xls"
new_file_name = "myfilename_" & get_date(0) & ".xls"
 
Call shell.Run("cmd /c copy " & chr(34) & file_path & old_file_name & chr(34) & " " & _
               chr(34) & file_path & new_file_name & chr(34), 0, true)
 
Call shell.Run("cmd /c " & chr(34) & file_path & new_file_name & chr(34), 0, true)
 
Private Function get_date(day_offset)
 
  Dim day_string, month_string
 
  get_date = DateAdd("d", day_offset, Date())
 
  day_string = DatePart("d", get_date)
  month_string = DatePart("m", get_date)
 
  If(len(day_string) < 2) Then day_string = "0" & day_string
  If(len(month_string) < 2) Then month_string = "0" & month_string
 
  get_date = (month_string & day_string & DatePart("yyyy", get_date))
 
End Function

Open in new window

0
 

Author Comment

by:sdc248
ID: 22960385
Thanks for the code, Lewismay.

Is there a way to first test whether the old file for yesterday is there, if not, try the day before and so on? I am thinking of a loop that trys 10 days before giving up and exit.
0
 
LVL 1

Expert Comment

by:lewismay
ID: 22960694
Ok something like this will probably work

Dim shell, fso
Dim file_path, old_file_name, new_file_name, i
 
Set shell = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
 
file_path = "c:\yourdir\"
old_file_name = "myfilename_" & get_date(-1) & ".xls"
new_file_name = "myfilename_" & get_date(0) & ".xls"
 
i = -1
 
Do Until (fso.FileExists(file_path & old_file_name))
 
  i = i - 1
  old_file_name = "myfilename_" & get_date(i) & ".xls"
 
  If i < -10 Then Exit Do
  
Loop
 
Call shell.Run("cmd /c copy " & chr(34) & file_path & old_file_name & chr(34) & " " & _
               chr(34) & file_path & new_file_name & chr(34), 0, true)
 
Call shell.Run("cmd /c " & chr(34) & file_path & new_file_name & chr(34), 0, true)
 
Private Function get_date(day_offset)
 
  Dim day_string, month_string
 
  get_date = DateAdd("d", day_offset, Date())
 
  day_string = DatePart("d", get_date)
  month_string = DatePart("m", get_date)
 
  If(len(day_string) < 2) Then day_string = "0" & day_string
  If(len(month_string) < 2) Then month_string = "0" & month_string
 
  get_date = (month_string & day_string & DatePart("yyyy", get_date))
 
End Function

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Accepted Solution

by:
lewismay earned 1000 total points
ID: 22960761
Whoops slight mistake i needs to start at 0, or you need to decrement after generating the file name, fixed code below
Dim shell, fso
Dim file_path, old_file_name, new_file_name, i
 
Set shell = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
 
file_path = "c:\yourdir\"
old_file_name = "myfilename_" & get_date(-1) & ".xls"
new_file_name = "myfilename_" & get_date(0) & ".xls"
 
i = 0
 
Do Until (fso.FileExists(file_path & old_file_name))
 
  i = i - 1
  old_file_name = "myfilename_" & get_date(i) & ".xls"
 
  If i < -10 Then Exit Do
  
Loop
 
Call shell.Run("cmd /c copy " & chr(34) & file_path & old_file_name & chr(34) & " " & _
               chr(34) & file_path & new_file_name & chr(34), 0, true)
 
Call shell.Run("cmd /c " & chr(34) & file_path & new_file_name & chr(34), 0, true)
 
Private Function get_date(day_offset)
 
  Dim day_string, month_string
 
  get_date = DateAdd("d", day_offset, Date())
 
  day_string = DatePart("d", get_date)
  month_string = DatePart("m", get_date)
 
  If(len(day_string) < 2) Then day_string = "0" & day_string
  If(len(month_string) < 2) Then month_string = "0" & month_string
 
  get_date = (month_string & day_string & DatePart("yyyy", get_date))
 
End Function

Open in new window

0
 

Author Comment

by:sdc248
ID: 22961328
The code works fine until, when running the new file, the program hangs and a dialogue window pops up asking whether to enable macro. How to work around this? Thansk.
0
 
LVL 1

Expert Comment

by:lewismay
ID: 22974851
You just need to set macro security in excel to Low
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question