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 |
|
beh
Starting Member
2 Posts |
 Posted - 2007-11-28 : 12:21:46
|
Hi, I'm using the following script to run 2 macros, one to delete all worksheets (except one) and then one to recreate them. **********************************************************************' Visual Basic ActiveX Script'************************************************************************Function Main() 'References to Excel dim xl_app dim xl_Spreadsheet SET xl_app = CREATEOBJECT("Excel.Application") SET xl_spreadsheet = xl_app.Workbooks.Open _ (DTSGlobalVariables("gv_str_SpreadsheetName").Value) 'Run the macro xl_app.Run DTSGlobalVariables("gv_str_Macro_Delete").Value xl_app.Run DTSGlobalVariables("gv_str_Macro_Create").Value 'Very important to clean up. 'if you make changes to a spreadsheet then XL will ask you if you want to save 'it on closing so let's pre-empt it and save the workbook then close xl_spreadsheet.Save xl_spreadsheet.Close xl_app.Quit set xl_app = Nothing Main = DTSTaskExecResult_SuccessEnd Function The above works fine. However, I also need to: 1) - Create a copy of my template (which contains buy of my macros) - This is the file with the address held in -->gv_str_SpreadsheetName2) - Rename the old file with the date3) - Rename the copy to the original file name. Can you please help me !!Regards, Paul |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-28 : 15:27:12
|
| you can accomplish those using the filesystemobject, maybe file too...http://msdn2.microsoft.com/en-us/library/6tkce7xa.aspx |
 |
|
|
beh
Starting Member
2 Posts |
Posted - 2007-11-29 : 04:24:30
|
| thanks for your reply. I'll have a go with the filesystem object :) |
|
|
|
|
|
|
File IO from DTS ActiveX - VB