Pyro's Excel Goodies

[alexander]

I do, all props to the king of macros :)

 

All hail Pyro :):wave::)

[Pyrotex]

You are very welcome, Alexander.

Thanks for asking.

It would not bother me at all if I were to become a "resource" for all Hypographites who need spreadsheet expertise of any kind.

 

Pyro, King of the Macros :D

:thumbs_up

[Pyrotex]

BTW, Alexander, did you TRY the solution? Did it work?

[alexander]

Well, ok, we actually came up with a solution a while ago, i'm now trying to do conditional hides, and for the time being it's not working

 

here's the macro code we use now for hiding the fields (courtesy of the person in the AV division, who's name i don't think makes any difference here):

(btw no separate table required to restore the width :phones: )

 

Sub company_format_review()
   Columns("D:N").Select
   Selection.EntireColumn.Hidden = False
   Range("A8").Select
   With ActiveSheet.PageSetup
   End With
   ActiveSheet.PageSetup.PrintArea = ""
   With ActiveSheet.PageSetup
       .Orientation = xlLandscape
   End With
End Sub

Sub Customer_review()
   Range("E:E,G:G,H:H,I:I,J:J,K:K").Select
   Range("K1").Activate
   Selection.EntireColumn.Hidden = True
   Range("A8").Select
   With ActiveSheet.PageSetup
   End With
   ActiveSheet.PageSetup.PrintArea = ""
   With ActiveSheet.PageSetup
       .Orientation = xlPortrait
   End With
End Sub

 

now here is the new perplexion

 

have a field that points to another field that does calculations for say labor, what i wanna do is hide the whole row if the value is nothing, and if there is a value, i dont want to hide the row. And i'll need to do this for a dozen or so rows...

 

Pong back to you Pyro, help me Pyro-One-Kenobi, for you this is like fun, and perhaps not even anything challenging, and i don't like M$ Office

[Pyrotex]

Well, ok, we actually came up with a solution a while ago...

Okay, your friend's code will also work. Hiding a column produces the same effects as setting its width to zero. However, your friend's code is a tad sloppy. Instead of:

With ActiveSheet.PageSetup
   End With
   ActiveSheet.PageSetup.PrintArea = ""
   With ActiveSheet.PageSetup
       .Orientation = xlLandscape
   End With

you should have this, which does exactly the same thing without duplicate statements.

With ActiveSheet.PageSetup
   .PrintArea = ""
   .Orientation = xlLandscape
End With

have a field that points to another field that does calculations for say labor, what i wanna do is hide the whole row if the value is nothing, and if there is a value, i dont want to hide the row. And i'll need to do this for a dozen or so rows...

Certainly a macro can do this, but it cannot be a "conditional format" kinda thing. In other words, Excel will not automatically set a row height to zero because of some formula in a cell. Gotta be a macro. So, use the same technique as your friend did. Example:

Sub Hide_Zeros()
   Const cFlagOffset = 3
   Range("LaborHeader").Select
   Do Until Selection.Offset(0, cFlagOffset).Value = "XXXX"
       Selection.Offset(1, 0).Select 'move down one row
       If Selection.Value = 0 Then
           Selection.EntireRow.Hidden = True
       Else
           Selection.EntireRow.Hidden = False
       End If
   Loop
   Range("LaborHeader").Select
End Sub

Now, this assumes you have a Range-Name defined for the "header" of the Labor column. Change it to whatever the header actually is.

And, this assumes that you have a "Flag" (XXXX, in this case) situated off to the side that marks the END of the Labor data. The offset of this Flag (3 columns to the right, in this case) is defined as a constant and its value is up to you. So, for example, if your Labor values are in column "H", then this macro looks for "XXXX" in column "K" in the the same row as your LAST Labor value (whether its zero or not).

You can execute the macro with a keyboard shortcut, but I recommend a button.

And you will definately want to show ALL rows from time to time, so you need this macro:

Sub Show_All()
   Const cFlagOffset = 3
   Range("LaborHeader").Select
   Do Until Selection.Offset(0, cFlagOffset).Value = "XXXX"
       Selection.Offset(1, 0).Select 'move down one row
       Selection.EntireRow.Hidden = False
   Loop
   Range("LaborHeader").Select
End Sub

Of course, if your Labor Table has a constant number of rows, you don't need to use a Flag to mark the end ("XXXX"). You can replace the "Do/Loop" statement with:

    For I = 1 to 12
   ...
   Next I

[alexander]

ok this is what i settled on doing:

 

to the right of the various fields i had to hide, i set a function with a value of, as an example:

 

=NOT(NOT(A1))

 

and then macro was straight forward (after 4 hours of tinkering with it)

 

why? this effectively casts any value in the field as boolean, any 0, false, or null will be cast as false, and anything else as true, which is easy to check afterwards...

 

Sub hide_empty_fields_bottom()
   For I = 1 To 10
       Range("B" + CStr(I)).Select
       Selection.EntireRow.Hidden = Selection.Value Xor True
   Next I
   Selection.EntireColumn.Hidden = True
End Sub

Sub unhide_empty_fields_bottom()
   For I = 1 To 10
       Range("B" + CStr(I)).Select
       Selection.EntireRow.Hidden = False
   Next I
   Selection.EntireColumn.Hidden = True
End Sub

 

though in all reality they should be combined into a single function, but i dont care enough to do that...

 

Problem i ran into was that the range function was returning a value that i couldn't even cast, it was so insanely weird, probalby due to what has to happen on the back end of the function or formatting or something.... so after hours of banging my head against the table, just rewrote it as such :hihi: (it works now)

[Pyrotex]

Your macros, and the use of NOT(NOT(cell)) seems extravagently over-complicated for such a simple task.

[Jimoin]

Less with the Excel & more with the Book review Pyro!

[Pyrotex]

Less with the Excel & more with the Book review Pyro!

Sheesh! Can't youse guys give me a break? Okay... [grumble]... back to the book review.

[alexander]

Pyro, the problem is that the macro does not seem to recognize the value, whether i cast it to a bool, or anything else, or not even cast it at all, it constantly has a type mismatch, and that, honestly, blows, not not got me a value that was easily checked and understood by the macro, and yeah simple :)

 

I thought the macro was rather brilliant, not all that many people use the xor logic :phones: never mind using xor logic on the value of the cell, to assign row properties...

 

I could have used NOT(cell) and reversed the logic, but it seemed more confusing...

[Pyrotex]

ok ...Problem i ran into was that the range function was returning a value that i couldn't even cast, it was so insanely weird, probalby due to what has to happen on the back end of the function or formatting or something....

Hmmmm, he said.

Whatever column you're checking on, make sure the "empty" cells don't have spaces in them. Like, " ", you know. A check on whether or not a cell is 0/1 or F/T can fail if the cell contains text.

[alexander]

i went through every data type in excel, i can't cast it as any of them! Not not was a quick solution that just works. Besides, the content of the field are generated via a formula, so i dunno if there is a space there or not :|

[TheBigDog]

O Great Sage of Excel!! I come to the humbly to partake from your vast grid of wisdom.

 

I have a spreadsheet that I have distributed as an analysis tool. It essentially is two pieces. 1) An Access DB used to store data for analysis on the users local computer. 2) An Excel document that does the analysis of the data. When the user opens the sheet they use a UserForm to update their local Access database from a centralized SQL server. That is not the problem. Once the data is updated in the Access database I have the pivot tables in the Excel file all update. That is not the problem. The problem is coming as I try to add some sophistication to the update procedure.

 

Because the data set is getting larger I am giving the users the ability to filter what data is queried from Access into Excel. It is doing an analysis of all the product that passes through our manufacturing process. I trap the major events as "Milestones". I allow the user to select which Milestones they want to include in the data loaded to the pivot tables. I have the code working that creates the SQL query from their selections, but when I try and update the ThisWorkbook.PivotCaches(1).CommandText = sql I get an error 1004. I looked this up on the internet and found the problem is that I have several pivot tables all referencing this pivot table as the source - I do this to best utilize memory and to make it so I only have to do one refresh to update all the pivot tables.

 

My question is this... Do you know of a way I can update the CommandText property of the pivot caches object? I found some code on the net that is supposed to do it by updating the .Connection to change the .QueryType from ODBC to OLE and then back again after making the update, but it is not working. My next route is to decouple all of the pivot tables, update the .CommandText on the primary table, and then link them all to it again. But I figured I would check with you before I ran down that rabbit hole too far.

 

I hope you can help with this one! It has be pretty flustered.

 

Thanks!

 

Bill

[Pyrotex]

...I have a spreadsheet ...Access database from a centralized SQL server ...ThisWorkbook.PivotCaches(1).CommandText = sql ...error 1004 ...one refresh to update all the pivot tables ...update the CommandText property of the pivot caches object? ...change the .QueryType from ODBC to OLE ...decouple all of the pivot tables...rabbit hole ...flustered...

 

:eek2: :doh: :warped: :warped:

 

Uh... maybe if you SQL Pivot the decouple Command refresh between Access and the ODBC rabbit hole ... :naughty:

 

I am sorry old friend, but I cannot even begin to start to commence to solve your problem. I have never tried to cross-couple Excel and Access.

Sorry again.

[TheBigDog]

:singer: :singer: :singer: :warped: :warped:

 

Uh... maybe if you SQL Pivot the decouple Command refresh between Access and the ODBC rabbit hole ... :shrug:

 

I am sorry old friend, but I cannot even begin to start to commence to solve your problem. I have never tried to cross-couple Excel and Access.

Sorry again.

I solved the problem by going to the source. Instead of changing the query that extracts data from Access, I modify the Access query to filter out the data I don't want to see. This works like a charm. Here is what I do...

 

My pivot table is linked to an Access Query called Q1. I made a copy of that query called Q2. Neither Q1 nor Q2 have a WHERE clause. An Access Query is just a SQL statement saved as an object in Access.

 

With my code in Excel I have a tool for selecting the way the user wants to filter the data. I use this to make a WHERE clause. I then copy the Q2 SQL statement to a string, append the WHERE clause to it, and make it the new Q1 where clause. Then I refresh the pivot tables and voila.

 

Sub UpdateMilestonesQuery()

'Declare the variables I will use for this procedure
'Even when Option Explicit is false this is good form
   
'I use the "?_jet" to indicate objects used for working with Access (Jet)
'I do this because in many routines I will have connections to multiple sources of multiple types
   Dim wrk_jet As Workspace
   Dim db_jet As Database
   Dim qdf1_jet As QueryDef
   Dim qdf2_jet As QueryDef
   Dim sql_jet As String

'Establish connection to the Jet database
   Set wrk_jet = CreateWorkspace("NewJetWorkspace", "admin", "", dbUseJet)
   Set db_jet = wrk_jet.OpenDatabase(Setting("DWLocal"))
   Set qdf1_jet = db_jet.QueryDefs(Setting("DWLinkedQuery"))
   Set qdf2_jet = db_jet.QueryDefs(Setting("DWStaticQuery"))
   
   qdf1_jet.sql = _
       qdf2_jet.sql & " " & _
       "WHERE ([" & Setting("DWLinkedQuery") & "].[Milestones] IN (" & Setting("IncludeMilestones") & ")" & _
       "AND (" & Setting("DWLinkedQuery") & ".Plant IN (" & Setting("IncludedPlants") & ")) "

   db_jet.close

   RefreshPivotTables ThisWorkbook

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Below here are the other functions and subroutines that are called during the process. 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub UpdatePivotTables(ByVal wb As Workbook)

   Dim x as PivotCache
   Dim Count as Integer

   For Each x In wb.PivotCaches
       Count = Count + 1
       UpdateLog ("Refreshing pivot table data (" & Count & " of " & wb.PivotCaches.Count & ")")
       x.Refresh
   Next

End Sub

Function Setting(ByVal Field As String)

   Dim wrk_jet As Workspace
   Dim db_jet As Database
   Dim rst_jet As Recordset
   
'Establish connection to the Jet database
'Note that I have hard coded the location of the settings database.
'This is because it cannot call itself to find itself.
'My next version will include use of the Windows Registry to store the location of the settings DB.

   Set wrk_jet = CreateWorkspace("NewJetWorkspace", "admin", "", dbUseJet)
   Set db_jet = wrk_jet.OpenDatabase("C:ProjectsSettings.mdb")
   rst_jet = db_jet.OpenRecordset("SELECT Value FROM Settings WHERE Field = '" & Field & "'")
   
   If rst_jet.EOF = False Then
       Settings = rst_jet.Fields("Field").Value
   Else
       Settings = ""
   End If
   
   db_jet.Close
   
End Function

Sub UpdateLog(ByVal Message As String)

On Error Resume Next

'Writes to the screen
   txt_Log.Text = txt_Log.Text & Format(Now, Setting("LogTimeFormat") & " - " & Message & Chr(13)
   txt_Log.SetFocus
   
'Writes to the log file
   Set fs = CreateObject("Scripting.FileSystemObject")
   Set a = fs.Opentextfile(Setting("LogPath"), 8, True)
   a.writeline (Format(Now, Setting("LogDateTimeFormat") & " - " & Message & Chr(13))
   a.Close
   DoEvents
   
End Sub

[Pyrotex]

For those of you who do NOT have Microsoft Office (specifically, Excel) on your PCs...

 

At this site, you can download the Open Source versions -- and they are FREE!!!

 

Fly FREE, Little Turtle, Fly FREE!!!!

[Turtle]

For those of you who do NOT have Microsoft Office (specifically, Excel) on your PCs...

 

At this site, you can download the Open Source versions -- and they are FREE!!!

 

Fly FREE, Little Turtle, Fly FREE!!!!

 

:xparty: if i ever get my own computer, i'm coming here, then going there. while i can open most .xls sheets here on this machine with quattro, i do wistfully long for your lightning liveliness. :shrug:

 

all is not lost however, partly because i'm not dead yet, but also because i was telling one of the kids about my little hunt for deficient & abundant numbers and he asked if this thing you-all call excel could do that searching. i had to say, "i don't know, but i know someone who do." that be you. :hihi: so; can you do it mr winston wizzard? can ya huh?