Open webpage via VBA

[NewOrderFac33]

Good morning,

I'm looking for examples of how to use VBA to open a webpage whose address is held in a string variable.

I have tried a few Google examples, but the best I can get

http://vba-corner.livejournal.com/4623.html

returns "unspecified error", and I'm sure there's someone on here who'll have something much simpler.

I'd prefer to use Chrome, but we also have IE if I have to go down that route.

Thanks in advance

Pete

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim URL As String
    URL = "http://vba-corner.livejournal.com/4623.html"
    ActiveWorkbook.FollowHyperlink URL
End Sub

 

[Arithos]

I use this setup, this is for work, so Chrome is not an option for me

Dim IEapp As Object        
Dim WebUrl As String


        Set IEapp = CreateObject("InternetExplorer.Application") 'Set IEapp = InternetExplorer
        WebUrl = "[URL="http://vba-corner.livejournal.com/4623.html"]Collection of VBA Code Snippets and Useful Excel Knowledge - Working with Internet Explorer Using VBA[/URL]"
                                                                                              
        With IEapp
            .Silent = True 'No Pop-ups
            .Visible = True 'Set InternetExplorer to Visible
            .Navigate WebUrl 'Load web page
        End With

or simply

[COLOR=#000000][FONT=Consolas]shell[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"C:\Users\USERNAME\AppData\Local\Google\Chrome\Application\Chrome.exe -url http:google.ca"[/FONT][/COLOR][COLOR=#000000][FONT=Consolas])[/FONT][/COLOR]

 

[NewOrderFac33]

Folks,

Thanks for your suggestions, both of which work just fine with a "simple" URL

However, the URL I'm trying to use is an output from an "Export to Excel" query within the JIRA issue management tool.

If I paste the URL directly into a browser, it generates an Excel export file for which you have to click a "The file you are trying to open is in a different format than specified by the file extension - Do you want to open the file now?" message.

To compound things, there are no accelerator keys for the "Yes" "No" and "Help" buttons, meaning that I'm going to need some sort of SendKeys tabbing solution.

However, your suggestions have put me on the right road, so thank you - any further suggestions will be more than welcome!

Have a good weekend.

Pete

 

[Andrew Poulsom]

From Googling it seems that the exported file is html but given an xls extension. Bug report:

https://jira.atlassian.com/browse/JRA-32352

 

[NewOrderFac33]

Hi, Andrew,

the following works:

Sub GenerateJIRAData()
    Dim URL As String
    URL = "https://jira5/sr/jira.issueviews:searchrequest-excel-current-fields/12927/SearchRequest-12927.xls?os_username=MYJIRALOGINID&os_password=MYJIRAPASSWORD&tempMax=5000"
    Application.SendKeys ("{Tab}{Enter}")
    ActiveWorkbook.FollowHyperlink URL
    ChDir "J:\Build and Release\Release Statistics"
    SendKeys ("Y") 'Suppress "Overwite" prompt
    ActiveWorkbook.SaveAs Filename:= _
        "J:\Build and Release\Release Statistics\B+R SUPBARS Raw Data.xlsx", _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

In this case 12927 is the ID of the query from which I would manually export to Excel. This can obviously change.

Thank you for the valuable pointers - have a good weekend!

Pete

 

[Lummo]

I know that it's been a while... Is there a way of specifying:

target="someName"

so that the page opens in the same browser tab each time?