Solved

Convert Excel macro to VBScript

Posted on 2013-01-20
4
884 Views
Last Modified: 2013-01-20
I'm trying to format excel from VBS  - I'm grouping several columns and it works fine but I need to collapse the column level. The last line (in bold) was recorded using the macro recorder. My question is: how do I translate this so it will run in VBS (how do I collapse the outline in VBS)?

Set myExcel = CreateObject("Excel.Application")
myExcel.visible = TRUE
Set myWorkbook=myExcel.Workbooks.Open("C:\Scripts\10_Macro_Format.xlsx")
Set mySheet = myWorkbook.sheets(1)
mySheet.Range("C1:H1").Group

ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
0
Comment
Question by:mike_VBS
  • 2
  • 2
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38799228
Try this:

Dim myExcel, myWorkbook, mySheet

Set myExcel = CreateObject("Excel.Application")
myExcel.visible = TRUE
Set myWorkbook=myExcel.Workbooks.Open("C:\Scripts\10_Macro_Format.xlsx")
Set mySheet = myWorkbook.sheets(1)

With mySheet
    .Range("C1:H1").Group
    .Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
End With

Open in new window

0
 

Author Comment

by:mike_VBS
ID: 38799333
get the following error on line 10

Microsoft VBScript compilation error: Expected statement
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38799395
Sorry, forgot that VBScript does not support passing arguments by name.

Dim myExcel, myWorkbook, mySheet

Set myExcel = CreateObject("Excel.Application")
myExcel.visible = TRUE
Set myWorkbook=myExcel.Workbooks.Open("C:\Scripts\10_Macro_Format.xlsx")
Set mySheet = myWorkbook.sheets(1)

With mySheet
    .Range("C1:H1").Group
    .Outline.ShowLevels 0, 1
End With

Open in new window

0
 

Author Comment

by:mike_VBS
ID: 38799413
Yes, that works - hacking around a could have sworn I tried that - I guess not.

Hey, thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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