Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Write to Excel

 
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
VBdotnet2005
Asked:
VBdotnet2005
  • 13
  • 11
1 Solution
 
Jorge PaulinoIT Pro/DeveloperCommented:
Hi VBdotnet2005,


I don't understand what is working and what are the ?????
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You want to fill with data from the db and save with a new name in the end ?
0
 
VBdotnet2005Author Commented:
correct...
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Jorge PaulinoIT Pro/DeveloperCommented:
>> 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
 
VBdotnet2005Author Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
Replace:
wrkbook.sheet(1)

With:
wrkbook.Worksheets(1)
0
 
VBdotnet2005Author Commented:
I can't find wrkbook.Worksheets(1)
0
 
VBdotnet2005Author Commented:
I am usring MS office 2007
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Ok, I use 2003 version but you have Sheets(1)!

Right ?
0
 
VBdotnet2005Author Commented:
I declared this wrong...


Dim wrkbook As Excel.Workbooks


to

Dim wrkbook As Excel.Workbook
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
VBdotnet2005Author Commented:
I am sorry. What is  "x" >   "A" & x ? >>>>>>>  xlApp.Range("A" & x).Value = reader("field1")
0
 
VBdotnet2005Author Commented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
VBdotnet2005Author Commented:
thank you so much... That works for me.
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Glad I could help!

jpaulino
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
VBdotnet2005Author Commented:
I tried

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

Excel instance still show on Task manager.
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Do you have that on the Finally (from the try catch block) ?
0
 
VBdotnet2005Author Commented:
Yes. I do.
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
CAn you show the code you have ?
0
 
VBdotnet2005Author Commented:
    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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now