473,239 Members | 1,598 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

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 8568
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?
Sep 3 '21 #3
twinnyfo
3,653 Expert Mod 2GB
Delete lines 4-6 from my code it is unnecessary for this to work.
Sep 3 '21 #4

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

Similar topics

5
by: Roy | last post by:
My dll file is copied to the "%windir%\Microsoft.NET\Framework\{version}Temporary ASP.NET Files" folder when I compile my asp.net project. When I run the application. The dll under this folder is...
7
by: cannunzi | last post by:
I'm trying to import multiple files in one access table all at once but i keep hitting an error stating incorrect file path. The code below works when i change the * to the exact file name but then...
1
by: Music Man | last post by:
Greetings All: I built a database in Microsoft SQL Server 2000 and used Microsoft Access 2010 as the front end. The database is used to keep track of "issues" that rise out of my employment. ...
0
Tyler Wiebe
by: Tyler Wiebe | last post by:
I would like to know how to import files, more specifically, mp3 files. I'm trying to make a mp3 player application that fits my taste in design. I'd like to have a simple importing system. All...
3
AR Ratheesh
by: AR Ratheesh | last post by:
Hi, I have Access 2010 and When i'm trying to Export Text files to System folder ("C:\Windows\abc.asc"), in Windows 7 ultimate, couldn't export. But Previously i successfully exported to...
2
by: Paul Howarth | last post by:
My Access 2010 database is suddenly not appending Named Ranges from Excel into and Existing Access 2010 Table. I have not written any code. I simply right-click the Table-Import-Excel and follow...
2
by: shannonsims | last post by:
In an effort to digitize our personnel files, I am currently building an Access database that tracks stores employee information, to include admin documents, leave requests, training docs etc. After...
0
by: rahul2310 | last post by:
how many users can share access 2010 database on Network Folder If operating system is microsoft Windows XP Or Windows 7
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.