none
download from web to ms access

    Question

  • Hi,

    I want to download data from web site to ms access directly when ever user clicks button.  But i dont know how to do through vba codes.

    Can any one help me.

    site is : https://www.amfiindia.com/spages/NAVAll.txt?t=12012019055054

    Saturday, January 12, 2019 12:22 PM

All replies

  • You could use a webbrowser control, then access the DOM = Document Object Model via that control. Search for "access vba webbrowser control DOM" and you should find some promising leads.

    -Tom. Microsoft Access MVP

    Saturday, January 12, 2019 4:42 PM
  • Actual webpage data or download files? 

    Do you have permission from the website to do this?
    Have you inquired to see if they have an API available to you to interact with their site?


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, January 12, 2019 5:49 PM
  • This is public Domain.  For information to every one
    Sunday, January 13, 2019 4:20 AM
  • This works fine:

    Option Compare Database
    Option Explicit
    
    Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
    
    Sub Example()
    
       Dim strFile          As String
       Dim URL              As String
       Dim strLocalFullPath As String
       Dim strWebFullPath   As String
       
       URL = "http://portal.amfiindia.com/spages/"
       
       strFile = "NAVALL.txt"
       Dim strREf As String
       strREf = "12012019055054"
       
       strWebFullPath = URL & strFile & "?t=" & strREf
       
       strLocalFullPath = "c:\test2\" & strFile
       
       
       MsgBox "Download Status : " & (URLDownloadToFile(0, strWebFullPath, strLocalFullPath, 0, 0) = 0)
    
       ' now read the file into a table.
       
       Dim f             As Integer
       Dim strDAta       As String
       Dim strOneLine    As String
       Dim strGroup      As String
       Dim rst           As dao.Recordset
       Dim OneBuf        As Variant
       Dim i             As Integer
       
      Set rst = CurrentDb.OpenRecordset("tblData")
       
       f = FreeFile()
       Open strLocalFullPath For Input As #f
       
       ' skip  first 3 lines
       For i = 1 To 3
          Line Input #f, strOneLine
       Next i
       
       Do While EOF(f) = False
          Line Input #f, strOneLine
          If Trim(strOneLine) <> "" Then
             OneBuf = Split(strOneLine, ";")
             If UBound(OneBuf) = 0 Then
                ' change group
                strGroup = OneBuf(0)
             Else
                ' add record
                rst.AddNew
                rst!DataGroup = strGroup
                For i = 0 To UBound(OneBuf)
                   rst(i + 1) = OneBuf(i)
                Next i
                rst.Update
             End If
          End If
       Loop
       
       rst.Close
       Close (f)
       
       MsgBox "data input done"
       
    End Sub
    
    

    It assumes you have a table in Access defined as:

    So, place the above code in a module.

    (save it)

    Now run the routine Example

    Just type in example in the debug window, or place your cursor in the example code module and hit f5 to run.

    After you run, the tblData will have the results.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Wednesday, January 16, 2019 2:58 AM