Error: can't find project or library

[Andyjim]

Hi-
We are trying to put out a small investment system in Excel to many users.
When I run the following macro with Excel 2000 it runs fine, but when I run
it with Excel v. 2003, I get the error: Can't find project or library. I
will mark below where the error occurs. As a follow up question, will we
need to send out different macros to users depending on their version of
Excel? You guys always come through for me. Thanks in advance!

Sub PlanCa()
'Modified BevUpdate2 because it assumes
'user's filename is "UserFile.xls"
'Will also attempt to make a variable for user's file

Dim updfile As String
' Dim usrfile As String
' Dim n, p
' n = ActiveWorkbook.Name
' p = ActiveWorkbook.Path
updfile = ActiveWorkbook.Path
updfile = updfile & "\" & "devfile.xls"
' updfile = p & "\" & "devfile.xls"
' usrfile = p & "\" & n
Set usrfile = ActiveWorkbook 'HERE IS WHERE ERROR OCCURS!!!!!!!!!!
' Set updfile = updfile 'Tried to make it an object
'ERROR: REQUIRES OBJECT

'Make backup of user's file
usrfile.SaveCopyAs filename:="fxRiskMasterBackup.xls"
'Prefer "Backup" append to User's own filename,
'and save in current folder.

'Insert Filename in Filename cell
Range("Filename") = ActiveWorkbook.Name

'Copy Filename to Update file: UserFilename cell
Sheets("Lookup").Visible = True
Application.Goto Reference:="Filename"
Selection.Copy
'Workbooks.Open Filename:=updfile 'OPEN devFile
If Dir(updfile) = "" Then
MsgBox ("Devfile.xls not found. Move devfile.xls to this folder.")
Exit Sub
End If
' On Error Resume Next
' Test to see if the file is open.

If IsFileOpen(updfile) Then
' Display a message stating the file in use.
'MsgBox "File already in use!"

Else
' Display a message stating the file is not in use.
' MsgBox "File not open!"
' Open the file in Microsoft Excel.

Workbooks.Open filename:=updfile 'OPEN devFile

End If


'Workbooks.Open Filename:=updfile 'OPEN devFile
'Copy Filename to Update file: UserFilename cell
usrfile.Activate
Sheets("Lookup").Visible = True
Application.Goto Reference:="Filename"
Selection.Copy
Windows("devFile.xls").Activate
Sheets("Lookup").Visible = True
Application.Goto Reference:="userfilename"
ActiveSheet.Paste

'Copy Current Version to Update file, Old Version cell
usrfile.Activate 'WORKS!
Application.Goto Reference:="CurrentVersion"
Application.CutCopyMode = False
Selection.Copy

'Activate devFile. Can't make this happen with variable
' updfile.Activate 'ERROR: INVALID QUALIFIER
' Windows(updfile).Activate 'ERROR: SUBSCRIPT OUT OF RANGE
Windows("devFile.xls").Activate
Application.Goto Reference:="OldVersion"
ActiveSheet.Paste

'Activate Update2 macro
' Application.Run "'devFile.xls'!Update3"
' Application.Run "!Update3"
' Application.Run "'updfile'!Update3"
' Application.Run "'ActiveWorkbook'!Update2"
Application.Run "'devfile.xls'!Update2"

End Sub

 

[Phil]

Andy,

I bet you've found the answer by now, but just in case:

See here:

updfile = ActiveWorkbook.Path
Set usrfile = ActiveWorkbook 'HERE IS WHERE ERROR OCCURS!!!!!!!!!!

The MISSING bit is the property of the object ActiveWorkbook.

Coffe is not going to help if you killed a bottle of vodka, or whisky, or
schnapps or err, lemme get some more ice...

Voila.

Joseph

 

[sebastienm]

Hi,

You are assigning a Workbook object to a string variable.
So either you assign the object to an object:
dim usrfile As Workbook
set usrfile = ActiveWorkbook
debug.print usrfile.name
or you assign the name of the book to a striung variable
dim usrfile as string
usrfile = ActiveWorkbook.Name
debug.print usrfile

 

[Andyjim]

Thanks you much! Works great!

Hi,

You are assigning a Workbook object to a string variable.
So either you assign the object to an object:
dim usrfile As Workbook
set usrfile = ActiveWorkbook
debug.print usrfile.name
or you assign the name of the book to a striung variable
dim usrfile as string
usrfile = ActiveWorkbook.Name
debug.print usrfile

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>
<http://www.ready-reports.com>

 

[Tim Zych]

Check the VBE's Tools -> References and see if there are any MISSING
references in 2003. If so, they will need to be fixed or removed depending
on what they are.

 

[Tim Zych]

You are assigning a Workbook object to a string variable.

But he's not. The "Dim ursfile.." line is commented out in his post. And, he
says this works in 2000.

' -----------------
' Test 1
' -----------------
' Option Explicit
Dim s As String
Set s = ActiveWorkbook

Error is "Object required", not "Can't find project or library."

' -----------------
' Test 2
' -----------------
' Option Explicit
' Dim s As String
Set s = ActiveWorkbook

Works OK.

XL 2003

 

[sebastienm]

You're right; I mixed up with the other variable with similar name 'updfile'.

I get the same results as you did for Test1 & 2 under both XL 2003 AND XL
2000 (Win 2k machine). Both results make sense (test1: cannot use Set on a
string variable; test2 assigns an Object to an empty Variant).

Test3:
Dim s As String
s = ActiveWorkbook
Err 438: Object doesn't this property or method.
.... which makes sense too if Workbook has no default property or none that
can be cast to a string.