Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run .vbs script on client workbooks
Hi -
I am not a programmer and have a question I hope I can explain and that you can answer. The agency I work for requests hundreds of excel workbooks from other state agencies. We are auditors. (Please don't hold that against me. I'm not an auditor. I just keep their computers running.) Since these workbooks come from other agencies, there is no standard format or style. Many times, the workbooks have hidden rows and columns and may contain one sheet or several sheets. I have been asked if it is possible to run a Macro on the workbook to unhide the columns and rows when the workbook opens. I have the Macro: (I have saved it as a .vbs file) Sub ShowHiddenRowsAndColumns() ' ' showhiddenrows Macro ' exposes hidden rows and columns ' Selection.EntireColumn.Hidden = False Selection.EntireRow.Hidden = False End Sub This works for the workbook it was created in. We want to run it on a workbook that does not have the macro in it when the file opens. Is there a simple way to do this? Thank you very much in advance for your time and effort in helping me. Wendy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run .vbs script on client workbooks
Hi Wendy
Macros are VBA, not VBS, so you can't port code like that without altering it a bit. I'd rather create a "magic workbook" that does this.Open the magic workbook first, and it will unhide all rows and columns in all sheets in all workbooks that are opened after in the session, as long as the book is open that is. Here's how to make it: Open the VB editor in a new workbook. Go to the ThisWorkbook module and paste this in: '************ top of module ********************** Option Explicit Private WithEvents App As Application Private Sub Workbook_Open() Set App = Application End Sub Private Sub App_WorkbookOpen(ByVal Wb As Workbook) Dim oSht As Worksheet On Error Resume Next For Each oSht In Wb.Worksheets oSht.Cells.EntireColumn.Hidden = False oSht.Cells.EntireRow.Hidden = False Next End Sub '***************** end of code ****************** Save this as either MagicBook.xls or MagicBook.xlsm (2007 version only). Close it, reopen it and Excel will handle the rest without further interaction. Exception is if the sheets are password protected. Code will not err, but not unhide anything either. It's possible to include password breaking code in the routine if so, but it complicates it a bit, so i'll leave that for now. HTH. Best wishes Harald "goplayoutside" wrote in message ... Hi - I am not a programmer and have a question I hope I can explain and that you can answer. The agency I work for requests hundreds of excel workbooks from other state agencies. We are auditors. (Please don't hold that against me. I'm not an auditor. I just keep their computers running.) Since these workbooks come from other agencies, there is no standard format or style. Many times, the workbooks have hidden rows and columns and may contain one sheet or several sheets. I have been asked if it is possible to run a Macro on the workbook to unhide the columns and rows when the workbook opens. I have the Macro: (I have saved it as a .vbs file) Sub ShowHiddenRowsAndColumns() ' ' showhiddenrows Macro ' exposes hidden rows and columns ' Selection.EntireColumn.Hidden = False Selection.EntireRow.Hidden = False End Sub This works for the workbook it was created in. We want to run it on a workbook that does not have the macro in it when the file opens. Is there a simple way to do this? Thank you very much in advance for your time and effort in helping me. Wendy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rtd client | Excel Discussion (Misc queries) | |||
script to send data from worksheet to multiple workbooks | Excel Programming | |||
Client side script in office web component | Charts and Charting in Excel | |||
Excel 2000/XP script to Excel97 script | Excel Programming | |||
Client using 97 & XP | Excel Programming |