Running Excel VBA in IE

[powerpackinduo]

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:

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

 

[rdwray]

Try using .Activate instead of .Select.

 

[powerpackinduo]

Thanks for the suggestion. I've done that and it still has an error.
The error generates on .activate and .select.

 

[rdwray]

Ok, then have you tried using Cells(x,y) instead of range?

 

[powerpackinduo]

Yes

 

[rdwray]

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:

    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]

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]

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]

I didn't have any problem with IE8 and XP Pro SP3.

 

[rdwray]

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.