Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB creating Excel Worksheet

Posted on 1998-09-21
5
Medium Priority
?
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 800 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

636 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