Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# VB Script to 'hide' columns in Excel file

Posted on 2013-12-12
Medium Priority
2,096 Views
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!
0
Question by:zequestioner
• 3
• 2

LVL 37

Expert Comment

ID: 39714592
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

LVL 37

Expert Comment

ID: 39714605
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

LVL 37

Accepted Solution

TommySzalapski earned 2000 total points
ID: 39714632
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

LVL 72

Expert Comment

ID: 39714650
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

LVL 1

Author Comment

ID: 39714750
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

LVL 72

Expert Comment

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

## Featured Post

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Progress
Starting up a Project
###### Suggested Courses
Course of the Month15 days, 12 hours left to enroll

#### 581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.