Solved

VB creating Excel Worksheet

Posted on 1998-09-21
5
230 Views
Last Modified: 2010-04-30
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
Comment
Question by:mbarron
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:mcix
ID: 1435831
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
 

Accepted Solution

by:
vplusplus earned 200 total points
ID: 1435832
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
 

Author Comment

by:mbarron
ID: 1435833
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
 
LVL 4

Expert Comment

by:mcix
ID: 1435834
Looks like I am too late...
0
 

Author Comment

by:mbarron
ID: 1435835
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now