Open an Excel file via a script and Run a macro

[Sean]

I wish to open an Excel file via a script and run a specified macro,
then save the file at a certain time each day. I looked up the
Microsoft site and found the code below which Opens all files in a
directory, runs a macro on all then closes. It pretty much does what I
want except I just have one specified file, problem is I'm not sure
what part of the code I change, I just can't work it out.

Hope someone can help

Important information I guess is

Path where file exists: T:\2008\sean\my documents\Daily Report.xls
(this is a mapped drive on my PC)
Macro name: CompileReport

Code from Microsoft site below-

strComputer = "tvsfrank"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root
\cimv2")

Set colFileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _
& "ResultClass = CIM_DataFile")

Set objExcel = CreateObject("Excel.Application", strComputer)
objExcel.DisplayAlerts = False

For Each objFile In colFileList
If objFile.Extension = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Run("BoldfaceHeadings")

objWorkbook.SaveAs objFile.Name
objWorkbook.Close
End If
Next

objExcel.Quit

 

[Don Guillett]

The macro recorder is your friend
Sub Macro8()
'
' Macro8 Macro
' Macro recorded 9/24/2007 by Donald B. Guillett
'

'
Workbooks.Open Filename:="C:\yourfolder\yourfile.xls"
call your macro
' Range("F9").Select
' ActiveCell.FormulaR1C1 = "ddd"
' Range("F11").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

 

[Sean]

The macro recorder is your friend
Sub Macro8()
'
' Macro8 Macro
' Macro recorded 9/24/2007 by Donald B. Guillett
'

'
Workbooks.Open Filename:="C:\yourfolder\yourfile.xls"
call your macro
' Range("F9").Select
' ActiveCell.FormulaR1C1 = "ddd"
' Range("F11").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software















- Show quoted text -

Thanks Don, I was trying to do it via a script that I could call at a
specified time via Schedule Tasks

 

[Don Guillett]

Look in the vba help index for ONTIME

 

[Harlan Grove]

I wish to open an Excel file via a script and run a specified
macro, then save the file at a certain time each day. I looked up
the Microsoft site and found the code below which Opens all files
in a directory, runs a macro on all then closes. It pretty much
does what I want except I just have one specified file, problem is
I'm not sure what part of the code I change, I just can't work it
out.

If you can't figure out the sample code, what would happen when
anything went wrong with this? Automation can be very dangerous, so
you shouldn't be trying to use it unless you have a good idea what the
code would be doing.

Path where file exists: T:\2008\sean\my documents\Daily Report.xls
(this is a mapped drive on my PC)
Macro name: CompileReport ....
Code from Microsoft site below-

What Microsoft site? Presumably it's VBScript code.

strComputer = "tvsfrank"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & _
"\root\cimv2")

Set colFileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _
& "ResultClass = CIM_DataFile")

Do you really need to use Windows Management Instrumentation? If you
don't know what it's for, you really shouldn't be using it. While the
statements above only read system data, it could be use to change
system settings, and any glitches doing that could completely fubar
your PC.

Set objExcel = CreateObject("Excel.Application", strComputer)

This statement makes it appear Excel isn't already running. If you
want your script to launch Excel on your own PC, skip the 2nd argument
to CreateObject.

objExcel.DisplayAlerts = False ....
For Each objFile In colFileList

....

You don't need the loop construct.

Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)

....

However, you do want to open a particular workbook. The statement
above shows how. You just need to replace objFile.Name with your own
workbook's pathname.

objExcel.Run("BoldfaceHeadings")

This shows how to call a macro.

objWorkbook.SaveAs objFile.Name
objWorkbook.Close

....

These show how to save and close your workbook, but if you're saving
the file with its original filename, it's arguably better to use only
the .Close method with the SaveChanges parameter set to TRUE.

objExcel.Quit

And this ends the Excel session. Putting it all together,


'-- begin VBScript code --
fname = "T:\2008\sean\my documents\Daily Report.xls"
mname = "CompileReport"

Set oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False
Set oWB = oXL.Workbooks.Open(fname)
oXL.Run(mname)
oWB.Close SaveChanges:=True
oXL.Quit
'--- end VBScript Code ---


Then use create a Windows Scheduled Task to run this script (IMO,
better to use cscript than wscript for this sort of thing) at the
specified time. If there are any glitches, you may want to add
WScript.Echo statements to echo execution tracing information to a log
file as the script runs, and you may want to add error trapping to
your macro in your workbook as well as check the Excel/VBA error
object, in this case oXL.Err, in the script code after the macro
finishes.

 

[Sean]

If you can't figure out the sample code, what would happen when
anything went wrong with this? Automation can be very dangerous, so
you shouldn't be trying to use it unless you have a good idea what the
code would be doing.




What Microsoft site? Presumably it's VBScript code.




Do you really need to use Windows Management Instrumentation? If you
don't know what it's for, you really shouldn't be using it. While the
statements above only read system data, it could be use to change
system settings, and any glitches doing that could completely fubar
your PC.


This statement makes it appear Excel isn't already running. If you
want your script to launch Excel on your own PC, skip the 2nd argument
to CreateObject.


...

You don't need the loop construct.


...

However, you do want to open a particular workbook. The statement
above shows how. You just need to replace objFile.Name with your own
workbook's pathname.


This shows how to call a macro.


...

These show how to save and close your workbook, but if you're saving
the file with its original filename, it's arguably better to use only
the .Close method with the SaveChanges parameter set to TRUE.


And this ends the Excel session. Putting it all together,

'-- begin VBScript code --
fname = "T:\2008\sean\my documents\Daily Report.xls"
mname = "CompileReport"

Set oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False
Set oWB = oXL.Workbooks.Open(fname)
oXL.Run(mname)
oWB.Close SaveChanges:=True
oXL.Quit
'--- end VBScript Code ---

Then use create a Windows Scheduled Task to run this script (IMO,
better to use cscript than wscript for this sort of thing) at the
specified time. If there are any glitches, you may want to add
WScript.Echo statements to echo execution tracing information to a log
file as the script runs, and you may want to add error trapping to
your macro in your workbook as well as check the Excel/VBA error
object, in this case oXL.Err, in the script code after the macro
finishes.

Harlan

Many thanks for your input. I've tied to understand on-timer in excel,
but simply could not get my head around it. Not even really too sure
if my post is even in the rght Group. So I thought 'simle' DOS batch
file that will

a) open an excel file
b) execute a macro that I already run manually each day at a certain
time
c) save and close the file
d) all run via schedule task (don't know what cscript is or even echo
statements)

Only thing I have to figure out now is, to check if MS Outlook is
open, if not open it, as one of my macro's e-mails out a Report

Thanks again

 

[Sean]

Harlan

Many thanks for your input. I've tied to understand on-timer in excel,
but simply could not get my head around it. Not even really too sure
if my post is even in the rght Group. So I thought 'simle' DOS batch
file that will

a) open an excel file
b) execute a macro that I already run manually each day at a certain
time
c) save and close the file
d) all run via schedule task (don't know what cscript is or even echo
statements)

Only thing I have to figure out now is, to check if MS Outlook is
open, if not open it, as one of my macro's e-mails out a Report

Thanks again- Hide quoted text -

- Show quoted text -

This is where I picked up the code

http://www.microsoft.com/technet/scriptcenter/scripts/office/excel/default.mspx?mfr=true

 

[Harlan Grove]

. . . So I thought 'simle' DOS batch file that will

a) open an excel file
b) execute a macro that I already run manually each day at a certain
time
c) save and close the file
d) all run via schedule task (don't know what cscript is or even echo
statements)

Change the name of your macro to Auto_Open, and it'll run automatically when
its workbook is opened from the command line. Then use a one line batch file
like

@start "T:\2008\sean\my documents\Daily Report.xls"

which would use the .xls file type's association with Excel to start Excel
and open this file as if its filename had followed the Excel .EXE's filename
on the command line. Then the file's Auto_Open macro would run once it's
open. Add code to save and close to that macro. Also add code to e-mail it.

Only thing I have to figure out now is, to check if MS Outlook is
open, if not open it, as one of my macro's e-mails out a Report

I've never written e-mail macros in Excel for use with Outlook. Check out
the following for code to do that.

http://www.rondebruin.nl/sendmail.htm

 

[Sean]

...



Change the name of your macro to Auto_Open, and it'll run automatically when
its workbook is opened from the command line. Then use a one line batch file
like

@start "T:\2008\sean\my documents\Daily Report.xls"

which would use the .xls file type's association with Excel to start Excel
and open this file as if its filename had followed the Excel .EXE's filename
on the command line. Then the file's Auto_Open macro would run once it's
open. Add code to save and close to that macro. Also add code to e-mail it.


I've never written e-mail macros in Excel for use with Outlook. Check out
the following for code to do that.

http://www.rondebruin.nl/sendmail.htm

Thanks Harlan, wow one line, didn't think it was that simple

Yes I've already taken and adopted Ron's codes, so now all I'm trying
to do is automate my task of clicking the macro button, same time
every day 364