?
Solved

VBA using excel

Posted on 2004-12-01
9
Medium Priority
?
331 Views
Last Modified: 2010-05-18
Urgent:
I am running VB6 with SQL 7 as a backend on OS 2000/office 2002.  I use this piece of code to open excel.

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

    Set oXL = CreateObject("Excel.Application")
    Set oWB = oXL.Workbooks.Open(lblImportEnrollmentFile.Caption)   ' Get a new workbook.
    Set oSheet = oWB.ActiveSheet

lngBlankRow = oSheet.Cells.SpecialCells(xlCellTypeLastCell).Row      'Finds first blank row in Column A

I have to distribute my project to a Windows 98 machine using office 97.  
when i setup the VB application on the 98 machine i get an error :

"This program has performed an illegal operation and will be shut down.  if problem persists, contact the program vendor."


What does this mean?  Is there an issue with Excel not being able to open this way.  

0
Comment
Question by:JoannieJefferson
9 Comments
 
LVL 6

Expert Comment

by:wimthepimscake
ID: 12715774
Hi JoannieJefferson,

take notice that office 97 is using another reference library than office 2000/2002 so you should also deploy this.
but actually this is a f***ed up error message and reminds me why win 98 sucked so much :-)

Wimthepimscake
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12715788
No, your code should work fine. What else is entailed in the "VB application"?
0
 

Author Comment

by:JoannieJefferson
ID: 12715832
Wimthepimscake,

When i look in  Project/References in vb to add office 97 reference its not there.

I am new at this.....how and where do i find this reference.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:RichW
ID: 12715972
The reference would be there if you have Office 97 installed on your machine.  Wimthepimscake is correct.  Office 97 was VBA, and Office 2002 is VB 6.  They are two totally different animals.

If your Win 98 machines are running Office 97, I would suggest installing Office 97 on your machine and using that library instead.

RichW

0
 
LVL 8

Accepted Solution

by:
ampapa earned 2000 total points
ID: 12715999
By late binding which is what you are trying to use in your application it should be independent of the Excel version you are trying to run it on.

Try changing:
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

    Set oXL = CreateObject("Excel.Application")
    Set oWB = oXL.Workbooks.Open(lblImportEnrollmentFile.Caption)   ' Get a new workbook.
    Set oSheet = oWB.ActiveSheet

To:

Dim oXL As object
Dim oWB As object
Dim oSheet As object

    Set oXL = CreateObject("Excel.Application")
    Set oWB = oXL.Workbooks.Open(lblImportEnrollmentFile.Caption)   ' Get a new workbook.
    Set oSheet = oWB.ActiveSheet
0
 

Author Comment

by:JoannieJefferson
ID: 12716042
That's it. And this is the section that it errors on.  

Set oXL = CreateObject("Excel.Application")
    Set oWB = oXL.Workbooks.Open(lblImportEnrollmentFile.Caption)   ' Get a new workbook.
    Set oSheet = oWB.ActiveSheet
0
 

Author Comment

by:JoannieJefferson
ID: 12716239
in addition to my question....

I have an excel template that was created in office 2002 and is packaged with the setup.  
I also get an error when it trys to open the template.  How can I work around this?
0
 
LVL 8

Expert Comment

by:ampapa
ID: 12716402
Glad it worked and that I could help.

Excel 97 probably won't be able to open a 2002 template just a guess unless you can make it backward compatible? I don't use templates so am unfamiliar with the territory...
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12716497
if you are trying to install this app on a PC that does NOT have at least Office 2002 installed, then any attempt to open the excel template (which was created with Office 2002) is ddoomed to failure.  A genearl guidleine when developing applications that will be used on multiple versions of some underlying software (be it the Operating system, or some other component) is to ALWAYS, without exception, use the LOWEST COMMON DENOMINATOR on your development box.  That measn that if you want your application to be able to run successfully on Windows 98 or newer, using Office 97 or newer, then your development box MUST be configured for Windows 98, and Office 97.

If you don't do that, then you are almost certainly going to fail when attempting to run your application on a version of the underlying software that is EARLIER (OLDER) than that on which you developed the application.  Microsoft has generally been pretyy godd about being 'backward' compatible - that is a application developed on an OLDER platform, will usually run under the newer version, but NOT THE OTHER WAY AROUND (newer software running on older platform).

AW
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

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.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

571 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