viewpoint-particle

Author Topic: Excel Mystery  (Read 490 times)

stanl

  • Pundit
  • *****
  • Posts: 668
Excel Mystery
« on: March 25, 2017, 06:19:02 am »
I have a very simple WB script that accepts a dropdown of project reports from various sources, performs multiple SQL statements from multiple data sources, updates links, pivots and slicers in Excel Report files. Not wanting the curious to look into the various Data Tabs that populate the Pivots I wrote a routine that can make/unmake those tabs very hidden or visible.  There were 2 ways to do this as a subroutine or a function. Both would require sending a 'mode' parameter, while the function would require an additional 'sheet' parameter:

[NOTE: this is not optimal code...]

The subroutine works perfectly
Code: Winbatch
#DefineSubRoutine hSheet(mode)
   If mode==1
      If oXL.Activeworkbook.Sheets("%cSheet%").Visible==2 Then oXL.Activeworkbook.Sheets("%cSheet%").Visible=-1
   Else
      If oXL.Activeworkbook.Sheets("%cSheet%").Visible==-1 Then oXL.Activeworkbook.Sheets("%cSheet%").Visible=2
   Endif
   Return(1)
#EndSubRoutine
 

While as a function, I get an OLE Object Not recognized error

Code: Winbatch
#DefineFunction hSheet(mode,cSheet)
   If mode==1
      If oXL.Activeworkbook.Sheets("%cSheet%").Visible==2 Then oXL.Activeworkbook.Sheets("%cSheet%").Visible=-1
   Else
      If oXL.Activeworkbook.Sheets("%cSheet%").Visible==-1 Then oXL.Activeworkbook.Sheets("%cSheet%").Visible=2
   Endif
   Return(1)
#EndFunction

 


Of course, I realize a function should only return a value, so this a not a WB problem, per se. I originally wrote just the subroutine and the function was just a test. I live in a world where slicers are the 'coup de gras' of analytics, but truth be told my 200 lines of WB script code run just as fast and accurate as several thousand lines of WBNET code.


JTaylor

  • Pundit
  • *****
  • Posts: 806
    • Data & Stuff Inc.
Re: Excel Mystery
« Reply #1 on: March 25, 2017, 07:36:03 am »
Don't you need to pass oXL to the function as well?

Jim

stanl

  • Pundit
  • *****
  • Posts: 668
Re: Excel Mystery
« Reply #2 on: March 26, 2017, 04:35:53 am »
Don't you need to pass oXL to the function as well?

Jim

Makes sense. Getting rusty.  :o