October 14, 2005 at 9:15 am
I have a DTS package that basically dumps some data from a table to an Excel file. I use a template Excel file, which it copies each time, and then populates certain cells with the data using VBScript (it is a monthly report with lots of other things, and formatting, etc, so directly exporting it wouldn't work).
Now, I need to add more worksheets. Unfortunately, the number and names of these sheets are not static month to month, so I can't just create them in the template. What would be best is if I could somehow copy the first worksheet n times, with n being the number of records in the table I am drawing data from, and also rename them (on that tab at the bottom).
But, I am not very familiar with VBScript or the objects used (i.e. "Excel.App" etc), so I don't know how to make copies of a worksheet and then rename it. Googling (my general method of finding out how to do things) hasn't helped either.
Does anyone know how to do such a thing?
-- Stephen Cook
October 17, 2005 at 8:00 am
This was removed by the editor as SPAM
October 17, 2005 at 5:48 pm
I think you can make those things in Excel with Macro recorded. After that you can check and use the code of macro which can guide you how to copy and rename worksheet with vbscript.
October 18, 2005 at 9:33 am
Thanks, that's a good tip that I hadn't thought of...
Unfortunately I keep getting an error that says "A statement is expected" on the Copy line:
Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")
This syntax is identical to every example I have found on and off the Microsoft website, and the script generated by the Excel macro, but my SQL Server doesn't seem to like it.
Say, does anyone know of a good VBScript forum?
-- Stephen Cook
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply