Prev: Problems with Error 3021 - No Current Record
Next: Run time error 4003→what can I do ?
From: GS on 15 Mar 2010 15:27
on 3/15/2010, Dee Earley supposed :
> On 15/03/2010 01:07, freedom wrote:
>> I wrote a code in VB6：
>> ~~
>> Set xlapp = CreateObject("excel.application")
>> xlapp.Visible = True
>> Set xlbook = xlapp.workbooks.open("C:\a.xls")
>> Set xlsheet = xlbook.worksheets(1)
>> xlsheet.Activate
>> ~~
>> It can run in my computer.
>> When I used the VB program and excel file in other computer, it show a
>> message：
>> ~~
>> Run time error 40036
>> Application-defined or Object-defined error
>> ~~
>>
>> I don't know what can I do.
>> I need use the VB program and the excel file in any other computers.(the
>> excel file have some VBA program).
>>
>> I had made the VB program to .exe file. I think maybe the problem is other
>> computer doesn't install VB6 ?? I made the excel file in excel 2003,but it
>> can't run in excel 2007 ??
>>
>> thanks a lot
>
> You could try asking an Excel newsgroup as it is Excel that is reporting the
> error.
> Do you know which line is triggering it?

If the problem lies with your Excel file's VBA then I reiterate Dee's
advice. Since you're trying to automate Excel from VB6, then there's a
few possibilities as to why you may be getting this error from your VB6
app.
1st: Is Excel installed on the computer? If not installed then you
can't set an object ref to it.

2nd: Does the file "C:\a.xls" exist on that machine? If not then you
can't set an object ref to it.

I recommend that you test for these before trying to set any refs. That
way you can nicely notify the user the needed components are missing
and/or gracefully abort startup.

Example:
Sub Main()
Dim CanStart As Boolean
Dim xlApp As Excel.Application
Dim wkbApp As Excel.Workbook
Dim wksApp As Excel.Worksheet

'Attempt to start an instance of Excel.
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
On Error GoTo 0
'Proceed according to the result of the test.
If Not xlApp Is Nothing Then
'Check if file exists
If bFileExists("C:\a.xls") Then
CanStart = True
With xlApp
Set wkbAPP = .Workbooks.Open("C:\a.xls")
.WindowState = xlMaximized
.Visible = True
.UserControl = True 'if allowing full user access
End With 'xlApp
With wkbAPP
Set wksApp = .Sheets(1) 'if object ref required
wksApp.Activate
'.Sheets(1).Activate 'use if object ref NOT required
.RunAutoMacros xlAutoOpen 'if Auto_Open() procedure used
instead of Workbook_Open event
End With 'wkbApp
End If
Else
NotifyAutomationFailure 'Excel not installed
End If
If Not CanStart Then NotifyStartupFailure 'needed components missing
End Sub

**The reusable function to test if a file exists:**
Function bFileExists(ByVal sFileName As String) As Boolean
On Error Resume Next 'if sFileName doesn't exist
bFileExists = (Dir$(sFileName) <> "") 'return value is the result
End Function 'bFileExists()

**The notification procedures:**
Sub NotifyAutomationFailure()
Dim sMsg As String
sMsg = "This application requires Excel to be installed on your
computer."
sMsg = sMsg & vbCrLf
sMsg = sMsg & "Excel failed to start. This application can not
continue!"
MsgBox sMsg, vbCritical, "Startup Failure!"
End Sub

Sub NotifyStartupFailure()
Dim sMsg As String
sMsg = "A file needed by this application can't be found. " & vbCrLf
& vbCrLf & "You must reinstall this program to use it."
MsgBox sMsg, vbCritical, "Startup Failure"
End Sub


HTH
--
Garry


 | 
Pages: 1
Prev: Problems with Error 3021 - No Current Record
Next: Run time error 4003→what can I do ?