Creating a pivot table with multiple sheets

[xlsaffer]

Hi
I am trying to create a pivot table using multiple sheets. I looking for some code that will replace the "consolidated ranges" in the Pivot Table Wizard. I am looking for code because I am writing a macro that will create a different number of worksheets in the Workfile, depending on which dataset I use. i.e File 1 may have 75 worksheets, whereas File 2 may have 120 worksheets. '

The ranges on each of the worksheets will be the same. Range("A2:Av48")

Any help or directions to other references will be gratefully received.

Thanks

 

[Fazza]

I wonder if the missingitemslimit that is the problem? It may not be supported by the external datasource. On which line of code does the error occur? If it is the missingitemslimit, then delete it. So instead just have

CodeNameOfWksWithPT.pivottables(1).pivotcache.refresh

 

[Fazza]

some great info (for the general thread topic, not the latest question) is at

http://www.excelkb.com/article.aspx?id=10008

 

[kiran654]

Re: pivot table with multiple sheets not picking up values

arSQL(i) = "SELECT * FROM [" & wks.Name & "$A3:AB65536] WHERE Account Is Not Null"

How do I modify this code if my excel column name has spaces and single quote.
e.g.,Associate name' ABC

 

[Fazza]

Hi kiran

Enclose the field name within marks `like this`. Such as below.

cheers, Fazza

arSQL(i) = "SELECT `field name with odd characters`, FieldNameSimple, `Associate name' ABC` FROM ...

 

[barasztamas]

Hi Fazza,

can I use this to manipulate an Excel sheet with SQL as well?
Is it the same objRS.Open method or do I need to use different VBA code?

Thanks
Tamas

 

[Fazza]

hi, Tamas

I'm unclear of the requirement. There are mutliple approaches with ADO and many tasks can be done. The links in earlier posts lead to lots of other information: perhaps they will answer your question? Or if you have a more specific question maybe I can address it.

regards, Fazza

 

[barasztamas]

Hi Fazza,

Thank you very much, the links are really helpful!

A more specific question, as you wrote:

I tried to make a Connection object, because I want to run many queries from the same workbook. I guessed this must be faster than opening the workbook again for each query...

I can't create a recordset from the existing connection.
On the last line of this code I get a 3001 error:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Public Function Test()
  Dim oConnection As Object
  Dim oRecordSet As Object
 
  Set oConnection = CreateObject("ADODB.Connection")
 
  Set oRecordSet = CreateObject("ADODB.Recordset")
  oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\barasz\Desktop\Book2.xls; Extended Properties=""Excel 8.0;"""
 
  Set oRecordSet = oConnection.openrecordset("select count (*) from [a$] where [c] = 14")

Or alternatively I also tried this instead of the last line, this gives a different error:
-2147217904 (80040e10), No value given for one or more required parameters.

  oRecordSet.Open "select count (*) from [a$] where [c] = 14", oConnection

You know what I did wrong?
Of course sheet a has a c column, the select expression works well, when I use it the way you wrote...

Thank you very much for your help!
Tamas

 

[Fazza]

Hi, Tamas.

For running many queries, yes, a connection object is preferred. (As one might untuitively expect.) And then use multiple .execute

Suggest you search old posts for examples. When a connection object is created, the recordset object is then also available.

Some old threads (not all exactly relevant) that might help,
http://www.mrexcel.com/forum/showthread.php?t=334833
http://www.mrexcel.com/forum/showthread.php?t=294231
http://www.mrexcel.com/forum/showthread.php?t=300295
http://www.mrexcel.com/forum/showthread.php?t=305967
http://www.mrexcel.com/forum/showthread.php?t=305967
http://www.mrexcel.com/forum/showthread.php?t=314921
http://www.mrexcel.com/forum/search.php?searchid=2065955
http://www.mrexcel.com/forum/showthread.php?t=340914
http://www.mrexcel.com/forum/showthread.php?t=341670

With the last SQL, I assume you have a field named c. If instead there are no headers and you are referring to column "C", try F3 instead.

HTH, F

 

[MartinL]

It isn't clear if the range you gave includes the headers or not. I assume as you have started with row 2, that the headers are in row 1. If not, please advise it is a simple change.

Sorry to raise this old chestnut!

I have a very similar issue here

My Headers are in Row 4 (B4:F4) on every tab

and are labeled: Program, Availability, Original Order, Final Order, Delivered
Trying to follow this thread as best I can....

Martin

 

[Fazza]

Hi, Martin. Please refer to post #23 earlier in the thread. Regards, F