Sub or Function not Defined: Generating Pivot tables using VBA Code

**AnthonyWB** · 04-30-2010, 10:59 AM

Hi everyone. I am trying to create a pivot table using VBA code. I am reciveing an error, in the code though, but atually cannot pinpoint the problem. Any help would be greatly appreciated.

Please Login or Register  to view this content.

**AnthonyWB** · 04-30-2010, 11:29 AM

The error is an Invalid Procedure call or argument.

**AnthonyWB** · 04-30-2010, 01:52 PM

I have modified the code, and fixed some additional errors. However I ham still recieving and Invalid Procedure or Call Argument:

Please Login or Register  to view this content.

**AnthonyWB** · 04-30-2010, 02:30 PM

I made some additional correction. The code below is where the error occurs:

Please Login or Register  to view this content.

I desperately needs some help here.

**romperstomper** · 04-30-2010, 06:16 PM

Your tabledestination doesn't include a sheet name.

**AnthonyWB** · 05-03-2010, 11:14 AM

How would I inlcude the sheet name into the code?

**romperstomper** · 05-03-2010, 03:30 PM

I'd use a range object:

Please Login or Register  to view this content.

**AnthonyWB** · 05-03-2010, 04:00 PM

I am totally confused, you mean like this ?

Please Login or Register  to view this content.

**romperstomper** · 05-03-2010, 05:31 PM

The last line is incorrect:

Please Login or Register  to view this content.

**AnthonyWB** · 05-04-2010, 09:56 AM

Thank you for your help. I am still recieving an error :Compile Error expresseion expected, variable not defined. According to the Immedaite window the problem is here:

Please Login or Register  to view this content.

I am assuming everything else is good?

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 10:03 AM

You appear to be trying to create a cache in one workbook using a range in another workbook, in which case you need to have the path and names in this format:
'path here[workbook name here]sheet'!range_Address

i.e. you need a leading apostrophe, then the path, then a [ then the workbook name then ] then the sheet name then another apostrophe and then an exclamation mark followed by the range address. You appear to be missing most of the punctuation.
There's nothing wrong with the Version bit.

**AnthonyWB** · 05-04-2010, 10:08 AM

Uh ?

Is there a better approach? The data is in one notebook, but I want the table in another.

**AnthonyWB** · 05-04-2010, 10:16 AM

The path is variable and will depend on fPath and fDate, which are global variables:

fPath = "L\"

and fDate is an input from the user.

**romperstomper** · 05-04-2010, 10:19 AM

I didn't say you couldn't. I just said your file paths are incomplete. For example, if your source is Sheet1!A1:C100 in Book1.xls in C:\Test, then your SourceData argument needs to be:

Please Login or Register  to view this content.

but you are currently using the equivalent of:

Please Login or Register  to view this content.

so you are missing all the punctuation. Without knowing exactly what your paths are, you need something like:

Please Login or Register  to view this content.

**AnthonyWB** · 05-04-2010, 10:24 AM

Thank you, romperstomper, but according to the immediate window

Please Login or Register  to view this content.

has an invalid character. It should work though, if corrected?

**romperstomper** · 05-04-2010, 10:26 AM

I've just noticed you have a 1 not an l in the enumeration value - it needs to be:

Please Login or Register  to view this content.

i.e. XL at the start not X1

**AnthonyWB** · 05-04-2010, 11:24 AM

I corrected a few more errors. The underscore before versions was erroring out in the code. That has been corrected but I am still getting a compile error variable not defined.

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 11:31 AM

Do you have Option Explicit at the top of your module? If you do, you haven't declared PT or PC in your code as far as I can see.

**AnthonyWB** · 05-04-2010, 11:36 AM

I made the correction now it says that a Named argument is not found?

Please Login or Register  to view this content.

**AnthonyWB** · 05-04-2010, 11:39 AM

Thanks for all of your help on this ! Yes they have been declared.

Please Login or Register  to view this content.

This is a submacro of many others. Option Explicit was declared at the top.

**romperstomper** · 05-04-2010, 11:46 AM

Oh, I just noticed you are using Add not Create when adding the cache - Add does not have the Version argument so you'll need to remove it.

**AnthonyWB** · 05-04-2010, 11:55 AM

Remove it and replace it with Create, or just remove it as in:

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 12:03 PM

Remove the Version argument:

Please Login or Register  to view this content.

**AnthonyWB** · 05-04-2010, 12:05 PM

ok likewise with pt?

**romperstomper** · 05-04-2010, 12:08 PM

Nope, that should still be valid.

**AnthonyWB** · 05-04-2010, 12:11 PM

well pc is now error free, but all of pt errors.

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 12:13 PM

What error?

**AnthonyWB** · 05-04-2010, 12:15 PM

Subscript out of range, run time error 9

**romperstomper** · 05-04-2010, 12:19 PM

Then the workbook or worksheet name is incorrect (or both).

**AnthonyWB** · 05-04-2010, 12:23 PM

Yep it's incorrect, the path is:

Please Login or Register  to view this content.

and the file name is

Please Login or Register  to view this content.

with sheet

name "Borrowings_Portfolio_Bonds"

**romperstomper** · 05-04-2010, 12:25 PM

Is the sheet name Borrowing_Portfolio_Bonds or Borrowings_Portfolio_Bonds?
One or other name is wrong and only you can figure out which.

**AnthonyWB** · 05-04-2010, 12:27 PM

Funny - there is no s at the end Borrowing_Portfolio_Bonds.

**romperstomper** · 05-04-2010, 12:45 PM

Again, either the workbook name is wrong (assuming the workbook is open) or the sheet name is wrong.

**AnthonyWB** · 05-04-2010, 12:50 PM

The workbook is not open. Are you saying I need to have it open? Thanks for all your help !

**romperstomper** · 05-04-2010, 12:55 PM

Yes - you can't create a pivot table in a closed workbook!

**AnthonyWB** · 05-04-2010, 02:38 PM

I have been working on this for some time, and I am still recieving errors. I now have a sub or function not defined error at the CreatePivotTable line, please see the code below:

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 02:44 PM

CreatePivotTable is a method of the PivotCache object, which you have now removed for some reason:

Please Login or Register  to view this content.

**AnthonyWB** · 05-04-2010, 03:06 PM

I am almost there: I now have an invalid procedure call or argument:

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 03:10 PM

You don't need the path in the TableDestination since the workbook is open.

**AnthonyWB** · 05-04-2010, 03:21 PM

Man this is tough, so are you saying:

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 03:26 PM

Originally Posted by AnthonyWB

Man this is tough

To be honest, I'm not sure why you keep completely altering your code - it makes it tricky to work out what got fixed and what got broken in between.

For the destination it's easier to use a range object, IMO:

Please Login or Register  to view this content.

**AnthonyWB** · 05-04-2010, 03:38 PM

Creating the pivot is much harder than expected: I now have
an error showing the a Reference is not valid. I have doubled checked the file path and file name, and sheet name and all are correct.

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 03:41 PM

What is the exact error? Does it help if you change A1 to A3?

**AnthonyWB** · 05-04-2010, 03:52 PM

I am still getting an error. When I goto Debug run to curser, I am told that "Methid CreatePivotTable" of object "PivotCache' failed.

When I step into the the error I am told that the "a reference is invalid"

**romperstomper** · 05-04-2010, 04:03 PM

I think I need to see the workbooks, as we seem to be going round in circles. Can you strip them down a bit, censor any confidential info, and post (with code) please?

**AnthonyWB** · 05-04-2010, 04:15 PM

Sure they are two workbooks.

**AnthonyWB** · 05-04-2010, 04:24 PM

I am blocked from uploading files.

**romperstomper** · 05-04-2010, 04:36 PM

Who by? Your company, or the site?
Can you email them?

**AnthonyWB** · 05-04-2010, 04:42 PM

I am told that a security token is broken, I am assuming it's my company. Anyways the post is getting out of hand. I'll post the entire code again, and see if there are other errors. It could have been smarter to have recorded a macro, and go from there?

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 05:07 PM

Since you have workbook variables, why not use them:

Please Login or Register  to view this content.

And yes, it's usually easier to start with a recording and then edit it!

**AnthonyWB** · 05-04-2010, 05:16 PM

I recorded a macro and it works, however it fails to incorporate the global variables fpath and fDate which are crucial to the automation.

Please Login or Register  to view this content.

**romperstomper** · 05-04-2010, 06:37 PM

Something like:

Please Login or Register  to view this content.

**AnthonyWB** · 05-05-2010, 09:58 AM

I think we almost got it, we still have an error though: an invalid procedure call or argument.

Please Login or Register  to view this content.

**romperstomper** · 05-05-2010, 10:04 AM

Oops - missing an apostrophe:

Please Login or Register  to view this content.

**AnthonyWB** · 05-05-2010, 10:21 AM

It's almost done, and I appreciate your help, tremendously. There is an error "Cannot Open Pivot Table Source File "L:\30-apr-2010105'"

Please Login or Register  to view this content.

the correct path should be "L\30-apr-2010_157\105_Reports\105.xlsm or as it has already been declared,

Please Login or Register  to view this content.

**romperstomper** · 05-05-2010, 10:27 AM

I based the code on your recorded macro, which uses:

Please Login or Register  to view this content.

Since that is (apparently) a sheet in the same file, you should not need a path.

**AnthonyWB** · 05-05-2010, 10:47 AM

The correct path and file name for the source is

sPath1 = fPath & fDate & "_157\105_Reports\"
Const sFileInp1 As String = "105.xlsm"

where we have set Set wb1 = Workbooks.Open(sPath1 & sFileInp1). We then want the range to be R1C1:R11500C48" on a worksheet called 105.

**romperstomper** · 05-05-2010, 10:54 AM

That is not what your recorded code did. If you just need to use a sheet called 105, then use:

Please Login or Register  to view this content.

**AnthonyWB** · 05-05-2010, 11:07 AM

We Got It !

Sub or Function not Defined: Generating Pivot tables using VBA Code

LinkBack

Thread Tools

Rate This Thread

Display

Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Generate Pivot tables using VBA Code

Re: Generate Pivot tables using VBA Code

Re: Generate Pivot tables using VBA Code

Re: Generate Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Variable Not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Re: Sub or Function not Defined: Generating Pivot tables using VBA Code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions