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.