Utilizing VB Script in "active" Excel worksheet

[montanan]

Hello.

Macro Scheduler itself (keystroke sequences), plus some VB Script for complex functions, seems like a great combination.

Referencing and running the clunky "PERSONAL.XLS" global macro functions within Excel can be a challenge because they are not always present / accessible.

Sometimes lengthy Macro Scheduler keystroke sequences within Excel are not easy enough to debug or execute reliably.

I would like to execute some VB Script on an "active" worksheet to apply a custom date format to several columns.

Here is my code, adapted from one of the Macro Scheduler examples. It performs the desired functions on a new worksheet, but not what I want -- which is to perform this formatting on the "active" worksheet.

VBSTART
Sub Euro_Date_Format
Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

xlApp.visible = True
xlSheet.Application.Visible = True
xlSheet.Columns(7).NumberFormat = "yyyy-mm-dd;@"
xlSheet.Columns(8).NumberFormat = "yyyy-mm-dd;@"
xlSheet.Columns(15).NumberFormat = "yyyy-mm-dd;@"
xlSheet.Columns(16).NumberFormat = "yyyy-mm-dd;@"
xlSheet.Columns(19).NumberFormat = "yyyy-mm-dd;@"

Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

End Sub
VBEND

VBRun>Euro_Date_Format


I have looked into VB Help and there is an ActiveSheet property. The help file reads:

When a worksheet is the active sheet, you can use the ActiveSheet property to refer to it. The following example uses the Activate method to activate Sheet1, sets the page orientation to landscape mode, and then prints the worksheet.

Worksheets("Sheet1").Activate

I would greatly appreciate some help with tweaking my code above to do what I'm trying to do. I believe many others could benefit from understanding this as well.

-Richard

[gdyvig]

Instead of xlApp.Workbooks.Add

set xlBook=objExcel.Workbooks.Open(sResultsFile)
set xlSheet=objExcel.ActiveWorkbook.Worksheets("Sheet1")

You can change "Sheet1" to whatever you want.

Does this work?


Gale

[Marcus Tettmar]

You're calling Workbooks.Add, so your code *adds* a *new* workbook. Instead open the one you want with:

Set xlBook = xlApp.Workbooks.Open("somefile.xls")

And grab the sheet with:

Set xlSheet = xlBook.Worksheets("SHEET_NAME_HERE")

[montanan]

Gale and Marcus,

It's working great now.

Thank you!

-Richard