Directly loading Data from a URL in Access Database (1 Viewer)

[rajivivekb]

Hello,


Need some ideas on how to load the data from a URL in a table directly. At this time I am using excel and its Power Query which is tedious and has issues with performance which i would like to avoid and make this app independent of MS Office. Hope some one has some code snippet or ideas.


I tried to give the actual link, but the portal did not allow me to give the link and hence given the link in words. My apologies.


portal dot amfiindia dot com slash spages slash NAVAll dot txt



Thanks in advance


Raji V

 

[arnelgp]

try this my friend, copy the code in Standard Module.

Option Compare Database

Sub CopyWebContent(strFile As String)
'
' arnel gp
'
' syntax:
'
' CopyWebContent "E:\sample.txt
'
' parameter:
' strFile must include the fullpath of the textfile
' where you want to save, eg:
'
' "E:\sample.txt"
    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    Dim fnum
   
    'create a new instance of ie
    Set ieApp = New InternetExplorer
   
    'you don’t need this, but it’s good for debugging
    'ieApp.Visible = True
   
    'assume we’re not logged in and just go directly to the login page
    vAddr = "http://portal.amfiindia.com/spages/NAVAll.txt"
    ieApp.Navigate vAddr
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
    Set ieDoc = ieApp.Document
   
    'copy the tables html to the clipboard and paste to teh sheet
    ieApp.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
    ieApp.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

    fnum = FreeFile()
    Open strFile For Output As #fnum
    With New DataObject
        .GetFromClipboard
        
          Print #1, .GetText
        Close
    End With

End Sub

 

[arnelgp]

sorry its a table:

Sub CopyWebContent(strTable As String, strFieldName as string)
'
' arnel gp
'
' syntax:
'
' CopyWebContent "TableNameHere", "FieldNameHere"
'
' parameter:
' strFieldName is the name of Memo (Long Text)
' where you want to save, eg:
'
'
    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    Dim fnum
    Dim rs As DAO.Recordset
   
    'create a new instance of ie
    Set ieApp = New InternetExplorer
   
    'you don’t need this, but it’s good for debugging
    'ieApp.Visible = True
   
    'assume we’re not logged in and just go directly to the login page
    vAddr = "http://portal.amfiindia.com/spages/NAVAll.txt"
    ieApp.Navigate vAddr
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
    Set ieDoc = ieApp.Document
   
    'copy the tables html to the clipboard and paste to teh sheet
    ieApp.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
    ieApp.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

    set rs = currentdb.openrecordset(strTable)
    With New DataObject
        .GetFromClipboard
        rs.AddNew
        rs(strFieldName).Value = .GetText         
        rs.Update
    End With
    rs.Close
    set rs = Nothing
End Sub

 

[rajivivekb]

Hello Arnel



Thanks for the code snippet. I will work and come back if i face any issues.


Thanks Once again.


Raji V

 

[rajivivekb]

Hello Arnel,


While I was analyzing the code, I saw DataObject, while looking at the definition, I did not see DataObject defined anywhere. Could you help me in what object it should be ?



set rs = currentdb.openrecordset(strTable)
With New DataObject
.GetFromClipboard
rs.AddNew
rs(strFieldName).Value = .GetText
rs.Update
End With


With little tweak, I could execute till here.
Thanks
Raji V

 

[arnelgp]

add reference to Microsoft Form 2.0 Object Library.