PDA

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



M1NDN1NJ4
09-07-2017, 04:48 PM
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.

M1NDN1NJ4
09-07-2017, 04:54 PM
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