SHARE
TWEET

TSM Data Macro

Lazy-goldmaker Nov 22nd, 2016 (edited) 366 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub GetTSMData()
  2. '
  3. ' Makro1 Makro
  4. Dim URL As Variant
  5. Dim name As Variant
  6.  
  7.     URL = "TEXT;http://api.tradeskillmaster.com/v1/item/" & Range("Region").Value & "/" & Range("Realm").Value & Chr(63) & "format=csv_d&apiKey=" & Range("apiKey").Value
  8.     name = Range("realm").Value & Chr(63) & "format=csv_d&apiKey=" & Range("apiKey").Value
  9.     Worksheets("Data").Activate
  10.    
  11. '
  12.    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("$a$5"))
  13.         .name = name
  14.         .FieldNames = True
  15.         .RowNumbers = False
  16.         .FillAdjacentFormulas = False
  17.         .PreserveFormatting = True
  18.         .RefreshOnFileOpen = False
  19.         .RefreshStyle = xlInsertDeleteCells
  20.         .SavePassword = False
  21.         .SaveData = True
  22.         .AdjustColumnWidth = True
  23.         .RefreshPeriod = 0
  24.         .TextFilePromptOnRefresh = False
  25.         .TextFilePlatform = 437
  26.         .TextFileStartRow = 1
  27.         .TextFileParseType = xlDelimited
  28.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
  29.         .TextFileConsecutiveDelimiter = False
  30.         .TextFileTabDelimiter = False
  31.         .TextFileSemicolonDelimiter = False
  32.         .TextFileCommaDelimiter = True
  33.         .TextFileSpaceDelimiter = False
  34.         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
  35.         .TextFileTrailingMinusNumbers = True
  36.         .Refresh BackgroundQuery:=False
  37.     End With
  38. End Sub
  39. Sub Refresh()
  40.      ActiveWorkbook.RefreshAll
  41. End Sub
RAW Paste Data
Top