Iterate thru folder process each file?

[deBassMan]

hi guys

here's some background ...

Using an Access 2003 db, remote offices email data to Head Office as attached xml files (is xml a good choice?)

Incoming filenames are in the format: Process_3410_03.xml

At HO incoming files as above are saved in a particular folder. (Incidentally I would like to automate that process).

I'd like to automatically iterate thru each one, appending all the rows to an exisiting 'master' file.

Each incoming file would be renamed and/or moved to another folder for archiving.

The master file would then be further processed.

I have searched the forum with little success.

I appreciate your time/help/experience.

cheers

 

[gemma-the-husky]

In Access,

use dir command to iterate a folder

external file handling commands are name (to rename), filecopy and kill (to delete)

import text with transfertext, although i am sure there is a different command to handle xml - can't help you there.

lots of sql commands to maketables etc.

work out your macrocode loops and if you get stuck i'm sure you'll get specific help here.

 

[deBassMan]

Yeah - I've used DIR before but I don't believe that offers a way to work with the individual files.

TransferText is not appropriate for xml, I'm familiar with SQL.

thanks for your help anyway (hope your throat gets better)

deBassMan

 

[gemma-the-husky]

dir will call up each filename in turn (eg use parameter *.xml to get xml files) - you can then process the file with code - i haven't used xml, but there must be programmatic ways to import an xml file/schema etc.

 

[deBassMan]

OK I've sussed it out.

First I gave up on xml (could not seem to connect via ADO) and went back to good 'ol xls format.

I used FileSearch to iterate thru all files in a named folder and used TransferSpreadsheet to copy the contents of each file to a temporary table.

In the loop I renamed each file to ensure that it would only be processed once.