Export SQL to Excel in VB (VB6)

Topic

New Reply

I need to export data (a count report, so not a large number of rows) from SQL to Excel, which i’d usually use DTS for, but I need to be able to specify the filename and worksheet. I am looping through a table and running a report for each row in the table, so the first instance will create the spreadsheet and a named worksheet, and all further instances would add a new named worksheet.

As I wouldnt know where to start with passing parameters to DTS I thought i’d give VB a go, i.e. create a program and call it from SQL (which I do know how to do). However, exporting from SQL to Excel is proving more difficult than I thought it would.

Any help would be much appreciated.

Regards,
Phil

Reply | Quote

Replies

Have you considered turning this updise down and fetching the data from Excel using data, get external data, new database query?

Reply | Quote

I should have mentioned that this will be an automated process. I’ve never automated anything in Excel.

Reply | Quote

It is not very difficult to automate.
But since you seem to have tackled the problem…

Reply | Quote

Another option you might consider is using Access, either with Automation, or by using the TransferSpreadsheet command. You would probably want to have an ODBC link to the SQL table, but that’s about as complicated as it would get.

Reply | Quote

Would that be done by calling the Access database to load from SQL then having the export function kicked off in the autoexec?
Also, how would the Stored procedure know it has finished and to continue with the loop – or would it be a case of converting the whole stored procedure to VBA in Access?

Reply | Quote

Sorry for the delay in responding – I somehow missed your post. It looks like Rory has given you some Automation code to kick off Excel, so you may well have a solution in hand. What I was proposing was to connect to either a table or a view using ODBC from an Access database, and then simply use the TransferSpreadsheet method to blow the data from that “linked table” out to Excel. That could be done as an Autoexec macro from Access, but if you want to kick it off directly from SQL as a part of a stored procedure, things get rather more complicated. In that case you are probably looking at creating a UDF in SQL – but you may need to do the same thing with a VB app. Let us know how you get on.

Reply | Quote

Its temporarily on hold at the moment, but I did make progress before that happened:

I managed to create a vb project using Rorys code, that loops through each report and exports it perfectly. I even managed to find some additional commands to set font size, bold, text wrap etc for specified rows. It worked great……….. until I tried putting it in SQL.

Running .exe’s from SQL is a nightmare. From what I can tell: If something happens in the vb app, because SQL runs it silently, no errors are displayed – so if it errors SQL just waits for the program to end! But it won’t end as the error is hidden! I have experienced this before and couldnt fix it then, so I didn’t even try this time – I rebuilt the export using only queries and it was a nightmare, but is almost finished now.

It hasn’t come out exactly like the customer wanted, unlike the vb app but it will have to do. I personally would have been happy to make it stand alone vb and run it from scheduled tasks on a server instead of through SQL jobs – but it isn’t me who’ll support it if it goes wrong, so I have to abbide by the support departments methods.

Thanks for all your help though and thanks for the code Rory. When I get a sec i’ll post my final code.

Reply | Quote

Phil,
Just to clarify, do you want a new worksheet for each record in the table or will you be running this against multiple tables and want a new sheet for each table’s output (I’m guessing the former?)
The code isn’t that complicated but if you can provide a few more specifics it would help – e.g. where does the name of the sheet come from, where do you want the data to go (all on one row or in specific cells).

Reply | Quote

Difficult to explain this but here goes:

I have a standard output along the following lines:

Code | Description | Unique Responders | Requests Allowed | Requests Rejected | Total requests | Qty of Packs (A) | Qty of Packs ® | Qty of Packs despatched

These fields have counts against them that come from a table of profiles (stuff captured by the data entry team).
The report is grouped by the profile statement, therefore, for each statement a report is generated.

My Stored procedure loops through this table of statements and puts the statement ID in the criteria of the views that return the data (keeping the view names the same).
The filename.xls is a combination of a title and date i.e. EstabReport150305.xls
The sheet name comes from the statement description.
Both of these are currently retrieved from the statements table and help in variables in the stored procedure, so I have them ready for passing to something.

My end result should be a single spreadsheet, with a worksheet for each statement – and a count of all of the pofile data for that statement on each page.

I have created the bit that loops and changes the views and I have the 2 paramaters to pass to the export – but I can’t seem to export it dynamically.

This will run on a daily basis.
Any bits i havent been clear on give me a shout and i’ll happily add more.

Almost forgot, my view holds field headers as a row and totals as a row so there isnt any formatting issues, its simply taking a small grid of data and getting it in excel.

Reply | Quote

Managed to dig out a sample.
Ignor the formatting (bold, spacing etc) as it isnt needed.
This is an example with 1 worksheet, add to it 20 more worksheets with a different sheet name, same columns (possibly different number of rows) and different data and you’re there.

The headers, totals and data are all in data form in my view so I dont need to export field names.

Reply | Quote

This is air code so the usual disclaimers apply ( ) but something like this should get you started:

    Dim appXL As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    Set appXL = New Excel.Application
    Set wbk = appXL.Workbooks.Add(xlWBATWorksheet)
    'assume you already have a recordset rst based on the table
    Do Until rst.EOF
        'code to populate new recordset rst2 with required data from each view goes here
        Set wks = wbk.Sheets.Add(After:=wbk.Sheets.Count)
        With wks
            .Name = rst2!Description
            .Cells(1, 1).CopyFromRecordset rst2
        End With
        rst2.Close
        rst.MoveNext
    Loop
    'assumes you already have a path specified and stored in strPath
    wbk.SaveAs strPath & strTitle & Format(Date, "ddmmyy")

This will leave you with a blank first sheet which you can either delete at the end or use for any summary data or a description of what the file is.
HTH

Reply | Quote