Results 1 to 2 of 2

Importing multiple XML into access

  1. #1
    Pranu is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Aug 2018
    Posts
    1

    Importing multiple XML into access


    HI all ,

    I have close to 5000 XML files with different file, but similar tag structure with different data inside it , i want to import all data into access at once . it is very time consuming to import each xml . are there any softwares . i dont know vba coding

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,844
    This imports all files in the folder,
    usage:
    ImportAllFilesInDir "c:\folder"

    Code:
    '------------
    Public Sub ImportAllFilesInDir(ByVal pvDir)
    '------------
    Dim vFil, vTargT
    Dim i As Integer
    Dim sTbl As String, sSql As String
    Dim FSO
    Dim oFolder, oFile
    
    DoCmd.SetWarnings false
    
    On Error GoTo errImp
    If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
    
    sTbl = "tData"
    
    Set db = CurrentDb
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(pvDir)
    
    For Each oFile In oFolder.Files
    
        vFil = pvDir & oFile.Name
        If InStr(sfile, ".xls") > 0 Then      'ONLY DO EXCEL FILES
    
              DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, sTbl, vFil, True
        end if   
    Next
    
    Set FSO = Nothing
    Set oFile = Nothing
    Set oFolder = Nothing
    DoCmd.SetWarnings True
    Exit Sub
    
    errImp:
    MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
    End If
    Exit Sub
    Resume
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 12-29-2015, 09:41 PM
  2. Importing Multiple Tables from a Protected Access Database
    By Terry Lawson in forum Import/Export Data
    Replies: 6
    Last Post: 01-13-2015, 08:03 AM
  3. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 10:19 AM
  4. Importing from multiple spreadhseets across multiple workbooks
    By sgtpsychosis in forum Import/Export Data
    Replies: 2
    Last Post: 06-08-2012, 12:24 PM
  5. Replies: 1
    Last Post: 09-07-2011, 12:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums