I am trying to open a report on our Database in Internet explorer.
I am using IE9 on Windows7. My macro gets to the stage to open the database on the report page but it times out before it can open the report and copy data.
In excel the urls are listed in the Start page cells B1 and B2. Username and password are stored in cells B3 and B4.
The error i get is Run-time error '438': Object doesn't support this property or method. When i debug it points to the line : .LOGIN_USERNAME.Value = Sheets("Start").Range("B3").Value
I am really unknowledgeable about VBA. Does anyone know what i need to do to fix this?
Code:
Sub GoGetIt()
Dim URL_1 As String
Dim URL_2 As String
Dim ieApp As SHDocVw.InternetExplorer
Dim ieDoc As Object
URL_1 = Sheets("Start").Range("B1").Value
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & URL_1, Destination:=Range("$A$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 5
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 1, 1, 2, 1, 2, 2, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
If Range("A1").Value <> "ORG_SEC_ID" Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
URL_2 = Range("B2").Value
Set ieApp = New SHDocVw.InternetExplorer
ieApp.Visible = True
ieApp.Navigate URL_2
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Set ieDoc = ieApp.Document
With ieDoc.forms(0)
.LOGIN_USERNAME.Value = Sheets("Start").Range("B3").Value
.LOGIN_PASSWORD.Value = Sheets("Start").Range("B4").Value
.submit
End With
ActiveWorkbook.Worksheets.Add
Application.Wait (Now() + TimeValue("00:00:30"))
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & URL_1, Destination:=Range("$A$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 5
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 1, 1, 2, 1, 2, 2, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ieApp.Quit
Else
End If
End Sub
Bookmarks