Debugging errors for macro on for other users

yousafkhan1976

New Member
Joined
Feb 24, 2019
Messages
4
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.

Code:
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,354
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top