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
zequestionerAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor 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

Open in new window

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

0
 
TommySzalapskiCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
QlemoBatchelor, 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

Open in new window

0
 
zequestionerAuthor 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

Tommy, your code worked perfectly.

Thanks!
0
 
QlemoBatchelor, 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.

All Courses

From novice to tech pro — start learning today.