Ask Your Question

Help - macro to update external links [closed]

asked 2017-08-01 20:34:40 +0100

staylorx gravatar image

I tried 'record macro' but the section I wanted to capture was remarked out with little to no detail.

I'm looking to create a macro where I can update a link and run through the selection wizard [or bypass and update/refresh directly]


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-12 00:27:27.755498

1 Answer

Sort by » oldest newest most voted

answered 2017-08-03 07:17:51 +0100

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

Question Tools

1 follower


Asked: 2017-08-01 20:34:40 +0100

Seen: 476 times

Last updated: Aug 03 '17