4 Replies - 1030 Views - Last Post: 25 September 2012 - 06:12 AM Rate Topic: -----

#1 leewheezy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 21-September 12

Creating Macros in MS Excel 2010

Posted 21 September 2012 - 07:41 AM

I'm trying to create a Macro that would look for a specific name and then run accordingly. Currently it works but only if the file is named exactly what it needs to be. Is there a wildcard I could use? Also, is there a way to write the code to where it looks for a name and then responds accordingly? I currently have 7 different macros created to run Pivot Tables / Pivot Charts based on the file name. If I could create one that would do the work of all 7 it would be awesome! Also, is there a way to have the code pop up a window for the user to select what pivot chart they would want? Kinda like the code looking for a name, this would give the user the ability to bypass that and choose for themselves. Thanks for any help!

Is This A Good Question/Topic? 0
  • +

Replies To: Creating Macros in MS Excel 2010

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Creating Macros in MS Excel 2010

Posted 21 September 2012 - 08:31 AM

I'll answer your first question first. DOS wildcards apply here. * will replace any string in its position, and ? will replace any character in its position. * cannot be followed by any character except ., because it represents a variable number of characters. So, for example, w* will return any filename beginning with w, while ?w* will return any filename whose second character is w. Finally, ?w*.htm? would return any file whose second character was w and whose extension was htm, html, or whatever.

To the rest I will just say yes, you can do all of that stuff. What have you tried so far?
Was This Post Helpful? 0
  • +
  • -

#3 leewheezy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 21-September 12

Re: Creating Macros in MS Excel 2010

Posted 21 September 2012 - 10:15 AM

Here's the current code:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Ports!A:CK", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion14


I placed the * that you recommended so the new code looks like this:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"*Port*!A:CK", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion14

When I run the macro I get this error:

run-time error '1004':
Cannot open PivotTable source file '*Port*':
Was This Post Helpful? 0
  • +
  • -

#4 thava  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 179
  • View blog
  • Posts: 1,598
  • Joined: 17-April 07

Re: Creating Macros in MS Excel 2010

Posted 21 September 2012 - 10:02 PM

obviously * isn't work for a sheet name or in Range address as a wildcard, so you may create a new macro, in that macro use the activesheet object to get the name of the current worksheet and using that name and a select statement or an if statement you can fulfill your requirement

This post has been edited by thava: 21 September 2012 - 10:04 PM

Was This Post Helpful? 0
  • +
  • -

#5 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Creating Macros in MS Excel 2010

Posted 25 September 2012 - 06:12 AM

Well, I could have told you THAT wouldn't work. :) I thought you were trying to use wildcards to get a list of filenames. So, suppose you take your datasource names and put them in an array. Then do a for loop, plugging each array value into your Create macro.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1