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
    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
  •