Solved

VB Script to 'hide' columns in Excel file

Posted on 2013-12-12
6
1,252 Views
Last Modified: 2013-12-12
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
Comment
Question by:zequestioner
  • 3
  • 2
6 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
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

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
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

by:
TommySzalapski earned 500 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

Open in new window

0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 69

Expert Comment

by:Qlemo
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

Open in new window

0
 
LVL 1

Author Comment

by:zequestioner
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 69

Expert Comment

by:Qlemo
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBScript Write Column Headers 3 36
Alphabetical Order for Letters 2 21
Excel IF formula 3 19
VBA taking too long 5 14
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

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

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

Join & Ask a Question