I have taken over a project for our product Map Intelligence that produces a geographical map based on data in Excel spreadsheets using VBA. The product works fine when external browsers are invoked and at one time it was able to produce an embedded browser in Excel. It no longer works, at least in my environment which is:
OS Name Microsoft Windows 10 Pro
Version 10.0.19044 Build 19044
Microsoft® Excel® 2021 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
It opens a sheet for the map, then places a blank area for the map, but does not submit the form that drives our map viewer (in a separate server). I checked and our server receives no request.
Here is the code that we currently use to generate the embedded map. It does not produce any errors. Can anyone suggest what we need to do differently in a current Windows environment? Is it even possible now?
Cheers,
Chris Mugdan
Code:
Dim browserSheet As Object
Dim currentSheet As Worksheet
Dim miWebBrowser As Object 'our WB control
On Error Resume Next
Set browserSheet = ActiveWorkbook.Sheets(INTERNAL_SHEET_NAME)
Set currentSheet = ActiveWorkbook.ActiveSheet
If Err = 0 Then
'sheet exists already
Else
'create sheet
Sheets.Add.name = INTERNAL_SHEET_NAME
Set browserSheet = ActiveWorkbook.Sheets(INTERNAL_SHEET_NAME)
End If
Dim browserExists As Boolean
Dim i As Double
browserExists = False
browserSheet.Activate
For i = 1 To browserSheet.Shapes.count
'browserSheet.Activate
If browserSheet.Shapes(i).name = "WebBrowser1" Then
browserSheet.Shapes(i).Select
browserExists = True
Set miWebBrowser = selection.Object '... previously created
End If
Next i
If browserExists = False Then
Dim w As Double
Dim h As Double
If client.filllSheet = True Then
w = Application.UsableWidth - 33
h = Application.UsableHeight - 30
Else
w = 1500.5
h = 800
End If
Dim web
'add a browser to the sheet and populate it
Set web = browserSheet.OLEObjects.Add(ClassType:="Shell.Explorer.2", Link:=False, _
DisplayAsIcon:=False, Left:=2.25, top:=2.25, Width:=w, Height:= _
h) ' .Select
Set miWebBrowser = web.Object '...created for the 1st time
If client.filllSheet = True Then
web.Width = Application.UsableWidth - 33
web.Height = Application.UsableHeight - 30
End If
Else
' Set sizeHandler.miBrowser = selection
If client.filllSheet = True Then
selection.Width = Application.UsableWidth - 33
selection.Height = Application.UsableHeight - 30
End If
End If
'resize browser object and navigate to it
DoEvents
Sleep 500
miWebBrowser.Navigate ("file://" & getDefaultPath & "\launchfile.html")
'Do While miWebBrowser.readyState < 4
Sleep 500
DoEvents
'Loop
' ActiveSheet.range("A1").Select
'sadly we have to do this to make it work.
'otherwise it doesnt show the sheet correctly.
' browserSheet.Activate
If browserExists = False Then
currentSheet.Activate
browserSheet.Activate
End If
DoEvents
If ForceSubmit Then
miWebBrowser.Document.forms("miForm").submit
DoEvents
Sleep 2000
End If
