?
Solved

Write to Excel

Posted on 2008-06-09
24
Medium Priority
?
225 Views
Last Modified: 2010-04-23
 
I have a tamplate in Excel  2007. Column "A" is Firstname and column "B" is Lastname , and column "C" is Address. I have no problem getting data from DB. All I want to find out is how can I use my template and fill it out and save it to a new file.

column A       column B     Column C
FirstName  |    LastName   | Address

Tim             Johnsone     123 Test st. San Francisco, Ca 90000






 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As New Excel.Application
        Dim wrkbook As Excel.Workbooks

        wrkbook = xlApp.Workbooks.Open("mypath\test.xlsx")

        xlApp.Range("A" & 1).Value = "Tim"
      xlapp.range("B" & ???????????????

        wrkbook.Close()
        wrkbook = Nothing
        xlApp = Nothing





    End Sub
0
Comment
Question by:VBdotnet2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
24 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 21747315
Hi VBdotnet2005,


I don't understand what is working and what are the ?????
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21747319
You want to fill with data from the db and save with a new name in the end ?
0
 

Author Comment

by:VBdotnet2005
ID: 21747355
correct...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:jpaulino
ID: 21747391
>> I have no problem getting data from DB

You have only to do a loop on your db and fill the rows. Ex.

Dim x As Integer = 3 ' to star on 3rd row
While reader.Reads()
     xlApp.Range("A" & x).Value = reader("field1")
     xlApp.Range("B" & x).Value = reader("field1")
     xlApp.Range("C" & x).Value = reader("field1")
    x += 1
End While

Then in the end:
wrkbook.SaveAs("FileName.xlsx")
wrkbook.Close()
wrkbook = Nothing
xlApp = Nothing
0
 

Author Comment

by:VBdotnet2005
ID: 21747420
Correct?

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As New Excel.Application
        Dim wrkbook As Excel.Workbooks = xlApp.Workbooks.Open("mypath\test.xlsx")
        Dim wrksheet As Excel.Worksheet = wrkbook.sheet(1)  <<<<<<<<<<<< error this line
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21747491
Replace:
wrkbook.sheet(1)

With:
wrkbook.Worksheets(1)
0
 

Author Comment

by:VBdotnet2005
ID: 21747527
I can't find wrkbook.Worksheets(1)
0
 

Author Comment

by:VBdotnet2005
ID: 21747534
I am usring MS office 2007
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21747538
Ok, I use 2003 version but you have Sheets(1)!

Right ?
0
 

Author Comment

by:VBdotnet2005
ID: 21747543
I declared this wrong...


Dim wrkbook As Excel.Workbooks


to

Dim wrkbook As Excel.Workbook
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21747551
Ok, I didn't notice that. In my example that i'm doing it's ok,

How's the code now ? It's working ?
0
 

Author Comment

by:VBdotnet2005
ID: 21747579
I am sorry. What is  "x" >   "A" & x ? >>>>>>>  xlApp.Range("A" & x).Value = reader("field1")
0
 

Author Comment

by:VBdotnet2005
ID: 21747588
This is the whole code.    


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As New Excel.Application
        Dim wrkbook As Excel.Workbook = xlApp.Workbooks.Open("mypath\test.xlsx")
        Dim wrksheet As Excel.Worksheet = wrkbook.Worksheets(1)


        Dim myConstring As String = "mystring"
        Dim Con As New SqlConnection(myConstring)
        Dim myCommandString As String = "select ...."
        Dim Sqlcommand As New SqlCommand(myCommandString, Con)


        Con.Open()

        Try

            Dim dr As SqlDataReader = Sqlcommand.ExecuteReader

            While dr.Read


                xlApp.Range("A" & x).Value = dr.Item("Firstname").ToString
                xlApp.Range("B" & x).Value = dr.Item("LastName").ToString
                xlApp.Range("C" & x).Value = dr.Item("Address").ToString

            End While


            dr.Close()

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        Finally

            Con.Close()


        End Try



        wrkbook.SaveAs("test.xlsx")
        wrkbook.Close()
        wrkbook = Nothing
        xlApp = Nothing

    End Sub
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21747592
The "x" will represent the row number.

Range("A1").Value = "something" will write "someting" to the first row/column of excel. You add a variable to write each row of your databases to each row of the worksheet.

Then you will increment the variable to write in the second row  - Range("A2")
0
 
LVL 48

Accepted Solution

by:
jpaulino earned 2000 total points
ID: 21747598
Try to change it to this:

Dim X as Integer = 3
While dr.Read
    xlApp.Range("A" & x).Value = dr.Item("Firstname").ToString
    xlApp.Range("B" & x).Value = dr.Item("LastName").ToString
    xlApp.Range("C" & x).Value = dr.Item("Address").ToString
    x +=1
End While
0
 

Author Comment

by:VBdotnet2005
ID: 21747605
thank you so much... That works for me.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21747613
Glad I could help!

jpaulino
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21747624
Just one extra ...

Sometimes excel instance keeps on memory after you close it. To avoid that "problem" you should also do this in the "Finally" on Try block, forcing the Garbage Collection to work.

Finally
   Con.Close()
   GC.Collect()
   GC.WaitForPendingFinalizers()
End Try
0
 

Author Comment

by:VBdotnet2005
ID: 21795604
I tried

  Con.Close()
   GC.Collect()
   GC.WaitForPendingFinalizers()

Excel instance still show on Task manager.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21795800
Do you have that on the Finally (from the try catch block) ?
0
 

Author Comment

by:VBdotnet2005
ID: 21796404
Yes. I do.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21796767
CAn you show the code you have ?
0
 

Author Comment

by:VBdotnet2005
ID: 21797607
    Dim xlApp As New Excel.Application
        Dim wrkbook As Excel.Workbook = xlApp.Workbooks.Open(mypath)
        Dim wrksheet As Excel.Worksheet = wrkbook.Worksheets(1)

        Try

            Dim I As Integer = 1
            Dim re As Boolean

            While re = False

                I += 1

                myval = Trim(CStr(xlApp.Range("A" & I).Value))


                'MessageBox.Show(myval)



                If CStr(xlApp.Range("A" & I).Value) = Nothing Then

                    re = True

                End If


            End While


            wrkbook.Close()
            wrkbook = Nothing
            xlApp = Nothing

        Catch ex As Exception

            MessageBox.Show(ex.Message)  

        Finally

            GC.Collect()
            GC.WaitForPendingFinalizers()


        End Try
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21797981
Have you checked if isn't from other tests ? Have your delete manually all other excel instances ?

You should also include all code in the try ... catch method. If it fails on open it doesn't do the close/GC.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

770 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