asked on

VB Script to 'hide' columns in Excel file

Can someone provide an example vb script or macro which can do the following:

1. Open file.xlsx
2. Hide specific columns, for example A, B, D, F J. (delete the columns would be fine too)
3. Save file.xlsx
4. Close Excel.

Thanks experts!

TommySzalapski

This is what it could look like from VBA (within Excel)

    Dim book As Workbook
    Dim sheet As Worksheet
    Set book = Application.Workbooks.Open("C:\temp\junk.xls")
    Set sheet = book.Worksheets(1)
    sheet.Columns("A:A").Hidden = True
    sheet.Columns("B:B").Hidden = True
    sheet.Columns("D:D").Hidden = True
    sheet.Columns("F:F").Hidden = True
    sheet.Columns("J:J").Hidden = True
    book.Save
    Application.Quit

Open in new window

TommySzalapski

If you put that in an Excel macro, then you can call it from vbscript like this
Set xl = CreateObject("Excel.application")

xl.Application.Workbooks.Open "C:\temp\junk.xlsm"
xl.Application.Visible = True
xl.Application.run "'junk.xlsm'!macronametorun"

Set xl = Nothing

ASKER CERTIFIED SOLUTION

TommySzalapski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

You wouldn't want to have to include the macro in each XLS file. The general steps are correct, but we can do all that in VBS directly:

Set xl = CreateObject("Excel.application")

set wb = xl.Workbooks.Open("C:\temp\junk.xlsm")
with wb.WorkSheets(1)
  .Range("A:B").Hidden = True
  .Range("D:D").Hidden = True
  .Range("F:F").Hidden = True
  .Range("J:J").Hidden = True
end with

wb.Save
xl.Quit
Set xl = Nothing   ' not necessary, but good style

Open in new window

zequestioner

ASKER

Qlemo, your code gave this message: c:\ee\HideCol.vbs(5, 3) Microsoft Excel: Unable to set the Hidden property of the Range class

Tommy, your code worked perfectly.

Thanks!

Qlemo

If you replace .Range by .Columns, it should work. Tommy's code contains some unnecessary stuff, but that doesn't do any harm.