Solved

Export data to Excel

Posted on 2002-06-12
11
317 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:drees727
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 12

Expert Comment

by:Wouter Boevink
Comment Utility
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
 
LVL 1

Expert Comment

by:Chess
Comment Utility
You can add a sheet to the existing spreadsheet and insert data into the sheet using something like VB DLL.
0
 

Author Comment

by:drees727
Comment Utility
Chess - do you have an example of what the VB DLL code would look like as part of an ASP file?

Thanks.
0
 
LVL 1

Expert Comment

by:Chess
Comment Utility
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
 
LVL 12

Expert Comment

by:Wouter Boevink
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:drees727
Comment Utility
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
 
LVL 12

Accepted Solution

by:
Wouter Boevink earned 150 total points
Comment Utility
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
 

Author Comment

by:drees727
Comment Utility
That answers my question.  Thanks!
0
 
LVL 1

Expert Comment

by:Chess
Comment Utility
drees727,
You need to close this by giving credit where it is due.
Chess
0
 

Author Comment

by:drees727
Comment Utility
I did. I accepted Wboevink's answer.  It was the closest to what I needed.
0
 

Expert Comment

by:sweetpillow
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now