[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

VB creating Excel Worksheet

Background: Have a VBA script which runs in Excel 97
It creates a Worksheet and then fills the cells
with text. The size of the VBA script(approx 28 pages
printed) soon exceeded the resource of EXCEL/VBA.
The only way I can get this script to run to completion
is to reduce array sizes for example.
One of the advisor in this forum recommended
VB/ActiveX EXE as a solution.
Questions:
 1.Can Excel Worksheet be created and its cells
controlled and populated using  VB(activeX EXE) or does VB have
to somehow run Excel and prompt Excel to run some
VBA script?
 2. The vba script when imported as a module does
not compile in VB because of Excel classes such
as 'Range' in the script. Can I add libraries
that will enable VB to understand what an Excel
'Range' is for example? How do I import these
libraries?
3. Would this Excel/VB/ActiveX EXE situation be
initiated by a dummy vba script run in Excel
which somehow references the ActiveX EXE, which then
does most of the processing.
0
mbarron
Asked:
mbarron
  • 2
  • 2
1 Solution
 
mcixCommented:
1. Excel can be created and controlled from VB.  It does not necessarily have to be an ActiveX EXE.  It could be any of the standard project types; Standard EXE or ActiveX DLL will work as well.  That decision is based on how your application needs to be deployed. (A much deeper conversation)

2. If you have Excel 97 and VB5 loaded on your machine, you can add a reference to the Excel 97 library using the <R>eferences option of the <T>ools menu.  It should be listed as Microsoft Excel 8.8 Object Library.  Using the Object Browser <F2>, you can explore the full Excel 97 Object Model.

3. You will have the option to create the full functionality you require from you VB app.  This can be invoked using several methods.  A simple one would be to compile the VB app into a DLL and reference the DLL you create from VB.

Here is an article that touches on what you can do with VB and Excel.

http://support.microsoft.com/support/kb/articles/q124/0/94.asp
0
 
vplusplusCommented:
I think mcix have answered most of your questions.
Here is something to add...

>You wrote...
>The vba script when imported as a module does
>not compile in VB because of Excel classes such
>as 'Range' in the script.
>
After bringing in the VBA script to VB you may
have to touch the code a little bit.
For example, some property/method invocations assume
a current object ( like ActivveWorkBook, ActiveWorkSheet,
etc.) Simple mentioning of Range would imply
<CurrentlyActiveSheet>.Range

This will not be the case in VB. you may require
an explicit reference to the worksheet. If multiple
lines are using this IMPLIED NOTATION of VBA in Excel,
a simple "With <WorksheetObject> " at the beginning
of code block and an "End With" at the end will solve
the problem. You may also require to prefix all
method/property invocations with in the block with
a period ( Ex .Range )



0
 
mbarronAuthor Commented:
I want to give the points to you, but you gave a comment,
and I don't have ability to grade a comment as I see it.
  Mark
0
 
mcixCommented:
Looks like I am too late...
0
 
mbarronAuthor Commented:
I did not realize that VBA and VB were so close.
I found I had little fix up to do.
All the best and many thank to you and mcix
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now