VB6 to Excel

[Ivar]

Hi All.

I'm using VB6 to open a .xls file and put various values, formulas and
formats in to the spreadsheet,
All works well using reference to Excel 10 Object library.

I can put a button on the sheet using something like this:
xlsheet.Shapes.AddFormControl xlButtonControl, 100, 100, 100, 100
However, 2 things I can't find any sample or help on is
How to set the Caption Property of the CommandButton and
How to put any VBA code in the click event of the button, even if it's just
MsgBox "Hello World"
Or
xlsheet.Shapes(1).OnAction = "Macro1"
Cant find any info on how to add a Macro to the excel work book

Any Pointers please

Ivar

[GS]

Post by Ivar
Hi All.
I'm using VB6 to open a .xls file and put various values, formulas and
formats in to the spreadsheet,
All works well using reference to Excel 10 Object library.
xlsheet.Shapes.AddFormControl xlButtonControl, 100, 100, 100, 100
However, 2 things I can't find any sample or help on is
How to set the Caption Property of the CommandButton and
How to put any VBA code in the click event of the button, even if it's just
MsgBox "Hello World"
Or
xlsheet.Shapes(1).OnAction = "Macro1"
Cant find any info on how to add a Macro to the excel work book
Any Pointers please
Ivar

Try...

With xlSheet
.Shapes.AddFormControl xlButtonControl, 100, 100, 100, 100
With .Shapes(1)
'Control must be selected to edit text
.Select: Selection.Characters.Text = "ButtonCaption"
.OnAction = "MacroName"
End With
.Cells(1).Select 'exit edit text mode
End With

I don't know how to programmatically insert code behind the worksheet,
so hopefully someone will step up to help you with that. (I usually
build an Excel XLT that's all set up to receive data, with layout,
menus/controls, and formatting already in place)

<FYI>
It's always a good idea to go through a process manually and run
Excel's macro recorder so it generates code. This should give you what
you need to automate from VB6. Note that the generated code will need
to be cleaned up some to get rid of the inefficient coding the macro
recorder creates.
</FYI>

[Ivar]

Thank you for your reply
.Select - That's the bit I was missing. Got it now

After adding a reference to "Microsoft Visual basic for Applications
Extensibility" to the VB6 project I can now add a std Module to the Excel
Project files and add code to that. I would still rather put the code in the
click event of the button tho.

Using the Macro recorder is an easy way to get the VBA sintax, it's
unfortunate that you can't use the macro recorder to record that fact that
you are creating a macro. Oh well :-)

Thanks again

Ivar

Post by GS
With xlSheet
.Shapes.AddFormControl xlButtonControl, 100, 100, 100, 100 With
.Shapes(1)
'Control must be selected to edit text
.Select: Selection.Characters.Text = "ButtonCaption"
.OnAction = "MacroName"
End With
.Cells(1).Select 'exit edit text mode
End With
I don't know how to programmatically insert code behind the worksheet, so
hopefully someone will step up to help you with that. (I usually build an
Excel XLT that's all set up to receive data, with layout, menus/controls,
and formatting already in place)
<FYI>
It's always a good idea to go through a process manually and run Excel's
macro recorder so it generates code. This should give you what you need to
automate from VB6. Note that the generated code will need to be cleaned up
some to get rid of the inefficient coding the macro recorder creates.
</FYI>
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

[ralph]

On Sun, 24 Apr 2011 16:04:14 +0100, "Ivar"

Post by Ivar
Thank you for your reply
.Select - That's the bit I was missing. Got it now
After adding a reference to "Microsoft Visual basic for Applications
Extensibility" to the VB6 project I can now add a std Module to the Excel
Project files and add code to that. I would still rather put the code in the
click event of the button tho.

I'm probably missing something, but .... have you tried right-clicking
the button of interest and then assign a macro to it. <?>

-ralph

[GS]

Post by Ivar
Thank you for your reply
.Select - That's the bit I was missing. Got it now
After adding a reference to "Microsoft Visual basic for Applications
Extensibility" to the VB6 project I can now add a std Module to the Excel
Project files and add code to that.

While you're at it you could predesign your worksheet[s]
(formatting/formulas already in place) so all you need to do from VB6
is dump your data into the appropriate cells.

Post by Ivar
I would still rather put the code in the click event of the button tho.

The type of button your using doesn't have a 'click event'. As Gord
suggests, you assign a macro to it. This is done programmatically by
specifying the macro when setting its "OnAction" property as shown in
my code sample.

To do this manually you can right-click the button and choose 'Assign
macro' on the popup menu.

If you prepare the Excel file beforehand you can have the controls in
place with caption text, formatting, and macros assigned. (Requires the
macros also exist, preferably in a standard module.)

Post by Ivar
Using the Macro recorder is an easy way to get the VBA sintax, it's
unfortunate that you can't use the macro recorder to record that fact that
you are creating a macro. Oh well :-)

Not sure what you mean here. Using the macro recorder to record a macro
does (by attrition) do just that. Problem is the generated code almost
always needs cleanup/refining editing.

[GS]

Post by Ivar
.Select - That's the bit I was missing. Got it now

Thanks to a handy tip from Steve Rindsberg I've been able to remove the
need to select the button as was coded using the macro recorder. This
also serves as an example of what I meant by 'cleanup' to code
generated by the macro recorder. Revised code follows:

With xlSheet.Shapes.AddFormControl(xlButtonControl, 100, 100, 100, 100)
.OLEFormat.Object.Caption = "ButtonCaption"
.OnAction = "MacroName"
End With

[Steve Rindsberg]

Post by GS
With xlSheet
.Shapes.AddFormControl xlButtonControl, 100, 100, 100, 100
With .Shapes(1)
'Control must be selected to edit text
.Select: Selection.Characters.Text = "ButtonCaption"
.OnAction = "MacroName"
End With
.Cells(1).Select 'exit edit text mode
End With

I'd do this instead:

' Set up a variable to hold a reference to the shape
' you're going to create:
Dim oSh as Shape

With xlSheet
' create the shape and get a ref to it
Set oSh = .Shapes.AddFormControl( xlButtonControl, 100, 100, 100, 100)
' now you can work with the shape
' and don't have to rely on it being the first shape on the sheet
With oSh
'Control must be selected to edit text
.Select: Selection.Characters.Text = "ButtonCaption"
.OnAction = "MacroName"
End With
.Cells(1).Select 'exit edit text mode
End With

You can also avoid selecting it if you do this:

With oSh.OLEFormat.Object
.Caption = "Your Text Here"
End With

And inside that With/End With context you can work with any of the other
properties you'd normally see when you rightclick/Properties. Intellisense
won't help you but the Properties dialog will give you the names of the
available properties.

[GS]

Steve,
Thanks for that handy tip using OLEFormat. I've revised the code as
follows:

With xlSheet.Shapes.AddFormControl(xlButtonControl, 100, 100, 100, 100)
.OLEFormat.Object.Caption = "ButtonCaption"
.OnAction = "MacroName"
End With

[ralph]

On Sun, 24 Apr 2011 13:01:47 +0100, "Ivar"

Post by Ivar
Cant find any info on how to add a Macro to the excel work book

GS kindly made clear to me what it was you were trying to do.

In addition to his sage advice take a look at these two methods ...
http://www.mrexcel.com/forum/showthread.php?t=68286
to add existing macros to a new work book.

But as the others have already pointed out, there isn't much
difference between having a 'macro module' file you import into new
projects/workbooks or simply having a 'seed workbook/project', aka a
Template which already includes the macros you might want to use.

However, having a number of 'macros' or VBS code stored away in
'library' modules is a handy development tool. I'm all in favor of
write/test-once solutions. <g>

-ralph

[ralph]

Post by ralph
However, having a number of 'macros' or VBS code stored away in
'library' modules is a handy development tool. I'm all in favor of
write/test-once solutions. <g>

Oops!

Meant "VBA code" not "VBS code".

-ralph

[Steve Rindsberg]

Post by ralph
But as the others have already pointed out, there isn't much
difference between having a 'macro module' file you import into new
projects/workbooks or simply having a 'seed workbook/project', aka a
Template which already includes the macros you might want to use.

I haven't done enough with Excel to say whether this'd be true as in
some other Office products, but the ability to add code *via* code may
be dependent on the user's security settings; they may not allow access
to the visual basic project.

[GS]

Post by Steve Rindsberg

Post by ralph
But as the others have already pointed out, there isn't much
difference between having a 'macro module' file you import into new
projects/workbooks or simply having a 'seed workbook/project', aka a
Template which already includes the macros you might want to use.

I haven't done enough with Excel to say whether this'd be true as in
some other Office products, but the ability to add code *via* code may
be dependent on the user's security settings; they may not allow access
to the visual basic project.

Hi Steve,
The security settings are suppressed for automated instances. In this
case it might be possible regardless of user rights. As I already
stated, I've never written VBA programmatically but I've acted on the
VBE enough to know it's doable in an automated instance.

HTH

[Steve Rindsberg]

Post by GS

Post by Steve Rindsberg

Post by ralph
But as the others have already pointed out, there isn't much
difference between having a 'macro module' file you import into new
projects/workbooks or simply having a 'seed workbook/project', aka a
Template which already includes the macros you might want to use.

I haven't done enough with Excel to say whether this'd be true as in
some other Office products, but the ability to add code *via* code may
be dependent on the user's security settings; they may not allow access
to the visual basic project.

Hi Steve,
The security settings are suppressed for automated instances. In this
case it might be possible regardless of user rights. As I already
stated, I've never written VBA programmatically but I've acted on the
VBE enough to know it's doable in an automated instance.

Ah, thanks. That's good to know. I was fairly certain that there'd be no
macro warnings in an automated instance but wasn't sure about access to the
VBE.

[GS]

Post by Steve Rindsberg

Post by GS

Post by Steve Rindsberg

Post by ralph
But as the others have already pointed out, there isn't much
difference between having a 'macro module' file you import into new
projects/workbooks or simply having a 'seed workbook/project', aka a
Template which already includes the macros you might want to use.

I haven't done enough with Excel to say whether this'd be true as in
some other Office products, but the ability to add code *via* code may
be dependent on the user's security settings; they may not allow access
to the visual basic project.

Hi Steve,
The security settings are suppressed for automated instances. In this
case it might be possible regardless of user rights. As I already
stated, I've never written VBA programmatically but I've acted on the
VBE enough to know it's doable in an automated instance.

Ah, thanks. That's good to know. I was fairly certain that there'd be no
macro warnings in an automated instance but wasn't sure about access to the
VBE.

Well, as I said, I don't write code programmatically so I don't know if
that'll be possible. Thought I only suspect it's doable since we can
access the VBE in automated instances with no problem.<g>