Debugging errors for macro on for other users

[yousafkhan1976]

I have created a Macro as follows to create a web query and loop through the alphabet to append the data of multiple queries into a worksheet. The macro is connected to a button on one of the worksheets in my Workbook.

Issue: I can run it on my computer (Trust Center settings set to Macros - Disable all macros with Notification, External Content - Prompt user for both Data Connections and Workbook Links)

When another user opens the file on their computer and try running the macro by clicking the button, a debug error pops up (Compile Error: Can't find project or library) at the red text below. I cannot figure out why as it is a standard function in Excel.

Sub RefreshWebQueries()
    Dim Alphabet, CurrentAlpha As String
    Dim r, Row, Column, Count As Integer
    Dim rngRowB As Range
    Dim shtRolesCostList As Worksheet
    Dim qtRolesGradesSalaries, qtable As QueryTable
    Dim CN As Variant
    Dim myVal As String


    Row = 1
    Column = 2
    Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
    
    Set shtRolesCostList = ThisWorkbook.Sheets("Roles Cost List")
    shtRolesCostList.Activate
    
    'Delete old data from Roles Cost List sheet
    shtRolesCostList.Range("B:E").ClearContents
    
    
    'Create a new Connection and Web Query to fetch fresh data by looping through the full alphabet
    
    For Count = 1 To Len(Alphabet)
          
        CurrentAlpha = [B][COLOR=#ff0000]Mid[/COLOR][/B](Alphabet, Count, 1)
        
        With shtRolesCostList.QueryTables.Add(Connection:= _
            "URL;http://OURURL/restricttocategory=" + CurrentAlpha _
            , Destination:=shtRolesCostList.Range("$B" + CStr(Row)))
            .Name = "RolesGradesSalaries"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = False
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "8"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        
        'Update next row to apend new data from web query with next letter of the alphabet
        Row = Application.WorksheetFunction.CountA(Range("B:B")) + 1
        
    Next Count
    
    'Delete connections and web queries
    For Each CN In ThisWorkbook.Connections
        'conCount = ThisWorkbook.Connections.Count
        If CN.Name = "GradesSalaries" Then
            conCount = ThisWorkbook.Connections.Count
        Else:
            CN.Delete
        End If
        
    Next CN


    For Each qtable In shtRolesCostList.QueryTables
        qtable.Delete
    Next qtable
    
    'Format the results
    Call ThisWorkbook.FileLoadFormatter(shtRolesCostList)
    ThisWorkbook.Sheets("Resources").Activate
    
End Sub

 

[Waimea]

Are you using the same versions of Microsoft Excel? I have had this happen to me also recently!

I didn't find a solution for it!

 

[yousafkhan1976]

I did manage to find a fix. Turns out that the Microsoft Office 16 library was included in my VBA settings, but other users on Office 13 did not. I followed the instructions in the link below and it fixed the issue.

https://www.barcodefaq.com/knowledge-base/vba-compile-error/