Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export SQL to Excel in VB (VB6)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export SQL to Excel in VB (VB6)

    Have you considered turning this updise down and fetching the data from Excel using data, get external data, new database query?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export SQL to Excel in VB (VB6)

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

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export SQL to Excel in VB (VB6)

    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.
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export SQL to Excel in VB (VB6)

    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?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Export SQL to Excel in VB (VB6)

    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).
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export SQL to Excel in VB (VB6)

    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.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export SQL to Excel in VB (VB6)

    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.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Export SQL to Excel in VB (VB6)

    This is air code so the usual disclaimers apply ( <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) but something like this should get you started:
    <pre> 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")
    </pre>

    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
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export SQL to Excel in VB (VB6)

    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.
    Wendell

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export SQL to Excel in VB (VB6)

    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.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export SQL to Excel in VB (VB6)

    It is not very difficult to automate.
    But since you seem to have tackled the problem...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •