Desperate! RESOLVED

Printable View

Dec 7th, 2002, 07:31 PM
rodman70

Desperate! *RESOLVED*

Will this piece of code not open an excel spreadsheet, write some letters and then close it??
Private Sub Command1_Click()

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets.Add

xlsheet.Cells(3, 1).Value = "ASDFASDFA"
xlsheet.SaveAs "C:\Documents and Settings\Chris Wilson\Desktop\temp.xls"
xlapp.Quit
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

End Sub

When I run it on my 98 machine it crashes at the xlsheet.cells line. I run it on my XP machine and it runs fine?? Anyone got any ideas?
Dec 7th, 2002, 07:34 PM
Armbruster

Re: Desperate!
Quote:

Originally posted by rodman70
Will this piece of code not open an excel spreadsheet, write some letters and then close it??
Private Sub Command1_Click()

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets.Add

xlsheet.Cells(3, 1).Value = "ASDFASDFA"
xlsheet.SaveAs "C:\Documents and Settings\Chris Wilson\Desktop\temp.xls"
xlapp.Quit
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

End Sub

When I run it on my 98 machine it crashes at the xlsheet.cells line. I run it on my XP machine and it runs fine?? Anyone got any ideas?

It probably doesn't like setting a value to a string (type mismatch)
try . . .
VB Code:
```
xlsheet.Cells(3, 1).Text = "ASDFASDFA"
```
. . . instead
Dec 7th, 2002, 07:36 PM
rodman70

Nope, it is giving me an automation error at this point.??
Dec 7th, 2002, 07:45 PM
Armbruster

1 Attachment(s)

Worked on my pc. Do you have a reference to Excel object library?

http://www.vbforums.com/attachment.p...postid=1293554
Dec 7th, 2002, 07:48 PM
rodman70

I do, I have reference to 9.0. Is there anyway to track down a .dll file that is out of date or could be causing this?? I used the setup.lst all of the .dll's are there but some are different version that my XP box. One other thing, when it ran installation it said it could not update msvcrt.dll because it was in use by Windows. Could this be it, it was not referenced in the setup.lst??
Dec 7th, 2002, 07:49 PM
Armbruster

Re: Desperate!

Quote:

When I run it on my 98 machine it crashes at the xlsheet.cells line. I run it on my XP machine and it runs fine?? Anyone got any ideas?

I didn't even read that right the first time!!

It seems obvious now. Do you have the same versions of Excel on both machines? If you reference Excel 10 library on the XP box (office xp) but have Excel 8 (office 97) on the 98 box, you will get an automation error. Try creating an install program or copy the excel library object from the 98 box and use it as your reference.
Dec 7th, 2002, 07:52 PM
rodman70

Same version on both machines, it won't even run at all on the 98 box. That was my first thought, to build it on that box. I am writing this as a utility for work and we use 98 boxes. This is when I decided to just write another smaller program that I also posted here just to open, write some small data and close excel. It wouldn't even work, crashed at the xlsheet reference. Someone else said they tried it and it worked for them??
Dec 7th, 2002, 07:53 PM
Armbruster

Do you have VB on the 98 box. If you open the project on the 98 box, I'm sure you will see a "missing" in the references. Copy Excel.exe from the 98 box to your xp box (don't overwrite your existing one!) and create a reference to that version.
Dec 7th, 2002, 07:56 PM
rodman70

I took the code straight over to the 98 box and it will not run. I am referencing 9.0 should I be using 10.0 like in your example?
Dec 7th, 2002, 08:03 PM
Armbruster

I think version 9 is office 2000 - is that the version of office you are running?

I have about 20 different msvcrt.dll's on my pc and the one in system32 is not the most recent - go figure?

You said you took the code straight to the 98 box. Do you have VB on that box? Are you able to compile it on that box? Will it run from the ide on the 98 box?
Dec 7th, 2002, 08:05 PM
rodman70

Yes I am running Office 2000, and yes I have VB on the 98 box. It will not run from the IDE, same error. It is almost as if the xlsheet is not set, it gives me runtime error '-214.......' Automation Error??
Dec 7th, 2002, 08:07 PM
rodman70

I also ran it on my Nephews 95 box and same error at the same point, he does not have VB on his box. Think it could be a missing or .dll, one that is not on the setup.lst?
Dec 7th, 2002, 08:15 PM
Armbruster

It would seem that for some reason it is failing to create the object on the 98 box and referencing it is then causing the automation error. Are you able to run excel on the 98 box?

Quote:

Automation Error Using Excel Object in Visual Basic Procedure
Last reviewed: September 2, 1997
Article ID: Q134835
The information in this article applies to:
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Standard and Professional Editions of Microsoft Visual Basic Programming System for Windows, version 3.0

SYMPTOMS
When you use a Visual Basic application to create an OLE Automation object using Microsoft Excel, you may receive the following error message when you attempt to access the object:

OLE Automation error

CAUSE
This problem occurs when you access a Microsoft Excel OLE Automation object in a Visual Basic procedure while Microsoft Excel is closed. If, for example, you create a reference for a worksheet object using the CreateObject function, and you create a reference for another Microsoft Excel object using the GetObject function in your procedure, if you then set the Microsoft Excel worksheet object (Excel.Sheet) equal to nothing, you receive the OLE Automation error message if you then try to access the other object.

This problem occurs because setting a Microsoft Excel object that was created using the CreateObject function equal to Nothing closes Microsoft Excel, even if your Visual Basic procedure still has a reference to another Microsoft Excel object.

For example, you receive this error message when you run the following code in Microsoft Visual Basic:

Dim xlSheet As Object
Dim xlApp As Object
Set xlSheet = CreateObject("Excel.Sheet")
MsgBox xlSheet.Application.Name
Set xlApp = GetObject(, "Excel.Application")
MsgBox xlApp.Name
Set xlSheet = Nothing
MsgBox xlApp.Name

You receive this error message because the statement "Set xlSheet = Nothing" closes Microsoft Excel, and the "MsgBox xlApp.Name" statement that follows in the procedure attempts to access the Microsoft Excel application object again.
Note that in the above example, if you set the Microsoft Excel application object equal to nothing (Set xlApp = Nothing), you do not receive an error message if you then access the Microsoft Excel worksheet object (xlSheet) in the procedure. Additionally, you do not receive this error message if Microsoft Excel is running when you run this macro because, in this case, the CreateObject function starts another instance of Microsoft Excel.

RESOLUTION
To avoid this behavior in a Visual Basic procedure, do not set the value of a Microsoft Excel object that was created using the CreateObject function equal to Nothing until you are done accessing all Microsoft Excel OLE Automation objects in the procedure.

--------------------------------------------------------------------------------

Additional query words: 7.00 5.00 5.00c
Keywords : AutoGnrl kbinterop kbprg xlwin
Version : 5.00 5.00c 7.00
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.
Dec 7th, 2002, 08:19 PM
rodman70

I can use Excel with no problem, the program will even allow me to open and it "acts" as if it is writing data (which it does not) then closes just fine. When it meets a line that references the sheet it crashes!

Is it maybe taking longer to create the object on 98 box?

maybe try something like . . .
VB Code:
Private Sub Command1_Click()
    Dim xlapp As Excel.Application
    Dim xlbook As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Set xlapp = New Excel.Application
    Set xlbook = xlapp.Workbooks.Add
    Set xlsheet = xlbook.Worksheets.Add
End Sub
 
Private Sub Command2_Click()
 
xlsheet.Cells(3, 1).Value = "ASDFASDFA"
xlsheet.SaveAs "C:\Documents and Settings\carl\Desktop\temp.xls"
xlapp.Quit
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
 
End Sub
Click command1 wait a few seconds, then click command2 just to see if it is taking longer to create the object. In theory the object SHOULD be created immediatly, but it is worth a try.

Dec 8th, 2002, 12:43 PM
rodman70

Thanks everyone for the help. I got it resolved. I had to reinstall Windows 98, apparently some of the .dll files that XP put into my P&D were not compatible with the 98 box. I reinstalled and it runs fine, I am going to build it on that box now!!!

Desperate! *RESOLVED*

Desperate! RESOLVED