Ask Your Question

How to edit and update url for external data?

asked 2017-07-31 20:44:42 +0200

staylorx gravatar image

updated 2017-08-01 16:25:57 +0200

I have 20+ Sheets, each with a 'Link External Data' pulling from an external URL that changes 'hash' each day? Each Link will use the same common 'hash' that I can update in a cell and create the url specific to that page.
day1: - ~~dv_options.j;jsessionid=bztR1aEjBkAa?ticker=~~
day2: - ~~dv_options.j;jsessionid=avOgpHhrDlIa?ticker=~~
Each Day for 20+ tickers
Currently I go get the 'hash' manually from the main URL copy to a cell that generates the link in the cell beside where I need to paste the External Data and copy, move to target cell, Insert>Link to External Data> and paste that days URL in and complete the wizard. I would like to open the sheet [NO to update links] update the 'hash' cell, then Edit>Links>select all Update.

Or to do it with a macro [when I tried it with 'record macro' it just added a remark for the section I really wanted to automate.

Any thoughts?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-08-03 07:18:47 +0200

staylorx gravatar image

This is what I came up with - goes through each sheet, copying the concatenated URL and uses it to import the External data. 16 tabs takes about 7 min to complete, much faster and WAY less keystrokes than doing each sheet manually.

sub tabSwitch

dim document   as object
dim dispatcher as object
dim oAddress as Object
dim sheet as Object
dim oSheets as Object
dim n as Long

document   = ThisComponent
dispatcher = createUnoService("")
oSheets = ThisComponent.Sheets

' count number of sheets and set pointer for last one - allows for growth
NumSheets = (oSheets.Count - 1)

' loop through each sheet
For n = 1 to NumSheets
    ' set active sheet
    sheet = document.Sheets(n)
    ' set cell to insert Link External Data
    oAddress = sheet.getCellByPosition( 4, 33 ).getCellAddress()
    ' insert at (cell, url, what HTML, default, default) defaults were what the sample had and I didn't change them
    document.AreaLinks.insertAtPosition( oAddress, sheet.getCellByPosition(3, 33).String, "HTML_tables", "calc_HTML_WebQuery", "0 0" )
Next n

end sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-07-31 20:44:42 +0200

Seen: 402 times

Last updated: Aug 03 '17