Unable to get the Open property of the Workbooks class

Hello,

I have this function that works in other scripts but for this one it seems
to be having trouble. Here's the function...
Function WorkbookOpen(ByRef WorkBookName, LastUpdate, ReadOnly)

If IsObject(WorkBookName) = True Then
If Not WorkBookName Is Nothing Then
Set WorkbookOpen = objExcel.Workbooks(WorkBookName)
End If
Else
Dim fso, f1: Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(WorkBookName) Then
Set WorkbookOpen = Nothing
Subject = "Data Not Copied."
Message = WorkbookName & " Not Found."
Call SendMsg(objScript, Owner, "", Subject, Message)
Else
Set f1 = fso.GetFile(WorkBookName)
If LastUpdate = "" Then
If ReadOnly = "" Then
Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName)
Else
Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName,,xlReadOnly)
End If
Else
If DateValue(f1.DateLastModified) < DateValue(LastUpdate) Then
Set WorkbookOpen = Nothing
Subject = "Data Not Copied."
Message = WorkbookName & " Not Updated Since Last Refresh."
Call SendMsg(objScript, Owner, "", Subject, Message)
Else
If ReadOnly = "" Then
Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName)
Else
Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName,,xlReadOnly)
End If
End If
End If
End If
End If

Set fso = Nothing
set f1 = Nothing

End Function

When it gets to the Set WorkbookOpen = objExcel.Workbooks.Open
(WorkBookName) I get the error 1004 - Unable to get the Open property of the
Workbooks class. But as you can see in the function I already verify that
the workbook exists via FSO. Has anyone had this kind of trouble? I can
open the workbook manually with Excel, so I do not think the workbook is
corrupted. Could there be a setting in the workbook that causes this symptom?

Thank you in advance for any assistance.

Regards, Dean.

in your dim statement do you have
Dim objExcel as Object, WorkbookOpen as Object
or is it
Dim objExcel as Excel.Application, WorkbookOpen as Excel.Workbook

I think if you have the first it can cause problems when using methods that
are unique to the Excel object model. It is better to explicitly define what
kind of objects your variables are.

"Dean Hinson" wrote:

> Hello,
>
> I have this function that works in other scripts but for this one it seems
> to be having trouble. Here's the function...
> Function WorkbookOpen(ByRef WorkBookName, LastUpdate, ReadOnly)
>
> If IsObject(WorkBookName) = True Then
> If Not WorkBookName Is Nothing Then
> Set WorkbookOpen = objExcel.Workbooks(WorkBookName)
> End If
> Else
> Dim fso, f1: Set fso = CreateObject("Scripting.FileSystemObject")
> If Not fso.FileExists(WorkBookName) Then
> Set WorkbookOpen = Nothing
> Subject = "Data Not Copied."
> Message = WorkbookName & " Not Found."
> Call SendMsg(objScript, Owner, "", Subject, Message)
> Else
> Set f1 = fso.GetFile(WorkBookName)
> If LastUpdate = "" Then
> If ReadOnly = "" Then
> Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName)
> Else
> Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName,,xlReadOnly)
> End If
> Else
> If DateValue(f1.DateLastModified) < DateValue(LastUpdate) Then
> Set WorkbookOpen = Nothing
> Subject = "Data Not Copied."
> Message = WorkbookName & " Not Updated Since Last Refresh."
> Call SendMsg(objScript, Owner, "", Subject, Message)
> Else
> If ReadOnly = "" Then
> Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName)
> Else
> Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName,,xlReadOnly)
> End If
> End If
> End If
> End If
> End If
>
> Set fso = Nothing
> set f1 = Nothing
>
> End Function
>
> When it gets to the Set WorkbookOpen = objExcel.Workbooks.Open
> (WorkBookName) I get the error 1004 - Unable to get the Open property of the
> Workbooks class. But as you can see in the function I already verify that
> the workbook exists via FSO. Has anyone had this kind of trouble? I can
> open the workbook manually with Excel, so I do not think the workbook is
> corrupted. Could there be a setting in the workbook that causes this symptom?
>
> Thank you in advance for any assistance.
>
> Regards, Dean.

either:
there's space where I'm not expecting one....
try:
Set WorkbookOpen = objExcel.Workbooks.Open(WorkBookName)

or:
you are using the constant xlReadOnly iso it's value 3...

have you defined that constant somewhere
or do you have a reference to Excel Object Library?


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dean Hinson wrote :

> Hello,
>
> I have this function that works in other scripts but for this one it
> seems to be having trouble. Here's the function...
> Function WorkbookOpen(ByRef WorkBookName, LastUpdate, ReadOnly)
>
> If IsObject(WorkBookName) = True Then
> If Not WorkBookName Is Nothing Then
> Set WorkbookOpen = objExcel.Workbooks(WorkBookName)
> End If
> Else
> Dim fso, f1: Set fso = CreateObject("Scripting.FileSystemObject")
> If Not fso.FileExists(WorkBookName) Then
> Set WorkbookOpen = Nothing
> Subject = "Data Not Copied."
> Message = WorkbookName & " Not Found."
> Call SendMsg(objScript, Owner, "", Subject, Message)
> Else
> Set f1 = fso.GetFile(WorkBookName)
> If LastUpdate = "" Then
> If ReadOnly = "" Then
> Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName)
> Else
> Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName,,xlReadOnly)
> End If
> Else
> If DateValue(f1.DateLastModified) < DateValue(LastUpdate) Then
> Set WorkbookOpen = Nothing
> Subject = "Data Not Copied."
> Message = WorkbookName & " Not Updated Since Last Refresh."
> Call SendMsg(objScript, Owner, "", Subject, Message)
> Else
> If ReadOnly = "" Then
> Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName)
> Else
> Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName,,xlReadOnly)
> End If
> End If
> End If
> End If
> End If
>
> Set fso = Nothing
> set f1 = Nothing
>
> End Function
>
> When it gets to the Set WorkbookOpen = objExcel.Workbooks.Open
> (WorkBookName) I get the error 1004 - Unable to get the Open property
> of the Workbooks class. But as you can see in the function I already
> verify that the workbook exists via FSO. Has anyone had this kind of
> trouble? I can open the workbook manually with Excel, so I do not
> think the workbook is corrupted. Could there be a setting in the
> workbook that causes this symptom?
>
> Thank you in advance for any assistance.
>
> Regards, Dean.

I am using VB script and have objExcel defined like this...
Set objExcel = CreateObject("Excel.Application")
Also WorkbookOpen is a Function I created in the script, not a method of
Excel.

This code works with other scripts and workbooks but for some reason, this
one is the exception.


"K Dales" wrote:

> in your dim statement do you have
> Dim objExcel as Object, WorkbookOpen as Object
> or is it
> Dim objExcel as Excel.Application, WorkbookOpen as Excel.Workbook
>
> I think if you have the first it can cause problems when using methods that
> are unique to the Excel object model. It is better to explicitly define what
> kind of objects your variables are.
>
> "Dean Hinson" wrote:
>
> > Hello,
> >
> > I have this function that works in other scripts but for this one it seems
> > to be having trouble. Here's the function...
> > Function WorkbookOpen(ByRef WorkBookName, LastUpdate, ReadOnly)
> >
> > If IsObject(WorkBookName) = True Then
> > If Not WorkBookName Is Nothing Then
> > Set WorkbookOpen = objExcel.Workbooks(WorkBookName)
> > End If
> > Else
> > Dim fso, f1: Set fso = CreateObject("Scripting.FileSystemObject")
> > If Not fso.FileExists(WorkBookName) Then
> > Set WorkbookOpen = Nothing
> > Subject = "Data Not Copied."
> > Message = WorkbookName & " Not Found."
> > Call SendMsg(objScript, Owner, "", Subject, Message)
> > Else
> > Set f1 = fso.GetFile(WorkBookName)
> > If LastUpdate = "" Then
> > If ReadOnly = "" Then
> > Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName)
> > Else
> > Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName,,xlReadOnly)
> > End If
> > Else
> > If DateValue(f1.DateLastModified) < DateValue(LastUpdate) Then
> > Set WorkbookOpen = Nothing
> > Subject = "Data Not Copied."
> > Message = WorkbookName & " Not Updated Since Last Refresh."
> > Call SendMsg(objScript, Owner, "", Subject, Message)
> > Else
> > If ReadOnly = "" Then
> > Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName)
> > Else
> > Set WorkbookOpen = objExcel.Workbooks.Open (WorkBookName,,xlReadOnly)
> > End If
> > End If
> > End If
> > End If
> > End If
> >
> > Set fso = Nothing
> > set f1 = Nothing
> >
> > End Function
> >
> > When it gets to the Set WorkbookOpen = objExcel.Workbooks.Open
> > (WorkBookName) I get the error 1004 - Unable to get the Open property of the
> > Workbooks class. But as you can see in the function I already verify that
> > the workbook exists via FSO. Has anyone had this kind of trouble? I can
> > open the workbook manually with Excel, so I do not think the workbook is
> > corrupted. Could there be a setting in the workbook that causes this symptom?
> >
> > Thank you in advance for any assistance.
> >
> > Regards, Dean.