I have an excel file that is used as a data file for a number of PivotTables. The Excel Data file is created from a Crystal Reports export and is re-created frequently. Is there a way to automatically create a Dynamic Named Range each time the Excel Data file is created?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Pivottable file - Dynamic Named Range
- Thread starter gc6294
- Start date
If the data is always dumped to the same excel file, you can set up a dynamic named range in there. If it is always to a new file, then it depends if you can automate the data export to Excel through VBA
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
- Thread starter
- #3
The excel file that the data gets dumped to will likely always have the same name, but gets re-created as part of the export process from Crystal, so essentially it's a new file. Any assistance anyone can give with doing this thru VBA would be appreciated - am a novice with VBA...
Thanks
Thanks
Please post the question in the VBA forum (Forum707)
This will depend heavily on whether Crystal exposes itself to VBA. If it does then you can probably control the export from Crystal and define the named range. If not, you may have to query the data directly into excel using the same SQL that the Crystal repoort uses...
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
This will depend heavily on whether Crystal exposes itself to VBA. If it does then you can probably control the export from Crystal and define the named range. If not, you may have to query the data directly into excel using the same SQL that the Crystal repoort uses...
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
- Thread starter
- #5
In which case, a VBA route would seem the best option if Crystal will allow you to do that...
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
- Status