Need Macro to copy web page to Calc sheet

[alf50]

Need Simple Calc Macro to goto URL web page, Select All,Copy to CLipBoard, select Cell in Calc Sheet and Past.
I can do this manually in either Mac using Safari or Google Chrome, or in raspberry Pi OS using Google Chrome. Why, when I search for this Macro do I see solutions that are hundreds of lines long? I have solved this problem on the Mac using AppleScripts to do the Launch Safari/select the Web Page/ Select All (CmdA) (of the web page), return to OO Calc Select the Cell and Paste all the lines of the web page from that point down. This copy/paste procedure only returns all the text from the web page which is exactly what I want. One would think the Code to copy all the text from a web URL link would be a 10 to 20 line macro, something like:

Code: Select all

Sub getURLdata
  rem ----------------------------------------------------------------------
  rem define variables
  dim document   as object
  dim dispatcher as object
  rem ----------------------------------------------------------------------
  rem get access to the document
  document   = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  rem ----------------------------------------------------------------------
  Url = "http:/www.mysite.com/my_web_page.html"
  URLtext = StarDesktop.loadComponentFromURL(Url, "_blank", 0, Dummy)
  rem ----------------------------------------------------------------------
  dim args1(0) as new com.sun.star.beans.PropertyValue
  args1(0).Name = "ToPoint"
  args1(0).Value = "$A$24"
  dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
  rem ----------------------------------------------------------------------
  dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
  rem ----------------------------------------------------------------------
end sub

but instead, after trying a number of 50 to 100 line suggestions, nothing works.

Any help would be appreciated.

[FJCC]

The following code loads a web page into a Calc document. Can you work with that?

Code: Select all

DIM propval(0) AS NEW com.sun.star.beans.PropertyValue
propval(0).Name = "FilterName"
propval(0).Value = "calc_HTML_WebQuery"
sURL="https://archive.apache.org/dist/openoffice/4.1.9/binaries/en-US/"
oDoc=StarDesktop.loadComponentFromURL(sURL,"_blank",0,propval())

[alf50]

FJCC Moderator: Thank you for the Quick Reply.

Even though your code works to access your html web page, if I apply it exactly as is,
but I get an error when I insert my URL saying:

BASIC runtime error.
Type: com.sun.star.lang.IllegalArgumentException
Message: URL seems to be an unsupported one..

Is there any more generic version of your code that would strip off all the hyperlink stuff
and not try to interpret the imbedded HTML stuff. It seems like a simple, manual "Copy from Web Page" and
"Paste to Starting Cell in the Calc Sheet" with intervening
"How do you want to Import Data" Dialog (in which I simple accept the defaults by typing enter) seems to handle things just fine.
The macro that includes "StarDesktop.loadComponentFromURL(sURL...) is not doing what the manual approach does.
Is the issue here because the Web Page Manual "Copy" is controlled by the web browser app and not by the OO Calc App ?
I.E.: The web browser knows how to handle the Web Page formatting while Calc does not.

[FJCC]

I am not surprised that Calc does not import arbitrary web pages cleanly. I have very little experience using it to handle web data. If you could post a link to the actual page you are working with, someone might have a suggestion for how to handle the data.

[alf50]

My post keeps coming back [SOLVED] even though it has not been solved.

The URL is to a Yahoo Finance Web page, open to the public, that contains a table. A URL link to it is:

https://finance.yahoo.com/screener/pred ... soc_trk=ma".

Your URL returns an oDoc page. My URL returns nothing.

Code: Select all

Sub Main

end sub

Sub NavURLooweb()
 DIM oDoc as object
 DIM propval(0) AS NEW com.sun.star.beans.PropertyValue
 propval(0).Name = "FilterName"
 propval(0).Value = "calc_HTML_WebQuery"

 sURL="https://archive.apache.org/dist/openoffice/4.1.9/binaries/en-US/"

 oDoc=StarDesktop.loadComponentFromURL(sURL,"_blank",0,propval())

 'CopyToClipboard(oDoc)
 'oDoc.close(True)

End Sub

Sub NavURLYFweb()
 DIM oDoc as object
 DIM propval(0) AS NEW com.sun.star.beans.PropertyValue
 propval(0).Name = "FilterName"
 propval(0).Value = "calc_HTML_WebQuery"

 sURL="https://finance.yahoo.com/screener/predefined/growth_technology_stocks?soc_src=screener-share&soc_trk=ma"

 oDoc=StarDesktop.loadComponentFromURL(sURL,"_blank",0,propval())

 'CopyToClipboard(oDoc)
 'oDoc.close(True)

End Sub

sub CopyToClipboard(Document as object)

    Dim dispatcher as object
    Dim docframe as object

    docframe = Document.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    dispatcher.executeDispatch(docframe, ".uno:SelectAll", "", 0, Array())
    dispatcher.executeDispatch(docframe, ".uno:Copy", "", 0, Array())
     
end sub

[FJCC]

The attached file shows what I get if I use the menu Insert -> Link to External Data (the menu item might be slightly different, my interface is not in English at the moment), enter https://finance.yahoo.com/screener/pred ... soc_trk=ma in the URL box, press Enter, accept the Automatic language setting, and pick HTML_1 from the available tables. Is that what you need?

[alf50]

I would rather have The Whole page, rather than just the table but I will try that.
The table I am really after is protected behind a login that needs to be updated once per week.
The reason I can do this manually is that the UserID/password protected link is established with the Safari or Chrome Browsers.
Since Safari on a Mac is AppleScriptable I can automate the whole copy/past process from a Mac with executable calls to the AppleScriptable
Browser from the Calc worksheet Macro.

I understand that the Chrome Browser is scriptable through a plugin so maybe that is a way of doing This.
Unless you have a way of tricking the StarDesktop.LoadComponentFromURL() routine to look like The Safari or Chrome Web Browsers so the website doesn’t block the request. Thanks anyway for trying.