[RESOLVED] - Cannot open and update Excel

[gmem9]

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!

[formlesstree4]

Please post your code so we can see what you have. Doing redundant coding over again wastes time.

[gmem9]

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...

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!

[formlesstree4]

The used before assigned is typically a warning.
It looks fine to me though.

'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?

[gmem9]

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!

[gmem9]

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!

[toecutter]

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

[ForumAccount]

Try this:

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

[gmem9]

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!