Posts by pete_bristol

    Re: Update Recordsets From Different Connections


    Hi Craig,


    Thank you. I've pretty much got that I think but what I'm having difficulty with is trying to update one recordset with the other. I will have a bash at home tonight and post any specific issues.


    Thanks again


    Pete

    Re: Update Recordsets From Different Connections


    Craig,


    Thank you for your reply.


    Are you able to expand on this please or give an example?


    I have googled for "Excel parrallel processing" but to be honest I couldn't pick up anything simple - most of it went on about processing on multiple computers!


    Thanks for any help you can give.


    Regards,


    Pete

    Hello,


    Please can you help?


    I have tried to figure this out myself but cant quite get it to work and I am unsure whether thats because "it cant be done" or I am simply getting mixed up with the syntax.


    Basically, is it possible (within the same procedure) to have two connections and two recordsets and update on from the other i.e write data back and forth between the two databases or does a recordset only work with its own connection?


    I appreciate any thoughts you may have on this.


    Regards,


    Pete

    Re: Combination Graph Type


    Thanks Andy for incredibly prompt reply.


    I had hoped that I would draw your attention as I had looked at your website and thought if it can be done, Andy can do it!


    I had tried beforehand an X-Y plus a bar and whilst I get close, its not quite what I am looking for. Basically its just a normal column chart with a line superimposed. The "difficulty" is that the line has got to commence in the same X Axis position as the first bar - if you see what I mean? i.e if there are 3 bars with in block, the line must plot a point (ideally in the centre) of the correspoding bar.


    Any help you can provide would be very appreciated.


    Regards,


    Pete

    Re: Pivot Table Calculated Item Removal


    Dave,


    Thats exactly it.


    Essentially for the second PT to be the same as (length wise) the first!


    I've been experimenting lots since my last post and it seems that the only way is to hide the row outside of the Pivot table "functionality" i.e by using autofilter or using VBA to hide the entire row rather than the methods / properties etc of the Pivot table object.


    Regards,


    Pete

    Re: Pivot Table Calculated Item Removal


    Hi Brandtrock,


    Thanks - our posts seem to haved crossed and suggest the same thing.


    Yes, I had already managed to do that. In reality I would have to re-structure my data file rather than incoporate the changes via formula as I have 10's of thousands of records to deal with.


    I will let you know how I get on.


    Regards,


    Pete

    Re: Pivot Table Calculated Item Removal


    Hi Brandtrock,


    Thanks.


    Yes, you are quite right in your understanding of my problem.


    In my "proper" project this is what I have effectively done anyway as I have got the autofilter to run after the PT refresh event. However, i had hoped there was a "better way!".


    I could (and have also solved my problem) by re-arranging my data so that sales, COS are "Fields" of my data so that I can use a calculated field to arrive at GP. This works significantly quicker and does away with the problem of redundant data.


    I am sure I have read somewhere that it is effectively a "sin" to include data items as fields - the classic example being months of the year. It is much better to have the data row-wise with 1 field for "month" and 12 records rather than 1 record with 12 fields?


    Perhaps the answer is "it depends". I would welcome views on this?


    I will carry on experimenting as there are all sorts of stuff in the pivot table object model that might enable me to do what i am seeking.


    Thanks again,


    Pete

    Re: Pivot Table Calculated Item Removal


    Dave,


    Thanks but displaying a zero isn't the problem - its far more complex than that. If you call up my spreadsheet I am sure you will understand my query.


    The two pivot tables derive from the same source data yet one is about three times longer than the other BECAUSE i have included a calculated item. Excel (quite rightly I guess) has included a the calculation for every possible combination of row / column data. All i wanted to do was supress the redunant data.


    I think i will just have to put it in the "too difficult to do pile" for now, although I am surprised that not one person has been able to offer any help. Its never happened to me on this fantastic site before (and I am being sincere in that comment).


    I will post back a solution when I find one.


    Thanks again


    Pete

    Re: Pivot Table Calculated Item Removal


    Hi Dave,


    Thanks for taking the time to have a look at my thread.


    However, I had hoped that the attached spreadsheet would show my problem precisely.


    If you call it up you will see that the second pivot table (the one on the right) is about three times longer than the first because with the addition of the "calculated item" it has included a whole load of "illogical" permutations that are all zero.


    I feel sure that if you call it up you will see what i mean?


    Thanks again,


    Regards,


    Pete

    Pivot Table Calculated Item Removal


    Wow, this must be even more tricky than I first thought not to get a single reply in two days from you experts.


    I like to think of myself as a pretty "advanced" user but i have to admit at the moment it has me stumped (and believe me, i have been trying!).


    I will continue my efforts and will post back a solution when(or if) I find one for the benefit of others.


    Thanks again anyway


    pete

    Dear All,


    Please can somebody help with a problem that I have spent the last two days trying to solve.


    Scouring the net, I have managed to find lots of "very similar" questions but unfortunately none offered any answers!


    I have cut down my problem to its simplest form - in the real world my spreadsheet is significantly more complex but the attached spreadsheet demonstrates it nicely!


    The data is straightforward. PT1 shows the data without a calculated item whilst PT2 is the same PT but with the addition of a calculated item for GP (i.e Sales - COS). Unfortunately adding this has resulting in the PT growing and adding "ilogical" combinations of data that didn't exist in the source data.


    In my current project I have "cheated" by using a helper column and filtering on this using the Worksheet_PivotTableUpdate event to hide the unwanted rows but surely there must be a way of removing them "properly".


    Another problem, and I guess this is just the way it is, is that my PT takes ages to calculate presumably becuase it is calculating this formula for every combination of values?


    Please can somebody assist / advise what code I need to attached to the button on my spreadsheet to make the items disappear - preferably not just to "delete the rows" but make it disappear from the PT?


    Many thanks in anticipation,



    regards,



    pete

    Re: Ado Versus Odbc Sql Syntax


    gollem,


    I just checked, and was hoping that was the answer, but unfortunately I *had* included "Extended Properties=Excel 8.0;" so i guess thats not the problem?


    My immediate worry has gone away now becuase I use the other provider but i would still like to know "why" if possible.


    Thanks for you time,


    Regards,


    Pete

    Re: Ado Versus Odbc Sql Syntax


    Oh joy of joys - after a day of pulling my hair out, I (accidently) stumbled on the solution!


    I'm not entirely sure "why" (and that does bother me, but just right now I'm more pleased that I got it to work!) but the solution was to change the "provider" in the connection string from "Provider=Microsoft.Jet.OLEDB.4.0" to "Provider=MSDASQL.1" and everything is fine.


    Hope this may help others as well.


    Regards,


    Pete

    Dear All,


    Please can you help with another issue which is testing my sanity!


    My excel spreadsheet contains the following code which uses ADO to query a closed workbook - until I add the last line, all works perfectly:-



    However, when I add the last line for the "where" clause it bombs out with an "automation error" - I have fiddled and fiddled with it without joy all day (on my day off!) and am just lost for what to do next.


    When I cut the debug.print sSQL output into a Querytable on the same sheet it works precisely as expected.


    For some reason, ADO (i guess) doesn't like the build up of the sSQL string that way although it doesn't choke on any of the other "where" clauses.


    Please someone tell me the errors of my ways!


    I look forward to your replies.



    Regards,


    Pete

    Re: Sql Queries In Same Workbook


    Hi Will,


    I think you are right - the blasted thing is back again after working like a dream all morning!!


    I give up - from now on, queries in one workbook, data in a (closed) other!


    Thanks again,


    Pete

    Re: Sql Queries In Same Workbook


    zimitry,


    Thanks for advice but unfortunately this spreadsheet could potentially be accessed by lots of people.


    However, my problem has moved on a bit since last night and am now even more confused!


    I have literally spend hours and hours fiddling with connection properties, trying permutations of ODBC, ADO etc, surfing the net for answers (and finding quite a few posts suggesting that self querying a workbook is a BAD idea) and was just about to give up when.....


    ..I tried it all again this morning, same machine, same workbooks and all works perfectly!


    I just do not know what to do next. If I incorporate self queries into my workbook (that work for me, today) will they work for others tomorrow?


    Any advice anybody can give will be greatly appreciated.


    Regards,



    Pete