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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Waimea

Active Member
Joined
Jun 30, 2018
Messages
449
Office Version
  1. 365
Platform
  1. Windows
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,220
Messages
5,836,067
Members
430,404
Latest member
goncaloColt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top