VBdotnet2005
asked on
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("mypa
xlApp.Range("A" & 1).Value = "Tim"
xlapp.range("B" & ???????????????
wrkbook.Close()
wrkbook = Nothing
xlApp = Nothing
End Sub
You want to fill with data from the db and save with a new name in the end ?
ASKER
correct...
>> 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.x lsx")
wrkbook.Close()
wrkbook = Nothing
xlApp = Nothing
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.x
wrkbook.Close()
wrkbook = Nothing
xlApp = Nothing
ASKER
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("mypa th\test.xl sx")
Dim wrksheet As Excel.Worksheet = wrkbook.sheet(1) <<<<<<<<<<<< error this line
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("mypa
Dim wrksheet As Excel.Worksheet = wrkbook.sheet(1) <<<<<<<<<<<< error this line
Replace:
wrkbook.sheet(1)
With:
wrkbook.Worksheets(1)
wrkbook.sheet(1)
With:
wrkbook.Worksheets(1)
ASKER
I can't find wrkbook.Worksheets(1)
ASKER
I am usring MS office 2007
Ok, I use 2003 version but you have Sheets(1)!
Right ?
Right ?
ASKER
I declared this wrong...
Dim wrkbook As Excel.Workbooks
to
Dim wrkbook As Excel.Workbook
Dim wrkbook As Excel.Workbooks
to
Dim wrkbook As Excel.Workbook
Ok, I didn't notice that. In my example that i'm doing it's ok,
How's the code now ? It's working ?
How's the code now ? It's working ?
ASKER
I am sorry. What is "x" > "A" & x ? >>>>>>> xlApp.Range("A" & x).Value = reader("field1")
ASKER
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("mypa th\test.xl sx")
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").ToStr ing
xlApp.Range("B" & x).Value = dr.Item("LastName").ToStri ng
xlApp.Range("C" & x).Value = dr.Item("Address").ToStrin g
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
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("mypa
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.Open()
Try
Dim dr As SqlDataReader = Sqlcommand.ExecuteReader
While dr.Read
xlApp.Range("A" & x).Value = dr.Item("Firstname").ToStr
xlApp.Range("B" & x).Value = dr.Item("LastName").ToStri
xlApp.Range("C" & x).Value = dr.Item("Address").ToStrin
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
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")
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")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you so much... That works for me.
Glad I could help!
jpaulino
jpaulino
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.WaitForPendingFinalizer s()
End Try
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.WaitForPendingFinalizer
End Try
ASKER
I tried
Con.Close()
GC.Collect()
GC.WaitForPendingFinalizer s()
Excel instance still show on Task manager.
Con.Close()
GC.Collect()
GC.WaitForPendingFinalizer
Excel instance still show on Task manager.
Do you have that on the Finally (from the try catch block) ?
ASKER
Yes. I do.
CAn you show the code you have ?
ASKER
Dim xlApp As New Excel.Application
Dim wrkbook As Excel.Workbook = xlApp.Workbooks.Open(mypat h)
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.WaitForPendingFinalizer s()
End Try
Dim wrkbook As Excel.Workbook = xlApp.Workbooks.Open(mypat
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.WaitForPendingFinalizer
End Try
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.
You should also include all code in the try ... catch method. If it fails on open it doesn't do the close/GC.
I don't understand what is working and what are the ?????