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