# 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!
LVL 1
###### Who is Participating?

Commented:
Actually, you can do it all from the vb script, sorry.

Set xl = CreateObject("Excel.application")

xl.Application.Workbooks.Open "C:\temp\junk.xls"
set book = xl.Application.Workbooks("junk.xls")
xl.Application.Visible = True
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
xl.Application.Quit

0

Commented:
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

0

Commented:
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
0

Batchelor, Developer and EE Topic AdvisorCommented:
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

0

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

Thanks!
0

Batchelor, Developer and EE Topic AdvisorCommented:
If you replace .Range by .Columns, it should work. Tommy's code contains some unnecessary stuff, but that doesn't do any harm.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.