[Webinar] Streamline your web hosting managementRegister Today


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

Posted on 2008-02-01
Medium Priority
Last Modified: 2013-11-26
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 ...

Question by:sanjaypandey
LVL 33

Accepted Solution

Robberbaron (robr) earned 1000 total points
ID: 20803743
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
LVL 41

Assisted Solution

graye earned 1000 total points
ID: 20804597
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


Author Closing Comment

ID: 31427428
thank you. I will give it a try

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

591 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