469,140 Members | 1,118 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,140 developers. It's quick & easy.

import xml files which are in folder and subfolder into access 2010

1
hello
i have a question about access 2010 vba
i am novice in programming

so please help me
i have main folder and
there are many sub folder in the main folder

in the subfolder, there are many xml files

my request
programming import all xml files in the first subfolder
and loop the import xml files from first to last subfolder

my main folder : C:\Users\Guest-1\Desktop\test
and sub folder name : C:\Users\Guest-1\Desktop\test\te1
C:\Users\Guest-1\Desktop\test\te2
C:\Users\Guest-1\Desktop\test\te3
.....



Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub cmdImport_Click()
  4. Dim strFile As String 'Filename
  5. Dim strFileList() As String 'File Array
  6. Dim intFile As Integer 'File Number
  7. Dim strPath As String ' Path to file folder
  8.  
  9.     strPath = "C:\Users\Guest-1\Desktop\test\"
  10.     strFile = Dir(strPath & "*.XML")
  11.  
  12.  
  13.  ''''
  14.     While strFile <> ""
  15.          'add files to the list
  16.         intFile = intFile + 1
  17.         ReDim Preserve strFileList(1 To intFile)
  18.         strFileList(intFile) = strFile
  19.         strFile = Dir()
  20.     Wend
  21.  
  22.      'see if any files were found
  23.     If intFile = 0 Then
  24.         MsgBox "No files found"
  25.         Exit Sub
  26.     End If
  27.  
  28.     'cycle through the list of files
  29.     For intFile = 1 To UBound(strFileList)
  30.         Application.ImportXML strPath & strFileList(intFile), 2
  31.     Next intFile
  32.  ''''
  33.  
  34.  
  35.     MsgBox "Import Completed"
  36.  
  37. End Sub
Feb 13 '15 #1
3 5992
twinnyfo
3,653 Expert Mod 2GB
There is a method for gathering folder information recursively, so that you are searching all folders under a main folder.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Private Sub cmdClose_Click()
  5.     DoCmd.Close acForm, Me.Form.Name
  6. End Sub
  7. Private Sub cmdMusic_Click()
  8. On Error GoTo EH
  9.     Dim strPath As String
  10.     Dim colFiles As New Collection
  11.     Dim vFile As Variant
  12.     Dim intFiles As Integer
  13.     Dim strFile As String
  14.     Dim strPath As String
  15.     Dim strFileTemp As String
  16.     Dim strPathTemp As String
  17.     Dim intPass As Integer
  18.     Dim fSwap As Boolean
  19.     Dim intNameLen As Integer
  20.     Dim xlApp As Excel.Application
  21.     Dim xlBook As Excel.Workbook
  22.     Dim xlSheet As Excel.Worksheet
  23.     Dim intRow As Integer
  24.  
  25.     strPath = "C:\YourPath\"
  26.     RecursiveDir colFiles, strPath, "*.xml", True
  27.     intFiles = colFiles.Count
  28.     For Each vFile In colFiles
  29.         'Import Your File
  30.     Next vFile
  31.     MsgBox "Your import has completed!", vbOKOnly, "All Done!"
  32.     Exit Sub
  33. EH:
  34.     If Err.Number = 75 Then Resume Next
  35.     MsgBox "There was an error importing!  " & _
  36.         "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
  37.     Exit Sub
  38. End Sub
  39. Private Function RecursiveDir(colFiles As Collection, _
  40.     strFolder As String, _
  41.     strFileSpec As String, _
  42.     bIncludeSubfolders As Boolean)
  43. On Error GoTo EH:
  44.     Dim strTemp As String
  45.     Dim colFolders As New Collection
  46.     Dim vFolderName As Variant
  47.  
  48.     'Add files in strFolder matching strFileSpec to colFiles
  49.     strFolder = TrailingSlash(strFolder)
  50.     strTemp = Dir(strFolder & strFileSpec)
  51.     Do While strTemp <> vbNullString
  52.         colFiles.Add strFolder & strTemp
  53.         strTemp = Dir
  54.     Loop
  55.     If bIncludeSubfolders Then
  56.         'Fill colFolders with list of subdirectories of strFolder
  57.         strTemp = Dir(strFolder, vbDirectory)
  58.         Do While strTemp <> vbNullString
  59.             If (strTemp <> ".") And (strTemp <> "..") Then
  60.                 If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
  61.                     colFolders.Add strTemp
  62.                 End If
  63.             End If
  64.             strTemp = Dir
  65.         Loop
  66.         'Call RecursiveDir for each subfolder in colFolders
  67.         For Each vFolderName In colFolders
  68.             Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
  69.         Next vFolderName
  70.     End If
  71.     Exit Function
  72. EH:
  73.     If Err.Number = 75 Then Resume Next
  74. End Function
  75. Private Function TrailingSlash(strFolder As String) As String
  76.     If Right(strFolder, 1) <> "\" Then
  77.         TrailingSlash = strFolder & "\"
  78.     Else
  79.         TrailingSlash = strFolder
  80.     End If
  81. End Function
I hope this works..... I cut/pasted/slaughtered it from a patch of code I have at home.
Feb 19 '15 #2
ccming
1 Bit
@twinnyfo Thanks for you code.
After I ran your code, Access prompt "Invalid use of Me keyword",
What should I change?
3 Weeks Ago #3
twinnyfo
3,653 Expert Mod 2GB
Delete lines 4-6 from my code it is unnecessary for this to work.
3 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.