[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Copy data from SQL Server 2000 to Excel and format spreadsheet

Posted on 2008-01-25
5
Medium Priority
?
672 Views
Last Modified: 2013-11-30
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.
0
Comment
Question by:carrp
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 20746007
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
0
 

Author Comment

by:carrp
ID: 20748152
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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20748906
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

0
 

Author Comment

by:carrp
ID: 20758751
OK, so it is possible to use VBA from within a DTS package?
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1000 total points
ID: 20764071
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question