Open an URL using Excel VBA in MAC

Question

Level 1

8 points

Open an URL using Excel VBA in MAC

I'm trying to Open an URL using Excel VBA in MAC and I want to close the Window after it loads.

I'm currently using this piece of Code so that I uses the Default browser to open the link, but need help on how to close the browser.

ActiveWorkbook.FollowHyperlink "https://www.google.com"

Sub test()

Dim h As Hyperlink

With Worksheets(1)

Set h = .Hyperlinks.Add(Anchor:=.Range("A1"), _

Address:="http://www.macnn.com")

h.Follow

h.Delete

.Range("A1").Clear

End With

End Sub

Both the above code open the link on the Default browsers. I can ask users to use Safari or Chrome in case I get to know the way I can close the browser instance.

Basically I want to open an URL where the browser windows doesn't show up the user and fetch the output. I'm in the process of writing a Function need help.Thanks for your help in Advance \(',')/

I'm trying to run the Code on Mac using Excel 2016

MacBook, Excel 2016

Posted on Nov 17, 2016 8:00 AM

Reply

Answer 1

Sep 26, 2017 7:39 PM in response to SGIII

If I have multiple rows of URLs in Excel on a Mac, how can I open one in Safari, saveas the webpage in a folder, close the tab, go to the next url in my list, and repeat the process until the end of that list.

Reply

Answer 2

Camelot

Level 9

54,333 points

Nov 20, 2016 11:20 PM in response to AbhishekDK

If you're using the default browser, I don't think there's any way you can tell which specific application that is, and therefore tell it to perform some follow-on action. In my experience, VBA is geared at driving itself, but not so good at managing external applications. If you want to drive external apps, consider using the Macscript command. Depending on the URLs you're accessing, you might even be better off not using a GUI browser at all, but driving something like curl via a shell/system command.

Reply

Answer 3

AbhishekDK Author

Level 1

8 points

Nov 20, 2016 11:27 PM in response to Camelot

Thanks a lot for taking time in Replying to my Concern. I'm not able to get any Solution how to go about executing Mac-script using VBA do can you help me with that.

In Windows we can Create an IE Object and we can call the Object and we can also see to that its not Visible i.e. ie.Visible= False. Can the same thing be done in Apple w.r.t Safari by any Chance. I'm new to Mac its very difficult for me to get Mac-script running using VBA.

Reply

Answer 4

SGIII

Level 8

35,264 points

Nov 21, 2016 10:27 AM in response to AbhishekDK

AbhishekDK wrote:

Basically I want to open an URL where the browser windows doesn't show up the user and fetch the output. I'm in the process of writing a Function need help.

By "fetch the output" do you mean perhaps either "fetch the source at the URL" or "fetch the results of an API at the URL"? If so you consider something like this (this example fetches CSV data returned by Yahoo!). This approach works well in Excel 2016 for Mac. It doesn't open a browser.

' Request the data from Yahoo! and put the returned CSV in rows in dDest's column

With ActiveSheet.QueryTables.Add(Connection:="URL;" & Y_API_URL, Destination:=dDest)

.Refresh BackgroundQuery:=False

.RefreshStyle = xlOverwriteCells

.SaveData = True

EndWith

Here the URL is in the form "http://finance.yahoo.com/d/quotes.csv?s=[SYMBOLS]&f=[TAGS]". Your URL most likely will be in a different form.

SG

Reply

Answer 5

AbhishekDK Author

Level 1

8 points

Nov 28, 2016 10:48 PM in response to SGIII

Thanks SG for your reply. But the code which you shared will import details or to download attachment from Excel. But I'm still not being able to get the required code. Is there any other way?

Reply

Answer 6

SGIII

Level 8

35,264 points

Nov 29, 2016 6:53 AM in response to AbhishekDK

AbhishekDK wrote:

But the code which you shared will import details or to download attachment from Excel.

Actually that is not what the code does at all. It does not "download attachment." It does not "import details." It retrieves data from a url without opening the browser. It works with Excel 2016.

SG

Reply