Consulting

Results 1 to 2 of 2

Thread: Run-time error "438": Object doesn't support this property or method

  1. #1

    Run-time error "438": Object doesn't support this property or method

    Hello everyone,

    I'm trying to run a macro that was written on a Windows machine. I'm gettting Run-time Error '438'. I've tried commenting out the lines, and can get it to run, however, it doesn't come back with the correct information from the website it's pulling information from.

    Any help would be greatly appreciated.

    Code in post below.

  2. #2
    Sub getSWGOH() '
         ' Convert_Links Macro
         ' Converts 50 Members Listed into Links
         '
         '
        Dim i, iBegin, iEnd As Integer 
        Dim iDate As Date 
        Dim firstPasteColumn, lastPasteColumn As String 
        Dim urlStr As String 
        Dim formulaRow As String 
        Dim nm AsString 
        Dim NameNum As Integer 
        Dim GearName As Integer 
        Dim DateCheck As Integer 
        Dim PageName As String 
        Dim WhichPage As Integer 
        Dim ColNumber As Integer 
        Dim ColLetter As String 
         
        Application.ScreenUpdating = False 
         
        iBegin = Sheets("ControlPanel").Range("L3").Value 
        iEnd = Sheets("ControlPanel").Range("L4").Value 
        formulaRow = Sheets("ControlPanel").Range("L5").Value 
        firstPasteColumn = Sheets("ControlPanel").Range("L6").Value 
        lastPasteColumn = Sheets("ControlPanel").Range("L7").Value 
         
        i = iBegin 
        Do While i <= iEnd 
            Sheets("GearDetail").Activate 
            nameCell = "B" & i 
            urlStr = "https://swgoh.gg/u/" & Range(nameCell).Value 
             
            Sheets("Queries").Activate 
             
            On Error GoTo next_i 
             
             'next query is for unit collection
            With ActiveSheet.QueryTables.Add(Connection:= _ 
                "URL;" & urlStr & "/collection", Destination:=Range("$A$3")) 
                .Name = "collection" 
                .FieldNames = True 
                .RowNumbers = False 
                .FillAdjacentFormulas = False 
                .PreserveFormatting = True '-This is where I get an error'
                .RefreshOnFileOpen = False 
                .BackgroundQuery = True 
                .RefreshStyle = xlOverwriteCells 
                .SavePassword = False 
                .SaveData = True 
                .AdjustColumnWidth = True 
                .RefreshPeriod = 0 '-This is where I get an error'
                .WebSelectionType = xlEntirePage '-This is where I get an error'
                .WebFormatting = xlWebFormattingNone '-This is where I get an error'
                .WebPreFormattedTextToColumns = True '-This is where I get an error'
                .WebConsecutiveDelimitersAsOne = True '-This is where I get an error'
                .WebSingleBlockTextImport = False '-This is where I get an error'
                .WebDisableDateRecognition = False '-This is where I get an error'
                .WebDisableRedirections = False '-This is where I get an error'
                .Refresh BackgroundQuery:=False '-This is where I get an error'
            End With 
            ActiveWorkbook.Connections("Connection").Delete '-This is where I get an error'
             
             'First copy the formulas, then paste the values over the formulas
            Sheets("GearDetail").Activate 
            Range(firstPasteColumn & formulaRow & ":" & lastPasteColumn & formulaRow).Select 
            Selection.Copy 
            Range(firstPasteColumn & i).Select 
            Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ 
            SkipBlanks:=False, Transpose:=False 
             
            Range(firstPasteColumn & i & ":" & lastPasteColumn & i).Select 
            Selection.Copy 
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
            :=False, Transpose:=False 
            Application.CutCopyMode = False 
             
            ActiveWorkbook.Save 
             
    next_i: 
            i = i + 1 
        Loop 
         
        Application.ScreenUpdating = True 
         
         
        EndSub 
    
    
    Formatting tags added by mark007
    Last edited by SamT; 09-07-2017 at 06:17 PM. Reason: Removed Quote Tags, Added Code Formatting Tags via # icon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •