Controlling embedded excel files on Access Form

[quuxbazer]

Hi everyone,



I have a bound object frame on my form which has contains an excel object (class = Excel.Sheet.8). When the user wants to create a new excel file, I have button such as:

Private Sub CreateNew_Click()
    On Error Resume Next
    If IsNull(Excel) Then
        Excel.Action = acOLECreateEmbed
        Excel.Action = acOLEActivate
    Else
        If MsgBox("It seems you already have data, would you still like to create a clean sheet?", vbYesNo, "Warning") = vbYes Then
            Excel.Action = acOLECreateEmbed
            Excel.Action = acOLEActivate
        End If
    End If
End Sub

Now my question is, how do I access the cells in the embedded Excel file? Like for example, I want to get the data like how I do this in excel: Worksheets(1).Cells(1,1)

I looked at some other posts on how to do this. But if I dim a variable like: Dim a as Excel.Worksheet, Access gives me an error saying "User defined type not defined".

Thanks ahead for the help guys! (I'm still kind of a noob with vba. Is there a good tutorial on learning about Objects and their manipulation?)

[ajetrumpet]

most likely, the user-defined error is coming from the broken link to the excel library. put a checkbox next to it in the 'references' menu item under the 'tools' menu.

is that embedded sheet an active x object?

[quuxbazer]

Thanks, now the excel reference is fixed.

So I kinda figured out how to control the object:

Excel.Action = acOLEActivate
Dim test As Excel.Workbook
Set test = Excel.Object
MsgBox (test.Worksheets(1).Cells(1, 1))

But apparently I HAVE to activate the object first. If I don't active the object, "Set test = Excel.Object" gives me an error saying the Excel.Object is NULL.

Is there a way around this? Thanks.

EDIT: Wait, nevermind, it only gave me that error when I put the event on the form.onload. Hmmm

anyways, Thanks for your help ajetrumpet!

[ajetrumpet]

I'm not really sure either. You may want to check ms's kb for information on using them. sorry I couldn't help more!