Home » Questions » Computers [ Ask a new question ]

Excel: Fetch values from downloaded xml and insert into table?

Excel: Fetch values from downloaded xml and insert into table?

How do I force Excel to download http://api.eve-central.com/api/evemon, parse it and format the values in a table every time the .xlsx file is opened?

Asked by: Guest | Views: 26
Total answers/comments: 1
Guest [Entry]

"If you are using Data|Import External Query|New Web Query,
there is a param, RefreshOnFileOpen, that you can just set to True.

Here is a snip that gets the data from the net, but doesn't format it (since you said you already got that working).

'
'
Range(""I12"").Select
With ActiveSheet.QueryTables.Add(Connection:= _
""URL;http://api.eve-central.com/api/evemon"", Destination:=Range(""I12""))
.Name = ""evemon_1""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub"