Open webpage via VBA

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,283
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I use this setup, this is for work, so Chrome is not an option for me :(

Code:
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
Code:
[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]
 
Upvote 0
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
 
Upvote 0
Hi, Andrew,

the following works:
Code:
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,833
Messages
6,181,242
Members
453,026
Latest member
cknader

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top