none
How do I extract web page content using VBA? RRS feed

Answers

  • As a starting point, you could try this VBA

    Sub GetPage()
    Dim s As String
    s = MacScript("do shell script ""curl http://www.goodworth.org/testwp.htm""")
    Debug.Print s
    End Sub
    That worked OK here, but you may find that instead of MacScript, which is deprecated, you will need to use the AppleScriptTask feature in the 2016/2019 version of VBA. That means that you have to create an external AppleScript file in the correct part of Office's "Group.Container". Yes, let's just make things more secure by making them so much more complicated that everyone ends up having to bypass all the security checks - well done, IT industry!

    And obviously, that's just for a fixed URL - you might need to build the URL's text on-the-fly, and you might also need to deal with authentication dialogs and so on.

     

    Peter Jamieson



    Tuesday, March 31, 2020 8:01 PM

All replies

  • Hi 

    You can refer to the code in the link below, you can't apply them directly, you should modify specific parameters according to your needs.

    Note: You should set Reference to Microsoft Internet Controls and Microsoft HTML object library from "VBE--->Tools-->>References" before running code.

    Extracting data from website using VBA

    Excel VBA - Extracting data from web page

    Best regards

    Itch Sun


     Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

                   

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.                

    Tuesday, March 31, 2020 2:03 AM
  • Itch Sun, will this work on Mac OS? Have you tried running your proposed solution on Mac OS?
    Tuesday, March 31, 2020 6:09 AM
  • Hi

    I'm sorry, I don't have mac environment for you to test.

    This is beyond my scope of business, I can only provide some cases for your reference.

    But as far as I know, there's not much difference between mac and windows VBA logic.

    If you need more professional VBA technical support, I suggest you ask a question in Excel Developers forum.

    Best regards

    Itch Sun


     Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

                   

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.                

    Tuesday, March 31, 2020 6:25 AM
  • This is the section for Office 2016 for Mac. If you don't have a Mac or have never worked on a Mac, then you don't need to participate in this section.
    Tuesday, March 31, 2020 6:28 AM
  • Hi

    Thank you for your valuable comments.

    In order to give you more professional help, we will help you move this case to Excel developers forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Best regards

    Itch Sun


     Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

                   

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.                

    Tuesday, March 31, 2020 6:34 AM
  • No, it won't work, because the objects referenced are not available on Mac OS.

    The question is what might work. I'm not an Excel expert on either Mac OS or Windows, but if you come from a Windows Excel background, 

     a. you can't assume the same things will be available on the Mac OS version

     b. you can't assume that the same things will even be available via VBA. VBA itself is very similar on both platforms, but the objects available from VBA are much more restricted on Mac OS.

    So perhaps best to backtrack and ask what you are starting with, e.g.,

     - do you just have a URL, or have you already loaded a web page into something (perhaps a browser, or using some feature of Excel), or perhaps you have a web page saved to disk

     - What kind of data do you need to extract from the web page? e.g., is it text at a particular location, or is it data held in an HTML table? (If the latter, the Excel Web Page query feature might be very helpful)


    Peter Jamieson

    Tuesday, March 31, 2020 7:02 PM
  • Peter Jamieson, I need to extract a web-page from the Internet. I need to extract the entire web-page as it is, that is, along with the html tags.
    Tuesday, March 31, 2020 7:10 PM
  • What do you then need to do with it. Do you just need to get it, then save it to disk, or what?

    Just as a guideline, even if you want to initiate that process from Excel VBA (e.g. starting from a button event or some such) on Mac OS what you're probably going to need to do is call out to a Mac Shell to do the actual work (perhaps using a utility such as "curl")





    Peter Jamieson

    Tuesday, March 31, 2020 7:17 PM
  • I need to write the contents of the web-page to a variable. I can do everything else. What I need is to extract the contents of a web-page from the Internet to a variable. If you can't extract it to a variable, you can do it to a file on your hard disk.
    Tuesday, March 31, 2020 7:25 PM
  • As a starting point, you could try this VBA

    Sub GetPage()
    Dim s As String
    s = MacScript("do shell script ""curl http://www.goodworth.org/testwp.htm""")
    Debug.Print s
    End Sub
    That worked OK here, but you may find that instead of MacScript, which is deprecated, you will need to use the AppleScriptTask feature in the 2016/2019 version of VBA. That means that you have to create an external AppleScript file in the correct part of Office's "Group.Container". Yes, let's just make things more secure by making them so much more complicated that everyone ends up having to bypass all the security checks - well done, IT industry!

    And obviously, that's just for a fixed URL - you might need to build the URL's text on-the-fly, and you might also need to deal with authentication dialogs and so on.

     

    Peter Jamieson



    Tuesday, March 31, 2020 8:01 PM
  • Peter Jamieson, thank you.

    What will the same code look like using AppleScriptTask?

    Wednesday, April 1, 2020 8:17 AM
  • For example, you might have an AppleScript called mytestapplescript that contained the following code (which lets us do 2 slightly different things):

    on mytesthandler1()
    	
    	return (do shell script "curl http://www.goodworth.org/testwp.htm")
    	
    end mytesthandler1
    
    on mytesthandler2(pagename as text)
    	
    	-- not good as this is basically a code injection approach, but it's
    	-- just an example
    	
    	return (do shell script "curl http://www.goodworth.org/" & pagename)
    	
    end mytesthandler2

    You save that (there are various possible formats - I happen to have used the compiled ".scpt" format) and copy it into the following folder ("~" means your user's home directory, if you are unfamiliar with Mac OS) 

    ~/Library/Application/com.microsoft.Excel

    (~/Library/Application should be there, but you may need to create the com.microsoft.Excel folder under it)

    Then you can use VBA code like this:

    Sub getpage2()
    
    Dim s1 As String
    Dim s2 As String
    ' No parameter but the argument is not optional, so provide ""
    s1 = AppleScriptTask("mytestapplescript.scpt", "mytesthandler1", "")
    Debug.Print s1
    s2 = AppleScriptTask("mytestapplescript.scpt", "mytesthandler2", "testwp4.htm")
    Debug.Print s2
    
    End Sub
    

    The main documentation is at :

    https://docs.microsoft.com/en-us/office/vba/office-mac/applescripttask


    Peter Jamieson

    Wednesday, April 1, 2020 11:30 AM
  • Peter Jamieson, thank you.
    Wednesday, April 1, 2020 11:38 AM