Format Excel from DTS when Excel is not installed on the SQL Server?

Question

Format Excel from DTS when Excel is not installed on the SQL Server?

Jeffrey Kalmar

SSC-Addicted

Points: 438
More actions
December 14, 2005 at 7:56 am

#67116

I have a DTS package that creates an Excel file every 2 weeks. A new file is created each time rather than overwriting the old one. It works fine, BUT...
The client has now requested spcific formatting (fonts, fill colors etc.) in the Excel files. The problem is, Excel is not installed on the database server. Is there any way to get to the Excel file and format it from the DTS package? This really needs to be automated, not done manually.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

iBuildDW Mr or Mrs. 500 Points: 537 More actions · Answer 1

AFAIK, you would need Excel installed. I have a similar required process (monthly) I currently run the DTS package, grab the file to my local system (where I have Excel installed) then run a VBS script file to do all the formating using the WScript.CreateObject("Excel.Application") object. I'd love to hear if there is another way.

Cheers..

Jeffrey Kalmar SSC-Addicted Points: 438 More actions · Answer 2

So you're running your VBScript manually on your local machine, or do you have an automated process that does it?

Excel is installed on the shared drive where the Excel files are being saved, I'm hoping there's a way to tell that server to do the Excel formatting...

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 3

You will need to write an activex task to do so, but it is possible.

You don't need to install excel as far as I know, but you will have to regsister the excel libraries on the server in order to do this.

That's about as much as I can help.

------------------------------
The Users are always right - when I'm not wrong!

iBuildDW Mr or Mrs. 500 Points: 537 More actions · Answer 4

Mine is manual now, but could easily be added to Schduled Tasks if you knew when the file would be available.

SQLServer creates the file as part of a sheduled job

The machine that has Excel, can have a job to check for and format the file.

Cheers..

Vadim77 Valued Member Points: 61 More actions · Answer 5

Here is what we do:

We created Excel file with all the formatting they want. That workbook has hidden worksheets that have no formatting. DTS overrides the hidden worksheets on every run (drop and add a table to Excel). The formulas within workbook, use hidden worksheets as datasources for visible and formated worksheets. At the end of the DTS package, the file is copied with date-specific filename and link to that file is distributed through email (also in DTS).

Hope that helps

Vadim.

Jeffrey Kalmar SSC-Addicted Points: 438 More actions · Answer 6

OMG, I never thought of just copying a formatted workbook with a new name! I'll try that - thanks!

ptap SSC Rookie Points: 28 More actions · Answer 7

Hi!

Or You can use OpenOffice... It's free... :-))

The automation system of it is a bit different to Excel's... But easely learnable... :-)))))

Jeffrey Kalmar SSC-Addicted Points: 438 More actions · Answer 8

Thanks for the suggestion, but Excel is mandated by company policy.

ptap SSC Rookie Points: 28 More actions · Answer 9

Hi!

You are welcome...

Ah I see... They could mandate a copy to your database server machine...

jvillarg Grasshopper Points: 12 More actions · Answer 10

Hi Vadim:

Please I need your help, could you send me an excel file as an example of what you mentioned about using hidden worksheets as input to the formatted excel file ?

My email is: jvillarg@viabcp.com

Thanks for your help.

jvillarg Grasshopper Points: 12 More actions · Answer 11

Hi Vadim:

Please I need your help, could you send me an excel file as an example of what you mentioned about using hidden worksheets as input to the formatted excel file ?

My email is: jvillarg@viabcp.com

Thanks for your help.