Automating an excel spreadsheet (OLE)

[Krass]

Hi fellow coders..

I have followed si_the_geek's excellent tutorial on how to automate excel from vb. Works like a charm... BUT now (on a completely different project of mine) I need to append data to an excel spreadsheet that is INSIDE an ole on my form.

One thread I've read was achieving that with the following code:

Dim objXL as Object

Ole1.DoVerb (-1)
Ole1.Action = 7

Set objXL = oleTable.object.ActiveSheet
 
For i = 0 to 3
 objXL.Cells(i, 1) = "Field " & i & ":"
Next i
 
Ole1.Action = 9

Set objXL = Nothing

I assumed that "Ole1" was the name of his OLE control and gave my control the same name. When executing, Ole1.DoVerb(-1) (which I have no idea what it is), triggers a "Object doesn't support this property or method" error message.

I simply skipped over it and got a "Object required" message when executing the "Set objXL" line of code.

I double-checked and "Microsoft Excel 10.0 Object Library" is in my project's references.

Any idea what is missing here?

Note1:

Note2: This is an ole excel grid on a form that will always start empty, will never be printed or saved. The user will use my application to fill this spreadsheet and when he's all done, he'll manually copy its content and work with it on his own. This is mainly why I didn't want a new excel app/spreadsheet to be opened (like si_the_geek's methods) but rather use a OLE excel spreadsheet)

Thank you.

[dee-u]

This worked for me.

Private Sub Command1_Click()
    Dim i As Integer

    OLE1.DoVerb -1
    OLE1.Action = 7

    Set objWB = OLE1.object
    For i = 1 To 3
        OLE1.object.ActiveSheet.Cells(i, 1) = "Field " & i & ":"
    Next

    OLE1.Action = 9

    Set objXL = Nothing
End Sub

[si_the_geek]

I haven't used the OLE control much, or for many years... but the line "Set objWB = OLE1.object" seems vaguely familiar, and I suspect that is the important part.

[Krass]

Thanks guys. Post #2 solved it.

I couldn't find on the net what the ".DoVerb" and ".Action" methods are used for. I guess I could just wipe em off the code. If you know what it's used for, I would appreciate.

Also, you'll notice that when you use an excel spreadsheet in a OLE object, you will see the columns "A, B, C" appearing only when you enter (double-click) the OLE object. Would there be a way to make them visible all the time?

Thank you

Christian

[anhn]

With code in post#2, I don't see any use of objWB and objXL.
If that code works then you don't need 2 lines below

    Set objWB = OLE1.object

    Set objXL = Nothing

[Krass]

anhn: You were right. Those lines were of no use.

Would anyone know what .DoVerb and .Action are used for?

Also, I used si_the_geek code to clear the excel spreadsheet, here's the code:

    LastRow = Ole1.Object.ActiveSheet.UsedRange.Rows.Count
    LastCol = Ole1.Object.ActiveSheet.UsedRange.Columns.Count
    
    For x = 1 To LastRow
        For y = 1 To LastCol
            Ole1.Object.ActiveSheet.Cells(x, y) = ""
        Next y
    Next x

...this is working fine. Just wondered if I'm missing a ".clear" method or something more effective. Meanwhile I'll stick with this.

Recap:
1- user uses my app to filter/modify/play with data.
2- user outputs that data to OLE spreadsheet in my app
3- user wants to take that data and bring it in his own excel spreadsheet

To do #3, the user enter the OLE excel spreadsheet (by double-cliking it) and manually copy the data by clicking the upperleft cell. Could this be automated?

Something like:

clipboard.settext = Ole1.Object.ActiveSheet.Range("A1:J25").Value

(I would then work the A1:J25 part more professionally as this would be dynamic - I could use .UsedRange method)

Edit:
This is interesting. I just tried this line of code. What it does is OPEN an instance of excel with my OLE spreadsheet data in it. That's almost it. I'd like that data to go to the clipboard, not to a new instance of excel.

myclip.SetText Ole1.Object.ActiveSheet.Copy

Thanks for any feedback - you guys rocks.

[jcis]

OLE1.DoVerb Opens the Object for a given operation, in this case this operation is OLEShow (-1).
OLE1.Action Sets a value that determines the Action to perform, in this case it's OLEActivate (7).
So, your Code, using the proper OLE contants (native in VB) would be:

  OLE1.DoVerb vbOLEShow
  OLE1.Action = acOLEActivate

Now in VB, right click vbOLEShow and select Definition, you'll see the contant in the Object Browser with a description and all the other Contants you can use, some will be applicable and others won't, remember the OLE Control can contain many different things like Excel, Docs, MoviePLayers, etc..

Do the same with acOLEActivate and you'll see a complete constant list too in the Object Browser.

[jcis]

This should be enough to copy to Clipboard:

OLE1.Object.ActiveSheet.UsedRange.Copy