We help IT Professionals succeed at work.

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.
Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

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.

HTH
  David

Author

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 Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

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.

Cheers
  David

Author

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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.