Copy data from SQL Server 2000 to Excel and format spreadsheet

I have some data stored in SQl Server 2000 that I'd like to export to an Excel spreadsheet. I would then like to format this data (using VBA if possible). This is a daily report that needs to run as a job, without user intervention. I'm interested in the general logistics of setting this up.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:

A small qeustion first - why format the data in VBA?

A DTS job should be able to do this for you.

In DTS create a connection to an existing spreadsheet There can be other sheets in the workbook that link to that data such as charts and so on. A data pump task will export data to the spreadsheet. You can use an email tsk to send that spreadsheet to who and where ever as needed.

carrpAuthor Commented:
I mentioned VBA because I've used it before to format Excel. It doesn't have to be VBA though. What would you suggest to be able to format from the DTS job? If you could provide code to do something basic (ex. make cell A1 bold), I  should be able to build on that to accomplish what I'm trying to do.
David ToddSenior DBACommented:

The query inside the datapump task in the DTS package can do much of the formatting, and its done on a column basis, instead of chasing data all over the spreadsheet on a cell by cell basis - thats all.

I was thinking of more basic formatting, such as the number of decimals and the format of the date.

So, formatting cell A1 bold you'll have to do in VBA.


carrpAuthor Commented:
OK, so it is possible to use VBA from within a DTS package?
Yes definitely. Do you have some VBA code? Basically you convert it to VBScript and paste it into a ActiveX script task ni the DTS.

But you will need Excel installed on whatever is running the script.

i.e. if you're running this as a job on the SQL Server then Excel needs to be installed on the SQL Server (generally not recommended)

Basically VBA will have a line of code like this:

SET oExcelXB = New Excel.Workbook

but in VBScript its more like this:

SET oExcelXB = CreateObject("New Excel.Workbook")

There are other differences, but its a pretty straightfoward conversion from VBA to VBScript.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.