0ne-0nly
asked on
Automating Excel
Hi,
I need to automate Excel from VB.Net. What I need to do, is get data from a DtaBase such access, and populate that data into an excel file. The excel file has to be a template, so it already exists at a certain location on the machine. I am able to retrieve the data and I can populate to excel, but when it is finished, the file is not saved. What I want to do, is populate the template excel, then save it as a different excel file, allowing the user to choose the location and name of the new file. Below is the code I am using to populate excel.
I need to automate Excel from VB.Net. What I need to do, is get data from a DtaBase such access, and populate that data into an excel file. The excel file has to be a template, so it already exists at a certain location on the machine. I am able to retrieve the data and I can populate to excel, but when it is finished, the file is not saved. What I want to do, is populate the template excel, then save it as a different excel file, allowing the user to choose the location and name of the new file. Below is the code I am using to populate excel.
Dim xcApp As New Microsoft.Office.Interop.Excel.Application
Dim xcWB As Microsoft.Office.Interop.Excel.Workbook = xcApp.Workbooks.Open("C:\Documents and Settings\117152\My Documents\Visual Studio 2008\Projects\ECA\ECA\Documents\Simple.xlt")
Dim xcWS As Microsoft.Office.Interop.Excel.Worksheet = xcWB.Worksheets("sheet1")
Dim xcRange As Microsoft.Office.Interop.Excel.Range
Dim ADOConnection As ADODB.Connection
Dim RS As ADODB.Recordset
ADOConnection = New ADODB.Connection()
RS = New ADODB.Recordset()
ADOConnection.ConnectionString = myConnection
ADOConnection.Open()
RS.Open(SQLStr1, ADOConnection)
While Not RS.EOF
xcRange = xcWS.Range("A4")
xcRange.Value = RS.Fields("transMnth").Value & " " & RS.Fields("transYear").Value
xcRange = xcWS.Range("A5")
xcRange.Value = RS.Fields("transIncome").Value
RS.MoveNext()
End While
RS.Close()
xcWB.Save()
'clean up
xcWB.Close()
xcApp.Quit()
xcApp = Nothing
xcWB = Nothing
xcWS = Nothing
xcRange = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Instead of opening the template, create a new workbook, using the template....
Dim xcWB As Microsoft.Office.Interop.E xcel.Workb ook = xcApp.Workbooks.Add("C:\Do cuments and Settings\117152\My Documents\Visual Studio 2008\Projects\ECA\ECA\Docu ments\Simp le.xlt")
...and save the file using something like this....
Dim sfd As New SaveFileDialog
sfd.Filter = "Excel Files (*.xls)|*.xls"
sfd.FileName = "Filename.xls"
If sfd.ShowDialog = System.Windows.Forms.Dialo gResult.OK Then
xcWB.SaveAs(sfd.FileName)
xcWB.Close()
Else
xcWB.Close(False)
End If
Moving the Close statement to this snippet of code enables you to close the workbook without saving if the user clicks cancel on the dialog.
Wayne
Dim xcWB As Microsoft.Office.Interop.E
...and save the file using something like this....
Dim sfd As New SaveFileDialog
sfd.Filter = "Excel Files (*.xls)|*.xls"
sfd.FileName = "Filename.xls"
If sfd.ShowDialog = System.Windows.Forms.Dialo
xcWB.SaveAs(sfd.FileName)
xcWB.Close()
Else
xcWB.Close(False)
End If
Moving the Close statement to this snippet of code enables you to close the workbook without saving if the user clicks cancel on the dialog.
Wayne
ASKER