Export to EXCEL from VB.NET

I want to do an export in VB.NET to a excel spreadsheet with the columns:

Some text 1, Some text 2, Some Text 3, http://www.someurl1.com
Some text 4, Some text 5, Some Text 6, http://www.someurl2.com

I want the URL to be a hyperlink and if there are rows in the spreadsheet from before I want the new rows to be appended.

Can anyone please give me some advise to get me started?


jes12345Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Om PrakashConnect With a Mentor Commented:
Please check the following KB article:
http://support.microsoft.com/kb/316934
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
0
 
jes12345Author Commented:
Thanks for good advises. This was a good start. However I am still not able to display the URLs as I want.

I tried the code below but it does not seem to accept formulas..
Imports System.Data.OleDb

Public Class Form1

    Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=C:\ExcelData1.xls;" & _
               "Extended Properties=""Excel 8.0;HDR=YES"""

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim conn As New OleDbConnection()
        conn.ConnectionString = m_sConn1
        conn.Open()
        Dim cmd1 As New OleDbCommand()
        cmd1.Connection = conn
        cmd1.CommandText = "CREATE TABLE EmployeeData (Id char(255), Name char(255), BirthDate date)"
        cmd1.ExecuteNonQuery()
        cmd1.CommandText = "INSERT INTO EmployeeData (Id, Name, BirthDate) values ('AAA', '=Hyperlink('http://www.google.com','test')', '12/4/1955')"
        cmd1.ExecuteNonQuery()
        conn.Close()


    End Sub
End Class

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Om PrakashCommented:
cmd1.CommandText = "INSERT INTO EmployeeData (Id, Name, BirthDate) values ('AAA', '=HYPERLINK(""www.google.com"",""Google"")', '12/4/1955');"
0
 
jes12345Author Commented:
Did this work for you? I only get this:
Untitled-1.jpg
0
 
Om PrakashConnect With a Mentor Commented:
Limitations of Using Excel as a Database System

The following is some limitations when using ADO.NET with Excel:
- You can not delete a record or an entire datasheet using ADO.NET.
- You can not insert formulas in cells using ADO.NET.

http://www.beansoftware.com/NET-Tutorials/Excel-ADO.NET-Database.aspx
0
 
CodeCruiserConnect With a Mentor Commented:
Use automation.

To find out current number of rows populated:

ExcelApp.Workbooks(1).WorkSheets(1).UsedRange.Rows.Count

Similarly
ExcelApp.Workbooks(1).WorkSheets(1).UsedRange.Columns.Count



To add a hyperlink
ExcelApp.Workbooks(1).Worksheets(1).Hyperlinks.Add("A1", "http://www.microsoft.com", ScreenTip:="Microsoft", TextToDisplay:="Microsoft's Website."), Excel.Hyperlink)

Use appropriate objects (replace ExcelApp with your object refering to Excel)

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.hyperlinks%28VS.80%29.aspx
0
 
jes12345Author Commented:
Many thanks for the advise. Hope you don't mind a follow up question. I have never programmed against Excel but this may seem as a better approach then the OLEDB approach. Can I please advise if you know a good tutorial for how to setup the "ExcepApp"?
0
 
CodeCruiserConnect With a Mentor Commented:
Here is a complete tutorial

http://support.microsoft.com/kb/301982
0
 
jes12345Author Commented:
Many thanks for good advises:)
0
 
jes12345Author Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.