Exporting data to a pre-existing excel file on client machine from vb.net

Hi all,

I need to copy a set of data which include some parameters and table name to a pre-existing excel file on a client machine...

  what I am trying to achive is that we currently have excel file in a particular path that takes input like tablename and couple of parameters ..this excel file have VBA code and macro that read the content of the table supplied as a parameter from the sqlserver and generate reports... currently this is done using a desktop application which passes the required parameter ..

I want to pass the same set of parameter from a web page , so that we can use this existing excel  template for generating report ..while converting the desktop application to web based interface.

I will appreciate any help or idea on this ...

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.

Robberbaron (robr)Commented:
there are ODBC connection strings so you can treat an Excel file as a database.
this is VB6 code.

    'Open the ADO connection to the Excel workbook
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & svname & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""

    ExcelSetRange oConn, "proj_num", "tes123"
    ExcelSetRange oConn, "proj_name", "testing data"


Sub ExcelSetRange(oConn As Connection, RangeName As String, RngData As Variant)
    Dim sqlQ As String, vData As Variant
    Dim oRS As ADODB.Recordset
    Select Case VarType(RngData)
        Case vbNull
            vData = ""
        Case vbInteger, vbLong, vbSingle, vbDouble
            vData = RngData
        Case vbBoolean
            If RngData = True Then
                vData = "TRUE"
                vData = "FALSE"
            End If
        Case Else
            vData = RngData
    End Select
    'sqlQ = "Insert into " & RangeName & " (F1) Values (" & vdata & ")"
    sqlQ = "SELECT * FROM " & RangeName
    Set oRS = New ADODB.Recordset
        oRS.ActiveConnection = oConn
        oRS.CursorType = adOpenDynamic        'Static cursor.
        oRS.LockType = 2                      'Pessimistic Lock.
        'oRS.EditMode = 3
        oRS.Source = sqlQ
    'oRS.Open sqlQ, oConn, adOpenDynamic
        oRS.Fields(0).value = vData

End Sub

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
Another way is to use the "office automation" technique, whereby you launch a copy of the Excel application, open the workbook, and copy the values directly into the cells you want.

Here is an example that's somewhat related, where we keep a running history by adding a column to an existing spread sheet.

Imports Microsoft.Office.Interop
    ' Copy a column of data from an DataTable to the first available column in
    ' the WorkSheet.  Also includes a SUM at the bottom.
    Public Sub CopyColumnToWorkSheet(ByVal dt As DataTable, ByVal dtcol As Integer, ByVal eWorkBook As Excel._Workbook, ByVal SheetName As String)
        Dim eWorkSheet As Excel.Worksheet
        Dim HeaderText, sCol As String
        Dim row, col As Integer
        Dim got_it As Boolean
        ' sanity check
        If dt.Rows.Count <= 0 Then
            Exit Sub
        End If
        ' another sanity check...
        got_it = False
        For Each ws As Excel.Worksheet In eWorkBook.Worksheets
            If ws.Name.CompareTo(SheetName) = 0 Then
                got_it = True
                Exit For
            End If
        If Not got_it Then
            Exit Sub
        End If
        eWorkSheet = CType(eWorkBook.Worksheets.Item(SheetName), Excel.Worksheet)
        ' find the first empty column
        col = 0
            col += 1
            HeaderText = CType(eWorkSheet.Cells(1, col + 1), Excel.Range).Text.ToString
        Loop While HeaderText <> ""
        ' put today's Date as the heading
        CType(eWorkSheet.Cells(1, col + 1), Excel.Range).Value = Now.ToShortDateString
        CType(eWorkSheet.Cells(1, col + 1), Excel.Range).Interior.Color = &HC0C0C0
        row = 2
        For Each dr As DataRow In dt.Rows
            CType(eWorkSheet.Cells(row, col + 1), Excel.Range).Value = dr(dtcol).ToString
            row += 1
        ' spiff it up
        sCol = ConvertCol(col)
        eWorkSheet.Range(sCol & "1:" & sCol & dt.Rows.Count + 1).Borders.Weight = Excel.XlBorderWeight.xlThin
        eWorkSheet.Range(sCol & "2:" & sCol & dt.Rows.Count + 1).WrapText = False
        ' create a sum
        CType(eWorkSheet.Cells(dt.Rows.Count + 2, col + 1), Excel.Range).Value = "=SUM(" & sCol & "2:" & sCol & dt.Rows.Count + 1 & ")"
    End Sub

Open in new window

sanjaypandeyAuthor Commented:
thank you. I will give it a try
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
.NET Programming

From novice to tech pro — start learning today.