Excel

At some point you have to decide whether you're going to be a politician or an engineer. You cannot be both. To be a politician is to champion perception over reality. To be an engineer is to make perception subservient to reality. They are opposites. You can't do both simultaneously.

—H. W. Kenton

Hi, My plan for this website it to post useful pieces of code that I find or write that have been useful to me in my work. I'm not really a guru at all. I'm actually kind of lazy. Laziness - that's my main motivator when I'm writing code. I want the computer to do all my work! Most of my programming experience is with VBA in Access, Excel, Outlook. Also use SQL Server and recently have started to use VBScript, VB.Net and C#.Net (2005) and a little bit of javascript.

Here's some VBScript that opens a spreadsheet and runs a macro. I was helping a colleage build a DTS package. I don't know DTS very well and he doesn't know VB so he asked for my help. You can use VBScript in a DTS package, but you can also create a standalone VBScript file. All you need to do is save a textfile (notepad, wordpad) with the .vbs extension. Then you can doubleclick the VBScript file to run it.

VBScript

' Open spreadsheet and run macro

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\brogers\Desktop\DTS_Test.xls")

objExcel.run("macro1")

objWorkbook.Close True

objExcel.quit

Here's a script that loops through every excel file in a folder and runs a macro in each spreadsheet. I have a VBA version that I'll try to find and post later.

This script uses the FileSystemObject and searches for files ending in xls.

'Loop through spreadsheets in folder and run macro

strPath = "C:\Documents and Settings\br\Desktop"

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName (objFile.Path) = "xls" Then

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

objExcel.run("macro1")

objWorkbook.Close True 'Save changes

End If

Next

objExcel.Quit