VB4 & Excel97/Excel 2000 ???

Here is my dilemma:

I have a VB4 application that manipulates an Excel 5 spreadsheet.

Now we are moving to Excel 97 (and very, very soon to Excel 2000).

When I install Excel 97 VB no longer works with Excel and returns an Error.

Any clue how to get this working with Excel 97/Excel 2000 w/o rewriting the whole application?

Thanks
szx248Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
What is the error that you are getting?  What does the code look like?
0
szx248Author Commented:
This is the code one which it chokes:
MyExcel.Cells(7 + t, 1).Value = DateAdd("yyyy", 1, DateAdd("m", t, CVDate(Text1)))

The Error message is 438
Object doesn't support this property or method
0
Bob LearnedCommented:
What is MyExcel dimensioned as?  How is it set?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

szx248Author Commented:
Set MyExcel = GetObject(glocationdir & "client.xlt", "excel.sheet")
MyExcel.SaveAs filename:=myfile
MyExcel.Unprotect

MyExcel.Cells(7 + t, 1).Value = DateAdd("yyyy", 1, DateAdd("m", t, CVDate(Text1)))
0
Bob LearnedCommented:
With Excel97, you .Sheet has changed to .WorkSheet.

You could also do this:

Dim MyExcel As New Excel.Application
Dim MySheet As Excel.Worksheet

Set MySheet = MyExcel.Workbooks.Open(gLocationDir & "client.xlt")
0
szx248Author Commented:
Dim MyExcel As New Excel.Application

Here I get "Invalid use of New keyword" error message

If I remove it then I get an error on this line:

Set MySheet = MyExcel.Workbooks.Open(gLocationDir & "client.xlt")

Error 91
Object doesn't support this property or method
0
Bob LearnedCommented:
Where are you running this from?  VB or Excel?
0
Bob LearnedCommented:
Did you include a reference in your project for Microsoft Excel 8.0 Object Library?
0
szx248Author Commented:
running from vb

0
szx248Author Commented:
I am using VB4 and I don't see any Rerfernce to Microsoft Excel 8.0 Object Library
0
Bob LearnedCommented:
Is there a reference to any library that resembles Office or Excel?

I haven't used anything but Office 97 for quite a while so I can't remember what the references for other versions are.
0
Bob LearnedCommented:
Are you using the GetObject statement to get an object reference to an Excel application that is already running?

My questions are pointed toward including a reference to the Microsoft Excel 8.0 Object Library, and using Automation to open an Excel application and Worksheet the most expedient way.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
szx248Author Commented:
At the end just adding
Worksheets("Client").

To
MyExcel.Cells(7 + t, 1).Value = DateAdd("yyyy", 1, DateAdd("m", t, CVDate(Text1)))

like this:
MyExcel.Worksheets("Client").Cells(7 + t, 1).Value = DateAdd("yyyy", 1, DateAdd("m", t, CVDate(Text1)))

Got it working.

let me know if u think there is something else I may be overlooking.

I am using VB4 with NO refrence to Excel 8.0 to the best that I can deturmine.

Thanks for pointing me in the right direction
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.