• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1122
  • Last Modified:

Convert Excel macro to VBScript

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
mike_VBS
Asked:
mike_VBS
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
mike_VBSAuthor Commented:
get the following error on line 10

Microsoft VBScript compilation error: Expected statement
0
 
Patrick MatthewsCommented:
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
 
mike_VBSAuthor Commented:
Yes, that works - hacking around a could have sworn I tried that - I guess not.

Hey, thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now