Script to Open and Save Files

[Karl Burrows]

I have several Excel workbooks that are dependent on each other and if they
aren't open in a certain order, data will not update correctly. Needless to
say, this happens much too often. Can someone help me get started on a
script that when executed (just a batch file will work fine) will open the
first workbook, accepts all prompts (\y), save and then go to the next
workbook and follow the same procedures?

Thanks!

 

[Guest]

Try the following VBScript
Change the path/file names on the lines that begin with (WSHShell.Open

Sleep values are in milliseconds, adjust as necessary. (Wscript.Sleep 8000
- 1000 ms = 1 secon
- 2000 ms = 2 second

Do not make the "Sleep" settings too low. Give the Excel program and files enough time to open

Add any keystrokes between the double-quotes (KeyboardObject.SendKeys " "

Single characters may be entered as individual letters. Most other keys need to be enclosed in brackets {
- Alt key:
- Ctrl key:
- Enter key: {ENTER
- Escape key: {ESC
- Letters: yn
- Space bar: {SPACE

A {SPACE} will respond or 'click' the default button on dialog boxes

'''''''''''''''''''''''''''''''''''''''''''''''

Dim KeyboardObjec
Dim WSHShel
Set KeyboardObject=CreateObject("WScript.Shell"
Set WSHShell=CreateObject("Shell.Application"
WSHShell.Open "C:\foldername\filename1.xls
Wscript.Sleep 800
KeyboardObject.SendKeys "
WSHShell.Open "C:\foldername\filename2.xls
Wscript.Sleep 800
KeyboardObject.SendKeys "
WSHShell.Open "C:\foldername\filename3.xls
Wscript.Sleep 800
KeyboardObject.SendKeys "

'''''''''''''''''''''''''''''''''''''''''''''''

Austin M. Horst

 

[Karl Burrows]

Thanks Austin!

Now this looks like it will only open files and not save and close them.
The first file must open, update, save and close before the next follows the
same process.

Also, will this run on the desktop or other to execute like a batch file?
How do I save it?

Thanks again!!!

Try the following VBScript.
Change the path/file names on the lines that begin with (WSHShell.Open)

Sleep values are in milliseconds, adjust as necessary. (Wscript.Sleep 8000)
- 1000 ms = 1 second
- 2000 ms = 2 seconds

Do not make the "Sleep" settings too low. Give the Excel program and files
enough time to open.

Add any keystrokes between the double-quotes (KeyboardObject.SendKeys " ")

Single characters may be entered as individual letters. Most other keys need
to be enclosed in brackets {}
- Alt key: %
- Ctrl key: ^
- Enter key: {ENTER}
- Escape key: {ESC}
- Letters: ync
- Space bar: {SPACE}

A {SPACE} will respond or 'click' the default button on dialog boxes.


''''''''''''''''''''''''''''''''''''''''''''''''

Dim KeyboardObject
Dim WSHShell
Set KeyboardObject=CreateObject("WScript.Shell")
Set WSHShell=CreateObject("Shell.Application")
WSHShell.Open "C:\foldername\filename1.xls"
Wscript.Sleep 8000
KeyboardObject.SendKeys " "
WSHShell.Open "C:\foldername\filename2.xls"
Wscript.Sleep 8000
KeyboardObject.SendKeys " "
WSHShell.Open "C:\foldername\filename3.xls"
Wscript.Sleep 8000
KeyboardObject.SendKeys " "

''''''''''''''''''''''''''''''''''''''''''''''''


Austin M. Horst

 

[Guest]

OK, scrap the last sample.
Make any modifications you need.

This example:
- opens a file (named c:\filename1.xls)
- writes the number 1 in cell A1
- saves the file as c:\filename1.xls
- closes the file
- opens another file (named c:\filename2.xls)
- writes the number 2 in cell A1
- saves the file as c:\filename2.xls
- closes the file

'''''''''''''''''''''''''''''''''''''''''''''''''
Dim objExcel
Dim objKeystrokes
Set objExcel = createobject("Excel.Application")
Set objKeystrokes=CreateObject("WScript.Shell")
objExcel.Visible = true
objExcel.Workbooks.Open "c:\filename1.xls"
Wscript.Sleep 1000
objKeystrokes.SendKeys "1{ENTER}"
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close

objExcel.Workbooks.Open "c:\filename2.xls"
Wscript.Sleep 1000
objKeystrokes.SendKeys "2{ENTER}"
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
'''''''''''''''''''''''''''''''''''''''''''''''''

Paste the code into Notepad.
Save the file with a .vbs extension.


Austin M. Horst

 

[Karl Burrows]

Okay, I think I have figured out some of the problems:

1. The code is trying to write a number into a cell where the
workbook/worksheets are protected.
2. I changed the {ENTER} to {SPACE} to prompt for the default option in the
dialog box for 'enable Macros', but that still didn't work, so not sure why
I still get a prompt to enable.
3. I'm not sure if the way you are opening the file is bypassing the normal
open procedure for Excel. It appears to not open Excel add-ins.

The basic procedures seem to work, it just all the little things that go
with the files. Can I set the sleep to a number larger than 8000?

Thanks again!!!

OK, scrap the last sample.
Make any modifications you need.

This example:
- opens a file (named c:\filename1.xls)
- writes the number 1 in cell A1
- saves the file as c:\filename1.xls
- closes the file
- opens another file (named c:\filename2.xls)
- writes the number 2 in cell A1
- saves the file as c:\filename2.xls
- closes the file

'''''''''''''''''''''''''''''''''''''''''''''''''
Dim objExcel
Dim objKeystrokes
Set objExcel = createobject("Excel.Application")
Set objKeystrokes=CreateObject("WScript.Shell")
objExcel.Visible = true
objExcel.Workbooks.Open "c:\filename1.xls"
Wscript.Sleep 1000
objKeystrokes.SendKeys "1{ENTER}"
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close

objExcel.Workbooks.Open "c:\filename2.xls"
Wscript.Sleep 1000
objKeystrokes.SendKeys "2{ENTER}"
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
'''''''''''''''''''''''''''''''''''''''''''''''''

Paste the code into Notepad.
Save the file with a .vbs extension.


Austin M. Horst

 

[Guest]

I don't know of a limit on WScript.Sleep
If it would have a maximum, you could just use multiple lines in a row
Wscript.Sleep 100000
Wscript.Sleep 100000
Wscript.Sleep 100000

Austin M. Horst