call visual-basic routin in an excel .xls

[NK]

Hello Fivewinners,

I have inserted a button in an Excel xls-File which invokes a Visual-Basic routine.
how can I invoke this routine cmdButton_Click() with ole?

best regards, Norbert

[Jeff Barnes]

Norbert,

I don't know if this will help but you can try to record a macro, then click your button. Now edit the macro and look at the code, it might give you an idea as to how to incoke from FW.

Jeff

[NK]

Jeff,

Norbert,

I don't know if this will help but you can try to record a macro, then click your button. Now edit the macro and look at the code, it might give you an idea as to how to incoke from FW.

Jeff

good idea but the click on the button isn't recorded in the Macro.

Norbert

[Gale FORd]

Try recording a macro.
Then record another macro that runs the first macro.
Then debug code and you will find something like
Application.Run "Book1!Macro1"

Maybe this gives you a clue.

[NK]

Try recording a macro.
Then record another macro that runs the first macro.
Then debug code and you will find something like
Application.Run "Book1!Macro1"

Maybe this gives you a clue.

Unfortunately, this doesn't work either since the keystroke isn't recorded in the macro.

I would like simply to fill a table with values and then reprocess these data by a routine which I have put on a key.

Code: Select all

Private Sub cmdCreateBeleg_Click()

    If (bOLAnmeldung) Then
        'MsgBox "Anmeldung erfolgreich"
        bAngemeldet = True
    End If
    ReadCells
    If bAngemeldet Then
        bOk = bSetBelegKopf
        bOk = bSetPositionen
        bOk = bSaveBeleg
        WriteErgebnis
    Else
'         MsgBox "Sie sind nicht an der Office Line angemeldet."
    End If

    Clear

End Sub

Isn't it possible to start a Visual-BASIC routine with OLE, then?

Norbert

[Gale FORd]

Why not put the code that is now in cmdCreateBeleg_Click() in a macro.
Then call the macro from cmdCreateBeleg_Click(), and from external program.

[NK]

Why not put the code that is now in cmdCreateBeleg_Click() in a macro.
Then call the macro from cmdCreateBeleg_Click(), and from external program.

Because within this routine further Functionen are invoked.
I have tested it. An error message "Function or Sub not defined" then comes

Norbert

[NK]

Why not put the code that is now in cmdCreateBeleg_Click() in a macro.
Then call the macro from cmdCreateBeleg_Click(), and from external program.

I have worked to execute the routine as Macro (simple this Private remove).
Can you still give me a tip as I start a Macro with OLE?

Norbert

[Gale FORd]

This works for me.

Code: Select all

function test()
   oExcel := TOleAuto():New("Excel.Application")
   oExcel:WorkBooks:Open( "H:\My Documents\test.xls" )
   oExcel:Visible := .t.
   oExcel:Run( "test.xls!Sheet1.CommandButton1_Click" )
return( .t. )

[Gale FORd]

Here is the code for my button in sheet1

Code: Select all

Sub CommandButton1_Click()
  MsgBox "Sie sind nicht an der Office Line angemeldet."
End Sub

[NK]

This works for me.

Code: Select all

function test()
   oExcel := TOleAuto():New("Excel.Application")
   oExcel:WorkBooks:Open( "H:\My Documents\test.xls" )
   oExcel:Visible := .t.
   oExcel:Run( "test.xls!Sheet1.CommandButton1_Click" )
return( .t. )

It works. Many thanks to you.

Norbert