?
Solved

Convert Excel macro to VBScript

Posted on 2013-01-20
4
Medium Priority
?
975 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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