Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

In the code provided, the browser and web page is not opening: Sub PremisesRequests()…

  1. User

    In the code provided, the browser and web page is not opening: Sub PremisesRequests()

    Dim url As String

    url = "https://docs.google.com/spreadsheets/d/1PbhnvqdiCrZM2oumPARI-bZSnKSbkH1BoG9L__Z54sw/edit?resourcekey#gid=878161329"

    CreateObject("Shell.Application").Open url

    Application.Wait (Now + TimeValue("0:00:05"))

    Dim wpgpr As Workbook

    Set wpgpr = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Premises Requests.xlsm")

    wpgpr.Activate

    Call PromptToCopyGoogleSheetToExcel

    End Sub

  2. Assistant

    It is possible that the issue is caused by the fact that the URL provided in the code is not a valid URL. Also, there may be issues with the file path in the Set wpgpr line of the code. Here are a few potential solutions:

    • Ensure that the URL in the code is correct and valid. Double-check the URL and try copying and pasting it into a web browser to make sure that it opens the intended page.
    • Check that the file path for the Premises Requests.xlsm file is correct and accessible. If the file is on a shared drive, check that you have access to the drive and that the file path is correct.
    • Try removing the line of code that opens the URL in the browser and see if the rest of the code works. It's possible that this line of code is causing issues and preventing the rest of the script from running.
  3. User

    Tested the url and it is OK, it does open the specified web page

  4. Assistant

    In that case, the issue may be with the file path in the Set wpgpr = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Premises Requests.xlsm") line of the code. Check that the file path is correct and that you have access to the shared drive. Here are a few potential solutions:

    • If the file path is correct, try validating that you have permissions to access the file. If you do not have permissions, you will need to request them from the file owner.
    • If the file path is incorrect, update it to the correct path.
    • It's also possible that the shared drive is not connected to your computer. Try disconnecting and reconnecting the drive to see if that resolves the issue.
    • Finally, it may be helpful to add error handling to the code to see if any errors are being thrown. This can help diagnose the issue and provide more details on what may be causing the problem.
  5. User

    when I run the code, the excel workbook is all that opens

  6. Assistant

    It's possible that the CreateObject("Shell.Application").Open url line of the code is not working as expected and is only opening the workbook. To open the web page in the default browser and then open the Excel workbook, you could try the following modifications to the code:

    Sub PremisesRequests()
    
    Dim url As String
    
    url = "https://docs.google.com/spreadsheets/d/1PbhnvqdiCrZM2oumPARI-bZSnKSbkH1BoG9L__Z54sw/edit?resourcekey#gid=878161329"
    
    ' Create a new Internet Explorer instance and navigate to the URL
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.Navigate url
    
    ' Wait for the web page to fully load before continuing
    Do While ie.Busy Or ie.ReadyState <> 4
        DoEvents
    Loop
    
    ' Open the Excel workbook
    Dim wpgpr As Workbook
    Set wpgpr = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Premises Requests.xlsm")
    wpgpr.Activate
    Call PromptToCopyGoogleSheetToExcel
    
    ' Close the Internet Explorer instance
    ie.Quit
    
    End Sub
    

    This should open the web page in Internet Explorer and wait for it to load before opening the Excel workbook and running the PromptToCopyGoogleSheetToExcel procedure. Once the script is completed, the Internet Explorer window will be closed.

  7. User

    the default browser is Google Chrome

  8. Assistant

    In that case, you can modify the code to use Google Chrome instead of Internet Explorer. Here's how the code would look with that change:

    Sub PremisesRequests()
    
        Dim url As String
        Dim chromePath As String
        Dim chromeOptions As Object
        Dim chromeWindow As Object
    
        url = "https://docs.google.com/spreadsheets/d/1PbhnvqdiCrZM2oumPARI-bZSnKSbkH1BoG9L__Z54sw/edit?resourcekey#gid=878161329"
        chromePath = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
    
        ' Create a new Chrome window and navigate to the URL
        Set chromeOptions = CreateObject("WScript.Shell")
        Set chromeWindow = CreateObject("Shell.Application").ShellExecute(chromePath, url, "", "", 1)
    
        ' Wait for the web page and workbook to be fully initialized before continuing
        Application.Wait (Now + TimeValue("0:00:05"))
    
        ' Open the Excel workbook
        Dim wpgpr As Workbook
        Set wpgpr = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Premises Requests.xlsm")
        wpgpr.Activate
        Call PromptToCopyGoogleSheetToExcel
    
    End Sub
    

    This should open the Google Chrome window and wait for the web page and workbook to be fully initialized before opening the Excel workbook. Once the script is completed, the Google Chrome window will remain open.

  9. User

    i am getting an error on this line: Set chromeWindow = CreateObject("Shell.Application").ShellExecute(chromePath, url, "", "", 1)

  10. Assistant

    It's possible that the ShellExecute method is not working on your system. In that case, you can try using the following code instead:

    Sub PremisesRequests()
    
        Dim url As String
        Dim chromePath As String
        Dim chromeOptions As Object
        Dim chromeWindow As Object
    
        url = "https://docs.google.com/spreadsheets/d/1PbhnvqdiCrZM2oumPARI-bZSnKSbkH1BoG9L__Z54sw/edit?resourcekey#gid=878161329"
        chromePath = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""
    
        ' Create a new Chrome window and navigate to the URL
        Set chromeOptions = CreateObject("WScript.Shell")
        chromeOptions.Run chromePath & " -url " & url
        
        ' Wait for the web page and workbook to be fully initialized before continuing
        Application.Wait (Now + TimeValue("0:00:05"))
    
        ' Open the Excel workbook
        Dim wpgpr As Workbook
        Set wpgpr = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Premises Requests.xlsm")
        wpgpr.Activate
        Call PromptToCopyGoogleSheetToExcel
    
    End Sub
    

    This should also open the Google Chrome window and wait for the web page and workbook to be fully initialized before opening the Excel workbook. Once the script is completed, the Google Chrome window will remain open.

  11. User

    error on this line: chromeOptions.Run chromePath & " -url " & url

  12. Assistant

    If you're seeing an error on the line chromeOptions.Run chromePath & " -url " & url, you may need to enclose the URL in quotes. Here's an updated version of the code that should work:

    Sub PremisesRequests()
    
        Dim url As String
        Dim chromePath As String
        Dim chromeOptions As Object
        Dim chromeWindow As Object
    
        url = "https://docs.google.com/spreadsheets/d/1PbhnvqdiCrZM2oumPARI-bZSnKSbkH1BoG9L__Z54sw/edit?resourcekey#gid=878161329"
        chromePath = """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"""
    
        ' Create a new Chrome window and navigate to the URL
        Set chromeOptions = CreateObject("WScript.Shell")
        chromeOptions.Run chromePath & " -url """ & url & """"
        
        ' Wait for the web page and workbook to be fully initialized before continuing
        Application.Wait (Now + TimeValue("0:00:05"))
    
        ' Open the Excel workbook
        Dim wpgpr As Workbook
        Set wpgpr = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Premises Requests.xlsm")
        wpgpr.Activate
        Call PromptToCopyGoogleSheetToExcel
    
    End Sub
    

    This should launch Google Chrome and open the web page specified by url. Once the page has fully loaded, the Excel workbook located at the specified path should open and the PromptToCopyGoogleSheetToExcel function should be called.