Compile Error: Can't find project or library (1 computer has the error, another does not?)

[jonvanwyk]

I have a request form that has been used by two departments for the past two years. After we went through an upgrade from Office 2007 to Office 2010, Department A is still able to use the form, while Department B receives the message "Compile Error: Can't find project or library". I have attached to this post a copy of the form, and a screenshot of the error being received by Department B. The form itself is much more complicated, but this is a simplified version that saves the PDF to your desktop so that it can be tested by anyone. If I can fix the error on this one, I should be able to incorporate the solution into the more complicated version.

Thanks in advance for any help that comes my way!

Screenshot of the Compile Error >>> Compile Error.jpg

Sub sendpdf01()

    Dim FileName As String
    Dim UserDesktop As String
    Dim WshShell As Object
    
      Set WshShell = CreateObject("WScript.Shell")
      
      UserDesktop = WshShell.SpecialFolders(4) & "\"
      
        FileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
           UserDesktop & Cells(2, 2).Value & " - " & Cells(6, 2).Value & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False
        Range("A1:D28").Select
        
        MsgBox "This request has been saved to your desktop."
    
End Sub

[jaslake]

Hi jonvanwyk
The error you're receiving usually indicates a missing reference. I'd guess it's the Calendar Control as it's not included with Excel 2010. On the offending machine in VBA go to Tools ----> References and see if any are missing. If it is the Calendar Control you can download it from several sites. You may wish to explore options to Calendar Control...there are several good ones available.

[jonvanwyk]

See VBA\Tools\References Screenshot >>> Calendar Control Screenshot.jpg

@jaslake: Thank you for your reply. It has been harder than normal to get help on this question. I did as you asked, except it was not on the offending machine, but on the machine on which the form works fine. It appears that Calendar Control is not on this machine either, and does not seem to affect anything in this particular form. Thoughts?

[jaslake]

Hi jonvanwyk

I'm unable to duplicate the error for either file in Excel 2007 or Excel 2010. I deleted the Reference to Calendar Control and both ran fine. Do you have ANY References on either machine that are marked MISSING?

[jonvanwyk]

I also cannot seem to duplicate the error, except when I am using a computer in the other department. Both departments have the same excel add-ins. I am completely stumped.

[Leith Ross]

Hello Dutch,

Check that the offending machine has VBA macros enabled and the Trust Centers are setup correctly for macros. The Left function is part of the VBA library. To check that this in fact the problem, prefix the Left with VBA (see the code below). If after the change, it errors at a different point, then it is confirmed that VBA library is not loading or loading incorrectly.

Sub sendpdf01()

    Dim FileName As String
    Dim UserDesktop As String
    Dim WshShell As Object
    
      Set WshShell = CreateObject("WScript.Shell")
      
      UserDesktop = WshShell.SpecialFolders(4) & "\"
      
        FileName = VBA.Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
           UserDesktop & Cells(2, 2).Value & " - " & Cells(6, 2).Value & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False
        Range("A1:D28").Select
        
        MsgBox "This request has been saved to your desktop."
    
End Sub

[jonvanwyk]

@Leith...Thanks for dropping in :-) I will check that this morning when the other department opens. Is it possible for all macros to be enabled, and the document to be trusted, and to still have issues with such a thing? If so, how does one fix that problem (and how likely would it be the the problem would exist on every machine in the other department)?

I should note that the actual code is a bit more complicated. Below is a sample of it in which the compile error first started with the [A50]. Someone recommended that I change the [A50] to "A50". When I did so, the form stopped working on the machines in our department. When used at the other department, it also did not work, but the error jumped from [A50] to the "Left(" error you see now.

 'Send email request to Ames PD #1
    If FileName <> "" And ([A50] = True) And _
  ([A60] = True Or [A61] = True Or [A62] = True Or [A63] = True Or [A64] = True Or [A65] = True Or [A69] = True Or [A70] = True) Then
        RDB_Mail_PDF_Outlook FileName, "cspencer@city.ames.ia.us", "Story County Attorney's Office - Records Request", _
                             "Please see the attached records dissemination request." _
                           & vbNewLine & vbNewLine & "-Story County Attorney's Office", True

I have attached the unadulterated form to this reply. It contains real email addresses in it, so I trust anyone looking at it will be careful in using it (though I am sure it wouldn't work for anyone else anyway given that the file name paths are specific to our network).

Records Request Form Excel 2003 Workbook.xls

[jonvanwyk]

@Leith: Okay, well...I attempted your code at the other department on the simplified version. I received no errors and the form functioned as intended. Should I just add "VBA.Left" whereever that code is on the more complicated form and call it good (if it also makes the the real form work properly)?

[snb]

PS.

Why do you use the variable filename ?
there's more robust code for that:

FileName = CreateObject("scripting.filesystemobject").getbasename(ThisWorkbook.FullName)

You can avoid the 'Left' issue.

To 'print' the PDF I think this will suffice:

ActiveSheet.ExportAsFixedFormat xlTypePDF, CreateObject("WScript.Shell").SpecialFolders(4) & "\" & Cells(2, 2).Value & " - " & Cells(6, 2).Value & ".pdf"

[jonvanwyk]

I have attached additional tested files below.

"Records Request Form Test A.xls" works perfectly on computers in my department. When I attempt to use it in the other department, I receive the error seen in the screenshot below (Compile Error 1).

Although perhaps misconceived, "Records Request Form Test B.xls" attemps to accomodate Compile Error 1 by changing, for example, [A50] to "A50". When this is done, the form no longer works in our department, and when attempted in the other department, I receive the error seen in the screenshot attached below (Compile Error 2). Since I added "VBA." to "Left", the error jumps to "Environ" found at the bottom of Module 1 under the function "Function RDB_Create_PDF".

Thoughts? I really appreciate your guys' help. I still do not know why this is happening. :|

[jonvanwyk]

@snb: Why do I use ANY code? Because it is all cannibalized from various examples throughout the web to create a form that meets my needs. I am sure there is a lot of code I use that could be made more efficient, but at my skill level I have a tendancey to follow the axiom "If it ain't broke, don't fix it."

Until the other department underwent their Office 2010 upgrade, the form worked great in both departments. After they went through their upgrade, it stopped working on their computers. Our department went through the same software upgrade shortly after, yet we have no issues with the form working perfectly. I am really confused.

[snb]

I think you could create a new Excel document on the computer that produces the errors and copy your code into it.
It might work.
Although you may think you work with identical software, sometimes 'updates' can interfere.

[jonvanwyk]

@snb. I can try that, though I am not excited about it. The copying and pasting of the VBA is easy enough, but that means I have to create the spreadsheet all over again. *sigh I wonder why the bracketed cell references are giving an error.

[snb]

The copying and pasting of the VBA is easy enough, but that means I have to create the spreadsheet all over again

I doubt that.

What happens if you replace [A50] by Range("A50")

[jonvanwyk]

@snb: That is the only difference between Test A and Test B forms (attached to my previous reply). Test A has it as [A50] and Test B has it as "A50". In Test A, the form works perfectly in our department while Test B does not. Neither Test A nor Test B works at the other department, though they get different errors. (Read reply #10 ...it goes into more detail.)

[snb]

Well, back to lesson 1 in VBA:

You can't use
If ("A50" = True) And

You can use
If Range("A50").value = True And

But you'd better use a reference to the sheet also:

with sheets("sheet_1")
   If .range("A50").value = True And .Range("A60").Value = True Or .range("A61").value = True Or .Range("A62").Value = True Or .Range("A63").Value = True Or .Range("A64").Value = True Or .Range("A65").Value = True Or .Range("A69").Value = True Or .Range("A70").Value = True Then 
End With

And you'd better avoid sheetnames that can be mixed with other things in VBA, e.g. a Usrform with a name "Form1"
Caal them "Sheet_1", "Sheet_2", etc.

[jonvanwyk]

I skipped "lesson 1"...went straight to "trial by fire" haha. I will create a new document labeled Test C and get back to everyone.

[jonvanwyk]

Thanks snb. I have made those changes (see attached file). I am going to test it over at the other department in a minute to see if that fixes the issue they were having. I get back to you.

[jonvanwyk]

The new and improved "Records Request Form Test C" works perfectly in our department, but gets the error below when used in the other department.

Function RDB_Create_PDF(Myvar As Object, FixedFilePathName As String, _
                        OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
    Dim FileFormatstr As String
    Dim Fname As Variant

    'Test If the Microsoft Add-in is installed
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

compileerror3.jpg

[jonvanwyk]

Further research suggests that the "MISSING: Microsoft Office Calendar Control 2007" on the offending computers might be the issue? The machines in our department (on which the form words perfectly) all have "Microsoft Calendar Control 9.0".

MISSING - Microsoft Calendar Control 2007.jpg

[jaslake]

Hi jonvanwyk
YES!!! I believe I mentioned this previously.

You MAY be able to copy the Calendar Control from another machine...don't know. You CAN, however, download a copy and do the install. See this link and follow the instructions carefully.
http://www.excelforum.com/excel-prog...r-library.html

If you're distributing see this link for an alternative to Calendar Control
http://www.excelforum.com/excel-prog...r-control.html

You may try simply unchecking the missing control...see if your code runs...if not do the download.

[Leith Ross]

Hello Dutch,

I have been doing a lot of reading as to why this problem maybe occurring. At this time, I haven't found any known issues with Excel 2003, 2007, or 2010 relating to this. The Environ method is also part of VBA. You can add the VBA prefix as before with Left. This should work. You can use CTRL+F to do a find and replace through out your project if there are several of these references in your code.

Strange thing is the brackets like [A1] did not change in either 2007 or 2010 from Excel 2003. Both versions support this short hand for the Evaluate method, which is part of the Excel application and not VBA. Perhaps it is related to the network setup. I'll keep looking.

[jonvanwyk]

@jaslake: The code I seem to be having issues with does not seem like it would even need calendar control, so I am still not convinced that this is the problem. That said, the only difference between the machines in our department and the machines in the other department is that we have Microsoft Calendar Control 9.0 on our machines, and they do not.

@Leith: I will add the VBA prefix before the Environ (it only shows up twice in my code) and see if that ends the errors experienced at the other department.

[jaslake]

Hi jonvanwyk

Did you clear the MISSING item in Tools--->References to see if this resolved the issue?

[jonvanwyk]

@jaslake: Our IT department didn't want to add Calendar Control to the offending machine, and I do not have administrative rights. So I figured if I cannot make their machines look like ours, I will make my machine look like theirs. Upon removing Calendar Control 9.0 from my machine; my form still works perfectly fine. I got to thinking about how none of the code in my form should really need calendar control, and it was the only thing labled as "Missing" on their VBA References. I can only assume that their machines have a different form that they use that is causing that...but I doubt it is related to my form at this point. I have added the VBA.Environ to resolve that issue. I will test it after the other department opens to see if any other errors pop-up after that one is resolved and get back to everyone on the forum.

[jaslake]

Hi jonvanwyk

Did you clear the MISSING item in Tools--->References to see if this resolved the issue?

[snb]

The form now works in our department, but not the other department. Everyone is on Office 2010 now.

So no use to check the existence of any addin to produce PDF's.

[snb]

In the earliest versions of Excel 2007 the PDFwriter wasn't built in. You had to install that 'addin'.
The later versions contained the ThisWorkbook.ExportAsFixedFormat.

So if you are working in Excel 2007 and Excel 2010 the check whether the PDF addin has heen installed should be redundant.

If you skipped lesson 1 in VBA it might occur that the userform/sheet contains more controls than you are able to see. You could check that too.

[jonvanwyk]

@snb: The form was originally created when both departments were using Office 2007. Both departments underwent an upgrade to Office 2010. The form now works in our department, but not the other department. Everyone is on Office 2010 now.

[jonvanwyk]

Adding the VBA. to the Environ fixed the remaining errors and the form now works as intended at the other department. So, after ALL of that, all I did to get it working was to add VBA. to the "Left" and VBA. to the "Environ". Thus, I still do not know what the problem was, but the solution seems to work lol. Thanks again everyone!

[jonvanwyk]

I knew I acted too quickly in marking this thread as solved! Darn. Okay, so the form is error free, but since having changed [A50] to Range("A50") ... and so on ... the form no longer picks and chooses who to send the request to based on what is = TRUE. Instead, it sends the request to EVERYONE no matter what is clicked. Nothing like having 17 people from 5 agencies reply to your email saying "Why am I getting this?".

Thoughts?

[snb]

You made a refence to the VBA library by adding VBA. to the left side.
You could consider to add reference to the sheet you are referring to when adressing ranges.

Sheets("Sheet1").Range("A50").value is to be preferred instead of Range("A50").
You can use With ... End With to shorten your code (lesson 3 in VBA )

[jonvanwyk]

There IS only one sheet though.

[snb]

There's also only one VBA library....

[jonvanwyk]

I went back to the [A50] syntax and it seems to work fine now. Who knows.