gypsyjoe11
Registered User.
- Local time
- Today, 00:59
- Joined
- Feb 19, 2010
- Messages
- 46
Hi All,
I thought I'd contribute something I wrote as a way of saying thanks for all the advice.
I had the need to transfer a large 3000 row table from a website into Access. I tried the normal DAO.Recordset route but it was way too slow(talking 10 minutes). I then looked into putting it into the past buffer manually. I was able to do this, but the only thing that recognized it was Word. Although it successfully pasted as a table in word it was still way too slow.
So the below is the answer I came up with and it is very fast.
It transfers the HTMLTable to a file in the same directory as the database. Then it makes a linked table in Access. Then it makes a permanent (non-linked) table from the linked one.
Once I get to the page I want and find the frame(mainFrame) that has the table I want I call my routine with:
I have references to several things for other routines.
The refereneces that I know you need are:
microsoft Internet controls & microsoft HTML object library
I hope this is of use to someone.
Happy new year,
Joe
I thought I'd contribute something I wrote as a way of saying thanks for all the advice.
I had the need to transfer a large 3000 row table from a website into Access. I tried the normal DAO.Recordset route but it was way too slow(talking 10 minutes). I then looked into putting it into the past buffer manually. I was able to do this, but the only thing that recognized it was Word. Although it successfully pasted as a table in word it was still way too slow.
So the below is the answer I came up with and it is very fast.
It transfers the HTMLTable to a file in the same directory as the database. Then it makes a linked table in Access. Then it makes a permanent (non-linked) table from the linked one.
Code:
Public Sub ConvertTableHTMLtoAccess(myHTMLtable As HTMLTable, myAccesstableName As String)
DoCmd.SetWarnings False
'Get the database path
Dim strFullPath As String
Dim dataBasePath As String
Dim I As Integer
strFullPath = CurrentDb().Name
For I = Len(strFullPath) To 1 Step -1
If Mid(strFullPath, I, 1) = "\" Then
dataBasePath = Left(strFullPath, I)
Exit For
End If
Next
'end get database path
'Put the HTML Table in a file
Open dataBasePath & "HTMLTable.html" For Output As #1
Print #1, "<HTML>"
Print #1, myHTMLtable.outerHTML
Print #1, "</HTML>"
Close #1
'end make HTML Table
'Erase the tables if it already exists
If TableExists("HTMLTable") Then
DoCmd.RunSQL "DROP TABLE HTMLTable"
End If
If TableExists(myAccesstableName) Then
DoCmd.RunSQL "DROP TABLE " & myAccesstableName
End If
'Transfer the file to a linked temporary table in Access
DoCmd.TransferText acLinkHTML, , "HTMLTable", dataBasePath & "HTMLTable.html", -1
'Transfer the linked temporary table to a permanent table that doesn't
'depend on the file
DoCmd.RunSQL "SELECT HTMLTable.* INTO " & myAccesstableName & " FROM HTMLTable;"
'Drop the temporary table
DoCmd.RunSQL "DROP TABLE HTMLTable"
'Erase the temporary File
Kill dataBasePath & "HTMLTable.html"
DoCmd.SetWarnings True
End Sub
Private Function TableExists(TableName As String) As Boolean
Dim tableFound As Boolean
Dim I As Long
tableFound = False
For I = 0 To CurrentData.AllTables.Count - 1
If CurrentData.AllTables(I).Name = TableName Then
tableFound = True
GoTo exitForLoop
End If
Next I
exitForLoop:
TableExists = tableFound
End FunctionOnce I get to the page I want and find the frame(mainFrame) that has the table I want I call my routine with:
Code:
ConvertTableHTMLtoAccess mainFrame.getElementsByTagName("TABLE")(5), "QuotaReport"I have references to several things for other routines.
The refereneces that I know you need are:
microsoft Internet controls & microsoft HTML object library
I hope this is of use to someone.
Happy new year,
Joe
