Automate Import Process (1 Viewer)

ted_one

New member
Local time
Today, 02:33
Joined
Nov 24, 2014
Messages
7
I have built an access database which just now I use to;
1. Load in XML file. (External Data > XML File)
2. Run a macro containing multiple queries (these queries add records to a table which build up an interface, which can loaded in to another program).
3. Repeat, until all files have been run through.
 
 
This was great at first due to only dealing with a small amount of XML files, but now the amount of files has grown and will only continue to increase. I would like to know if there is any way to make this automated, for for instance for access to run through every file in a directory.
I would like to know if this is possible to do using VBA?
Thanks.
 

namliam

The Mailman - AWF VIP
Local time
Today, 03:33
Joined
Aug 11, 2003
Messages
11,696
Lookup the DIR function in the access help, that should get you started.

Post back here if you get stuck
 

ted_one

New member
Local time
Today, 02:33
Joined
Nov 24, 2014
Messages
7
I am sorry, I should have pointed out I am complete novice with visual basic.
 

namliam

The Mailman - AWF VIP
Local time
Today, 03:33
Joined
Aug 11, 2003
Messages
11,696
Novice or no, research the DIR function and come back with any problems you find
 

ted_one

New member
Local time
Today, 02:33
Joined
Nov 24, 2014
Messages
7
Very little.

I've read up on it and understand it as allowing me to search a direcorty based on file type?

Code:
Dim Variable As String
 
Variable = Dir("C:\directory\directory\*.xml")

This will pick out all the xml files within the directory?
 

namliam

The Mailman - AWF VIP
Local time
Today, 03:33
Joined
Aug 11, 2003
Messages
11,696
It will pick out the FIRST file in the folder....
Code:
sub x

Dim Variable As String
 
Variable = Dir("C:\directory\directory\*.xml")
do while variable <> ""
    debug.print variable
    variable = dir
loop
end sub
Should list all xml files in the folder.
 

ted_one

New member
Local time
Today, 02:33
Joined
Nov 24, 2014
Messages
7
I know this doesn't work, but is it on the right track?

Code:
Sub x()
Dim Variable As String
 
Variable = Dir("C:\Users\homckeowne1\Desktop\VBA MACRO TEST\*.xml")
Do While Variable <> "" 
    Application.ImportXML Variable, acStructureAndData
    DoCmd.RunMacro "Macro1"
    Variable = Dir
Loop
 
 

End Sub
 

namliam

The Mailman - AWF VIP
Local time
Today, 03:33
Joined
Aug 11, 2003
Messages
11,696
Yes right track, though I wouldnt integrate macro's into VBA.
If all the macro does is call a series of action queries, I would call those queries from the VBA code using CurrentDB.execute "QueryName"
 

ted_one

New member
Local time
Today, 02:33
Joined
Nov 24, 2014
Messages
7
The problem seems to be with the Application.ImportXML, any idea?

Code:
Sub x()
Dim Variable As String
 Variable = Dir("C:\directory\*.xml")
Do While Variable <> ""
    Application.ImportXML DataSource = "C:\directory\*.xml", ImportOptions = acStructureAndData
        CurrentDb.Execute "ALTWALL1"
        CurrentDb.Execute "ALTWALL2"
        CurrentDb.Execute "ALTWALL3"
        CurrentDb.Execute "ALTWALL4"
        CurrentDb.Execute "ALTWALL5"
        CurrentDb.Execute "ALTWALL6"
        CurrentDb.Execute "ALTWALL7"
        CurrentDb.Execute "ALTWALL8"
    Variable = Dir
Loop
End Sub
 

namliam

The Mailman - AWF VIP
Local time
Today, 03:33
Joined
Aug 11, 2003
Messages
11,696
No, sorry, I never use importxml because all I have are failed attempts or broken attempts at using it.

I tend to write some VBA to read the XML file and disect the data from there.
 

Users who are viewing this thread

Top Bottom