Export data to Excel

I know you can export data retrieved from an SQL query to a CSV file but was wondering... can you export the data to a blank worksheet within an existing Excel file?

Thanks for your help.
drees727Asked:
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.

Wouter BoevinkMasterCommented:
If you have excel on the server you can create an instance of excel in asp and then use vba to put in the values you retrieve from the database.

Or you can use an asp component, like excelwriter,  to create an excel file.
0
ChessCommented:
You can add a sheet to the existing spreadsheet and insert data into the sheet using something like VB DLL.
0
drees727Author Commented:
Chess - do you have an example of what the VB DLL code would look like as part of an ASP file?

Thanks.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

ChessCommented:
Include "Microsoft Excel 8.0 Object Library" in your references.  The following code assumes a new excel file.  Modify it based on your needs.
----------------------------------------------------
Public Sub ImportFile(sValuationType As String, bTodaysDate As Boolean, sFileName As String, sPrimaryOrSecondary As String)

'sValuationType - can be CLIENT, COUNTERPARTY, BLOOMBERG, REUTERS
'bTodaysDate - True or False
'sFileName - Filename to be imported
'sPrimaryOrSecondary - The 2 possible values are "PR" or "SE"
On Error GoTo ImportFile_Error

    Dim ws As Excel.Worksheet
    Dim rw As Excel.Range
    Dim sSql As String, sSwapId As String, bError As Boolean
    Dim FirstRow As Boolean
    Dim objExcel As Object
    Dim fileNum As Long, iRet As Integer
    Dim sImportLogFileName As String
    Dim rs As New ADODB.Recordset

    sImportLogFileName = App.Path & "\ValuationImportErrors.log"
    fileNum = FreeFile
       
    'Checking here for mbCreatedFile as we may have already created an
    'error log file while processing the primary file.  It the variable
    'is true, don't delete the file, else if it exists, delete it.
    If Not mbCreatedFile Then
        If Dir(sImportLogFileName) <> "" Then
            Kill sImportLogFileName
        End If
    End If
   
    'Set it to true here, because if we are processing the secondary
    'file, this file will be deleted even if there are any errors in
    'the primary file import
    mbCreatedFile = True
    Open sImportLogFileName For Append As fileNum
   
    Set objExcel = CreateObject("Excel.Application")
    Set ws = objExcel.Workbooks.Open(sFileName).Sheets("Sheet1")
   
    FirstRow = True
    For Each rw In ws.rows
       If Not FirstRow Then
           If CStr(rw.Cells(, 1)) = "" Then
               'No more rows
               Exit For
           Else
                'First, get the swap id based on the client id in the import file
                If sValuationType = PRIMARY_TYPE Then
                    sSql = "sp_s_swap_id '" & Trim(CStr(rw.Cells(, 3))) & "'"
                Else 'If sValuationType = (COUNTERPARTY, BLOOMBERG,REUTERS)
                    sSql = "sp_s_swap_id_cp '" & Trim(CStr(rw.Cells(, 3))) & "'"
                End If
               
                rs.Open sSql, gDBConn, adOpenForwardOnly
                While Not rs.EOF
                    sSwapId = Trim(rs!Swap_Id)
                    rs.MoveNext
                Wend
                rs.Close
                Set rs = Nothing
               
                'If the swap id is not found, write it to a log file
                If sSwapId = "" Then
                    If Not bError Then
                        'Write the header
                        Print #fileNum, "The following Client/Counterparty Id's could not be found"
                        bError = True
                    End If
                    'Write the detail
                    Print #fileNum, rw.Cells(, 3)
               
                ElseIf bTodaysDate Then
                   
                    'Here, the Modified Swap Value = Original Swap
                    'Value because we are updating todays record
                    If CDate(rw.Cells(, 2)) <> CDate(txtProcessingDate.Text) Then
                        Set ws = Nothing
                        rw.Clear
                        Set rw = Nothing
                        Set objExcel = Nothing
                        Close #fileNum
                        MsgBox "The Processing Date you have selected does not match the date in the Import file.  Import File has been terminated.", vbOKOnly + vbInformation, APPNAME
                        Exit Sub
                    Else
                        'Insert/Update the record
                        sSql = "sp_ui_swap_values '" & sPrimaryOrSecondary & "', "
                        sSql = sSql & sSwapId & ", " & rw.Cells(, 4)
                        sSql = sSql & ", '" & Format(rw.Cells(, 2), "mmm" & " " & "dd" & ", " & "yyyy")
                        sSql = sSql & "', " & rw.Cells(, 4) & ", '" & Environ("username") & "'"
                    End If
                       
                Else
                    'Here, the Original Swap Value is zero (0) in the sql statement
                    'because we are not updating todays record
                    If CDate(rw.Cells(, 2)) <> CDate(txtProcessingDate.Text) Then
                        Set ws = Nothing
                        rw.Clear
                        Set rw = Nothing
                        Set objExcel = Nothing
                        Close #fileNum
                        MsgBox "The Processing Date you have selected does not match the date in the Import file.  Import File has been terminated.", vbOKOnly + vbInformation, APPNAME
                        Exit Sub
                    Else
                        sSql = "sp_ui_swap_values '" & sPrimaryOrSecondary & "', "
                        sSql = sSql & sSwapId & ", 0"
                        sSql = sSql & ", '" & Format(rw.Cells(, 2), "mmm" & " " & "dd" & ", " & "yyyy")
                        sSql = sSql & "', " & rw.Cells(, 4) & ", '" & Environ("username") & "'"
                    End If
                End If
               
               Call gDBConn.Execute(sSql)
               
           End If
       Else
           'Skip the column header row
           FirstRow = False
       End If
   Next

    Close #fileNum
   
    rw.Clear
    Set ws = Nothing
   
    'This does not prompt the user to save the worksheet
    objExcel.ActiveWorkbook.Saved = True
   
    objExcel.ActiveWorkbook.Close
    Set objExcel = Nothing
    Set rw = Nothing
   
    If bError Then
        'Display the log file in notepad after the process has completed
        iRet = Shell("notepad " & sImportLogFileName, vbMaximizedFocus)
    End If
   
   Exit Sub
   
ImportFile_Error:
    mbCreatedFile = False
    MsgBox "Error while importing " & sFileName & ". The error is " & Err.Description, vbOKOnly + vbCritical, APPNAME
   
End Sub
0
Wouter BoevinkMasterCommented:
Why create a visual basic dll?

In asp you can do the same

Set objExcel = Server.CreateObject("Excel.Application")
Set ws = objExcel.Workbooks.Open(sFileName).Sheets("Sheet1")

If Excel is installed on the server, same goes for the dll though.

etc.


0
drees727Author Commented:
The Excel file is going to be on a client machine.  The user will be downloading an Excel file from our Server to their machine.  I then want them to be able to click a button that will export data from a database on our server into their Excel file.
0
Wouter BoevinkMasterCommented:
Ah, then you use a client side vbscript to create an instance of Excel.

<script language="vbscript">
Set objExcel = CreateObject("Excel.Application")
Set ws = objExcel.Workbooks.Open(sFileName).Sheets("Sheet1")

etc.....

</script>
0

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
drees727Author Commented:
That answers my question.  Thanks!
0
ChessCommented:
drees727,
You need to close this by giving credit where it is due.
Chess
0
drees727Author Commented:
I did. I accepted Wboevink's answer.  It was the closest to what I needed.
0
sweetpillowCommented:
Hi Wboevink,
  I am sorry to interrupt into this close question. But I try your method and I get this error :

Microsoft VBScript runtime error '800a01ad'
ActiveX component can't create object: 'Excel.Application'

What should I do to solve it ?
And what code should I write to update the data in the Excel workbook ?

Sweetpillow.
0
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
ASP

From novice to tech pro — start learning today.