[RESOLVED] - Cannot open and update Excel
Hi,
I have an InfoPath(2007) form that users fill out and click a Submit button when complete. I have Visual Basic Code behind the button that needs to do the following:
- Opens and existing Excel spread sheet
- Updates a new row with the submitted information
- Save
- Closes
I cannot get this to work properly...I've been inundated with numerous errors. When I fix one error another one appears.
Has anyone worked on anything similar to what I've described...this sounds so trivial, but hasn't worked out the way I intended. Any help is appreciated...
Thanks and regards!
Re: Cannot open and update Excel
Please post your code so we can see what you have. Doing redundant coding over again wastes time.
Re: Cannot open and update Excel
Hi...Thanks for your reply. Here's what I have so far. I get an error stating that 'CreateObject' is not declared. Then when I declare it I get another error stating: "Variable 'CreateObject' is used before it has been assigned a value..."
Nothing writes to excel...
vb.net Code:
Option Explicit On
Imports Microsoft.Office.Interop
Imports Microsoft.Office.InfoPath
Public Sub CTRL2_5_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbooks
objExcel = CreateObject("Excel.Application")
objWorkbook = objExcel.Workbooks.Open("C:\myVBtest\test.xls")
'this is a test just to populate excel
'this doesn't work
objExcel.Cells(1, 1).Equals("TESTME")
'i really need it to do this; grab info from the form
'this does not work
'objExcel.Cells(1, 1).Equals("/my:myFields/my:field2")
objExcel.Workbooks(1).Save() 'Save the workbook, not excel file
objExcel.Workbooks(1).Close() 'Close the workbook then finally quit excel
objExcel.Quit()
End Sub
End Class
End Namespace
Thanks again!
Re: Cannot open and update Excel
The used before assigned is typically a warning.
It looks fine to me though.
vb Code:
'i really need it to do this; grab info from the form
'this does not work
'objExcel.Cells(1, 1).Equals("/my:myFields/my:field2")
I know its commented out, and I'm no excel expert, but what's this do that you haven't already done?
Re: Cannot open and update Excel
The piece of code that I have commented out is supposed get data from an InfoPath form. I'll try to get that working later.
The current code that I have doesn't work at all. It seems simple enough, but it doesn't do anything. Have you tried to run a similar piece of code? I was hoping someone out there had any luck.
Thanks!
Re: Cannot open and update Excel
Can someone point me to a post in this forum that had a similar issue? I can't find anything on this subject. My code looks simple enough and yet it doesn't work. Can someone try to run what I have? I could have a setup issue...
Thanks again!
Re: Cannot open and update Excel
Check out the Office Development FAQ thread in the Office Development section.
There is heaps of examples on working with office
Thread #3 may be interesting
HTH
toe
Re: Cannot open and update Excel
Try this:
Code:
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim MyExcel As New MyExcel("C:\myVBtest\test.xls", , False)
MyExcel.MyExcelWorksheet.Range("A1").Value = "Hello World"
MyExcel.MyExcelWorkbook.Close(True)
MyExcel.MyExcelApp.Quit()
End Sub
End Class
Public Class MyExcel
Public MyExcelApp As Excel.Application
Public MyExcelWorkbook As Excel.Workbook
Public MyExcelWorksheet As Excel.Worksheet
Public Sub New(ByVal PathToFile As String, Optional ByVal Visible As Boolean = True, Optional ByVal DisplayAlerts As Boolean = True)
MyExcelApp = New Excel.Application
MyExcelApp.Visible = Visible
MyExcelApp.DisplayAlerts = DisplayAlerts
MyExcelWorkbook = MyExcelApp.Workbooks.Open(PathToFile)
MyExcelWorksheet = DirectCast(MyExcelWorkbook.Worksheets.Item("Sheet1"), Excel.Worksheet)
End Sub
End Class
[Resolved] - Cannot open and update Excel
:check:Thanks to all the reply's.
I got this to work a couple of days ago...changed the following:
objExcel = CreateObject("Excel.Application")
to
objExcel = New Excel.Application
Thanks again!