Running Excel VBA in IE

powerpackinduo

Board Regular
Joined
Jul 28, 2005
Messages
128
I am having trouble running Excel VBA code in an excel workbook through Internet Explorer (IE).
The code I have runs fine in Excel. But when I running by opening in IE I get an error.
For example trying to run the simple command
Range("A1").Select

Results in the error message Method 'Range' of object '_Global' Failed

What do I need to change in my code to get IE to run the code properly?

Here's my code:
Code:
Option Explicit
Public cnn As New Connection
Public bFlag As Boolean
Dim rs As Recordset
Dim e As ADODB.Error
Dim CYExtract As Date
Dim PYExtract As Date

Public Sub LoadData()

Dim msg As String
Dim extractDate As String

On Error GoTo AnError

Application.ScreenUpdating = False

'Set Current Year Extract Date to be the most current data in the datamart.
CYExtract = Date - 1

'Set Previous Year Extract Date to be the most current data for previous time period.
If Range("BeginDate").Value > CYExtract Then
    PYExtract = CYExtract
    Else: PYExtract = Range("BeginDate").Value
End If

'Check that Property box has a property selected.
If Params.cboProperty.ListIndex = -1 Then
  MsgBox "Please select a property from the drop down menu.", vbOKOnly + vbExclamation, "Select Property"
  Application.StatusBar = False
  Application.ScreenUpdating = True
  Exit Sub
End If

'Check that Begin Date is enterred.
If Range("BeginDate").Value = "" Then
  MsgBox "Please enter a Begin Date.", vbOKOnly + vbExclamation, "Missing Begin Date"
  Range("BeginDate").Select
  Application.StatusBar = False
  Application.ScreenUpdating = True
  Exit Sub
End If

'Check that End Date is enterred.
If Range("EndDate").Value = "" Then
  MsgBox "Please enter an End Date.", vbOKOnly + vbExclamation, "Missing End Date"
  Range("EndDate").Select
  Application.StatusBar = False
  Application.ScreenUpdating = True
  Exit Sub
End If

' When all fields have passed validation process request.
Application.StatusBar = "Processing request. Please wait..."

'Clear all old data
Previous.Range("A2:" + FindLastCell(Previous)).ClearContents
Current.Range("A2:" + FindLastCell(Current)).ClearContents

'First the previous reservations sheet
If Not FormatSQL(CStr(PYExtract), CStr(DateAdd("yyyy", -1, Range("BeginDate").Value)), CStr(DateAdd("yyyy", -1, Range("EndDate").Value))) Then
  Application.StatusBar = False
  Application.ScreenUpdating = True
  Exit Sub
End If
Set rs = GetRecordset(sSQL)
Previous.Range("A2").CopyFromRecordset rs

'Verify records were returned for previous period extract.
If Previous.Cells(Rows.Count, "D").End(xlUp).row = 1 Then
    MsgBox "The parameters selected did not return any records for the previous period.  No list can be generated.", vbOKOnly + vbExclamation, "No Previous Records"
    Application.StatusBar = False
    Params.cboProperty.Activate
    Application.ScreenUpdating = True
    Exit Sub
End If

'Now the current reservations sheet
If Not FormatSQL(CStr(CYExtract), CStr(Range("BeginDate").Value), CStr(Range("EndDate").Value)) Then
  Application.StatusBar = False
  Application.ScreenUpdating = True
  Exit Sub
End If
Set rs = GetRecordset(sSQL)
Current.Range("A2").CopyFromRecordset rs

If cnn.State Then cnn.Close

'Create/Update Not Reserved sheet
Call NotReserved

'Delete Dupes on Not Reserved sheet
If Params.chkDeleteDupes.Value Then
  Sheets("Not Reserved").Select
  Call DeleteDuplicates
End If

'Sort and format all sheets in active workbook except the first (parameters)
    Application.StatusBar = "Formatting sheets..."
    
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "Parameters" Then
          ws.Activate
          FormatSheet
        End If
    Next ws

Application.StatusBar = False
Application.ScreenUpdating = True

Exit Sub
  
AnError:
  For Each e In cnn.Errors
  msg = msg + e.Description + vbLf
  Next
  MsgBox Err.Description + vbLf + msg + vbLf + "in LoadData"
 Err.Raise Err.Number, "LoadData", Err.Description
  
If cnn.State Then cnn.Close

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub
 

powerpackinduo

Board Regular
Joined
Jul 28, 2005
Messages
128
Thanks for the suggestion. I've done that and it still has an error.
The error generates on .activate and .select.
 

rdwray

Banned
Joined
Sep 22, 2009
Messages
93
One thing that I notice is that you use Range and do not give it a Workbook or Sheet name. I have never tried what you are doing, but working across platforms, I would think that you would have to focus on the object. For instance:
Code:
    Workbooks("Contacts.xls").Sheets("Records").Cells(3, 2).Activate
' The following will not work:
'    Workbooks("Contacts.xls").Sheets("Records").Range("B3").Activate
VB can be act up if not handled right. I would suggest giving the line that works in the above code a try.
 

powerpackinduo

Board Regular
Joined
Jul 28, 2005
Messages
128
I don't give a workbook or a sheetname because I'm using a named range.

When I code the longform of the address I still get the same error no matter whether I .activate or .select the cells.
 

powerpackinduo

Board Regular
Joined
Jul 28, 2005
Messages
128
To test create a text Excel file. Then right click on the file and do 'open with...' and select Internet Explorer to see the environment I'm having the issues with.
 

rdwray

Banned
Joined
Sep 22, 2009
Messages
93
I will say one thing, I am using XL2000 and I know that all versions after this one have problems of one kind or another and that is one reason I have never upgraded.
 

Forum statistics

Threads
1,081,841
Messages
5,361,630
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top