Help running external script on a query

[murlen]

I have a query that compares the contains of two folders on my C: drive and list the differences such as the following; ( the following is a list of folders )
UR152W
UR158W
UR458W
UR653X
UR874X
what i need to do is copy each folder to a new location and run a external script on the files in the folder and then loop to the next folder and do the same.

anyone got any ideas!

thanks

 

[dcx693]

What part of this are you having a problem with? To copy files, use the FileCopy statement.

 

[murlen]

this is what I have so far to copy the folders but it's not working
I also have to call a shell and run a script here somewhere as will
but first what am i doing wrong to copy the folders?

Private Sub updates_Click()


Dim rs As ADODB.Recordset
Dim oFileSyS As FileSystemObject
Dim stFile As String
Dim CNE As String
Dim stPath As String
Dim stUpdate As String
Dim strSource As String
Dim strDestination As String

Set oFileSyS = CreateObject("Scripting.FileSystemObject")
Set rs = Queries!new_editions.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
CNE = CStr(rs.Fields("Field5"))
stPath = "C:\rtsUA\ROOT\"
stUpdate = "C:\rtsUA\UPDATES\"
strSource = stUpdate & ENC
strDestination = stPath & ENC

oFileSyS.CopyFolder strSource, strDestination

rs.MoveNext
Loop

rs.Close

Set rs = Nothing
Set db = Nothing
End Sub

 

[dcx693]

I don't think you need the final "\" in your stPath and stUpdate strings, and what is ENC? Here's a link to a Microsoft article with an example of the CopyFolder method: An Introduction to the Scripting Runtime Object Library.

 

[murlen]

thanks dc for the help

the ENC is incorrect in the code, sorry it should have been CNE

the code works to copy the folder if i add in "UR152W" as in the following example, but how do I add the folder name form the record in the Query to the end of the stPath and stUpdate and loop to the next record?
the Query will contain one field (Field5) with records such as;
UR152W
UR168W
UR144X
UR128Z


-------------------------------------------------
Private Sub updates_Click()
On Error GoTo errHandler

Dim fso As Scripting.FileSystemObject
Dim stPath As String
Dim stUpdate As String
Dim strSource As String
Dim strDestination As String

Set fso = New Scripting.FileSystemObject
stPath = "C:\rtsUA\ROOT\UR152W"
stUpdate = "C:\rtsUA\UPDATES\UR152W"
strSource = stUpdate
strDestination = stPath

fso.CopyFolder strSource, strDestination

errHandler:
If Err = "76" Then MsgBox "Please enter a " & _
"valid source folder", vbCritical
Set fso = Nothing

End Sub
-----------------------------------------------------------------

 

[dcx693]

In order to access query results programatically, you need to open the query using a data access method like DAO (typical for Access 97) or ADO (typical for Access 2000 and above).

Here is a link to a post with some basic information about how you can access query results using a recordset: Can VBA code access results of a query?.

 

[murlen]

I finally got it to work, I forgot one line, I needed to set CNE to the currect field value with 'CNE = rs("Field5")'
now the last thing i need to do is run two external batch files from the current folder after the ChDir code. What I need to do is open a cmd window and run two batch files, one sets enviroments and the other process the files in the folder. I tried to run the batch files directly from the 'call shell' comand but it doesnt work properly.

any ideas?

heres the code
-----------------------------------------------------------
Set fso = New Scripting.FileSystemObject
Set db = CurrentDb
Set rs = db.OpenRecordset("new_editions")

rs.MoveFirst
Do While Not rs.EOF

CNE = rs("Field5")
stPath = "C:\rtsUA\ROOT\"
stUpdate = "C:\rtsUA\UPDATES\"
strSource = stUpdate & CNE
strDestination = stPath & CNE

fso.CopyFolder strSource, strDestination
ChDir (strDestination)
Call Shell("c:\winnt\system32\cmd.exe", 1)
rs.MoveNext
Loop
------------------------------------------------------------

 

[dcx693]

What happens when the Shell command is run?

 

[murlen]

if i use Shell "c:\winnt\system32\cmd.exe", 1
the dos window opens and stays open with the dos prompt at the correct folder
but if i go directory to the batch file with Shell "c:\catpro\qrgo.cmd", 1
the dos window opens for a second and closes again and the process is not run on the files.

 

[murlen]

sorry it works fine now
i just combined the two batch files
one batch file was seting enviroments but it was only seting them for the current window

Thanks for all the help!

murlen