Loop over a directory full of documents

I have the following code:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "Provider=SQLOLEDB;Driver={SQL Server};Server=(local);Database=fsbosys;Trusted_Connection=yes;"
objBL.ErrorLogFile = "c:\error.xml"
objBL.Execute "c:\schoolmapping.xml", "E:\inetpub\wwwroot\xmls\local-greatschools-feed-AK.xml"
Set objBL = Nothing

This works great for one document like this "local-greatschools-feed-AK.xml".  But the "E:\inetpub\wwwroot\xmls\" directory has about 100 documents in it and I do not want to have to manually change the code for each document.

How can I read the contents of "E:\inetpub\wwwroot\xmls\" then loop over this code inserting "local-greatschools-feed-AK.xml" for the name of the file?

Thanks

jfill
LVL 1
jfill89Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ironwill96Commented:
Have you tried using a for each statement to iterate the contents of the directory and then just plug-in the filenames to your statement.

You could do this with a file control.

I will post code for this in a sec.

Nathan
ironwill96Commented:
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
    objBL.ConnectionString = "Provider=SQLOLEDB;Driver={SQL Server};Server=(local);Database=fsbosys;Trusted_Connection=yes;"
    objBL.ErrorLogFile = "c:\error.xml"
    File1.Path = "E:\inetpub\wwwroot\xmls\"
    For i = 0 To File1.ListCount - 1
        File1.Selected (i)
        objBL.Execute "c:\schoolmapping.xml", "E:\inetpub\wwwroot\xmls\" & File1.FileName
    Loop
    Set objBL = Nothing

Just add a FileListBox control to your form called "File1".  I hope I interpreted what you wanted correctly.

Nathan

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ADSaundersCommented:
Hi jfill89,
Here is my ubiquitous file recursion routine in VBScript, modified to add your code. (Obviously, the additions have not been tested!)
Very easy to convert to VB. These two subs, andlefolder, and handlefile will (as written) recurse the folder tree from a 'root' folder, handling each file found as specified in handlefile.
If the source folder is 'flat' (i.e. contains no subfolders with files to be processed) then comment out the lines starting
For Each f In folder.SubFolders
in the handlefolder sub.
If the folder(s) contain other files, then uncomment the file extension test.
If you wish to move, or delete the files after processing, add the appropriate code to the handlefile sub.

Just c&p to a .vbs file and run it after required changes, or add to your VB project.

Regards .. Alan

private const SourcePath = "E:\inetpub\wwwroot\xmls\" ' put your source directory here
private sub HandleFile (byval f)
      objBL.Execute "c:\schoolmapping.xml", f.path
end sub

Private Sub handlefolder(ByVal folder)
' This (recursive) sub will first of all handle all sub-folders in it's parameter target
' then all files.
' Only need this next 'for each' if you need to to recurse subdirectories in the parameter target
  For Each f In folder.SubFolders ' may not be necessary if there are no subfoldrs to recurse
    handlefolder  f               '   ''          ''
  Next                            '   ''          ''
  for each f in folder.files ' Handle each of the file objects
        ' Possibly add
        'if lCase(f.extensionname) = "xml" then _
    handlefile f
  next
End Sub

' Main Routine starts here
Dim fso, fo, objBL
Set fso = CreateObject("Scripting.filesystemobject")
' Get handle to input folder
Set fo = fso.GetFolder(SourcePath)
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "Provider=SQLOLEDB;Driver={SQL Server};Server=(local);Database=fsbosys;Trusted_Connection=yes;"
objBL.ErrorLogFile = "c:\error.xml"
handlefolder fo
Set fso = Nothing
Set fo = Nothing
Set objBL = Nothing
ADSaundersCommented:
Hi, Even for 50 points (:-}) I'm interested in it's disposition.

.. Alan
ironwill96Commented:
I think both provided answers are valid solutions.   So up to you what to do with the points.

Nathan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.