Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
murrayb3024
Yak Posting Veteran
79 Posts |
 Posted - 2005-05-12 : 15:06:07
|
| I have a rather large DTS package I have built that imports and manipulates data from an Excel document. The user places 7 documents in a directory and then the DTS package is started and is running smoothly. The only problem I am having is that the user currently has to go in and change the names on all the worksheets to one standard one for the Data Pump to recoginze it. The reason is that the 7 documents are one for each day of the week. So we change Monday, Tuesday, etc all to TIMECARDS so the Data Pump will work. Is there a script I can write to change the names of just the worksheets before the datapump, or is there something I can change in the datapump so it doesn't stop when it can't find TIMECARDS? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-05-13 : 06:25:31
|
| You'll need to invoke an OLE object to interact with the Excel sheet.Try putting something together to do it as a seperate task in the likes of VB/VBA.Maybe this can be scheduled to run prior to your DTS job.Remember....use suitable tools for each step...don't get sucked into the "I MUST USE TOOL X for ALL the job, because I have to use it for part of the job" syndrome. |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-05-13 : 08:12:09
|
| the excel command is Sheets("MySheet").Name = "MySheetRenamed" or if you don't know the name but you know the order I think you can use something like Sheets(1).Name = "MySheetRenamed"steveA sarcasm detector, what a great idea. |
|
|
|
|
|
|
Script to change worksheet names?