Pyro's Excel Goodies

[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 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]

Originally Posted by alexander

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]

Originally Posted by TheBigDog

...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...

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

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]

Originally Posted by Pyrotex

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

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:\Projects\Settings.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