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?
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.

Om PrakashCommented:
Please check the following KB article:
http://support.microsoft.com/kb/316934
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
Éric MoreauSenior .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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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 PrakashCommented:
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
CodeCruiserCommented:
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
CodeCruiserCommented:
Here is a complete tutorial

http://support.microsoft.com/kb/301982
0
jes12345Author Commented:
Many thanks for good advises:)
0
jes12345Author Commented:
Thanks!
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
Visual Basic.NET

From novice to tech pro — start learning today.