Copy multiple files with a recordset

Ok, I know how to pull information from a database with a record set, but I
was wondering if you could actually copy multiple files with it.

I'm trying to go through a series of folders that search for a reference
number, but the file ext can be different and the file isn't always jut the
ref number.
examples:
12345.zip
12345.ba1
12345.bad
12345pan.zip
etc.


folders include
prod
proto
hold

I have a script that will go through and find just the .zip files... I'll
post it incase anyone sees a easier fix.

This script is still in it's infant stages so I haven't gone through and
added everything it needs to do.

Thanks

On Error Resume Next

Set objExplorer = WScript.CreateObject _
("InternetExplorer.Application", "IE_")
Set objShell=CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim strZippedFile ' The full path/filename of the file to unzip
strCommandLine = "pspecs " & strONum
strView = "c:\view\"


objExplorer.Navigate "file:///C:\Scripts\dropdown.htm"
objExplorer.ToolBar = 0
objExplorer.StatusBar = 0
objExplorer.Width = 250
objExplorer.Height = 175
objExplorer.Visible = 1

Do While (objExplorer.Document.Body.All.OKClicked.Value = "")
Wscript.Sleep 250
Loop

strType = objExplorer.Document.Body.All.OKClicked.Value
strONum = objExplorer.Document.Body.All.fldOrderNum.Value
objExplorer.Quit
Wscript.Sleep 250

If strType = "Cancelled" Then
Wscript.Quit
End If

If strONum = NULL Then
wscript.echo "Order number required"
Wscript.Quit
End If
' =====================================================================
' Locates file and copies it over
' =====================================================================
objShell.Run strCommandLine, 1, true
if strType = "Production" then
Dim strCopy, StrK, StrO, strcamb, strHold, strProd
strCopy = "c:\view\" & strONum & ".zip"
strK = "k:\prod\" & strONum & ".zip"
strO = "o:\prod\" & strONum & ".zip"
strcamb = "s:\cambak\prod\" & strONum & ".zip"
strHold = "k:\hold\prod\" & strONum & ".zip"
strProd = "o:\prod\" & intProd(strONum) & "\" & strONum & ".zip"
wscript.echo strProd
if objFSO.FileExists(strK) Then
wscript.echo "Found in the K"
objFSO.CopyFile strK, strCopy, true
elseif objFSO.FileExists(strO) then
wscript.echo "Found in the O"
objFSO.CopyFile strO, strCopy, true
elseif objFSO.FileExists(strcamb) then
wscript.echo "Found in the CAMBAK"
objFSO.CopyFile strcamb, strCopy, true
elseif objFSO.FileExists(strHold) then
wscript.echo "Found in the Hold"
objFSO.CopyFile strHold, strCopy, true
elseif objFSO.FileExists(strProd) then
wscript.echo "Found in the " & intProd(strONum)
objFSO.CopyFile strProd, strCopy, true
else
wscript.echo "File not found"
End if

else
strCopy = strview & "P" & strONum & ".rel"
strproto = "S:\MODEM\REL\P" & strONum & ".rel"
objFSO.CopyFile strProto, strCopy, true
wscript.echo "File Copied"
end if

strComLine2 = "c:\scripts\wzunzip c:\view\" & strONum & ".* c:\view"
objShell.Run strComLine2, 1, true




' =====================================================================
' End
' =====================================================================
Function intProd(ByVal intChar)
Select Case TRUE
Case intChar > 0 and intChar <= 5000
intProd = "01k-5k"
Case intChar > 5001 and intChar <= 10000
intProd = "05k-10k"
Case intChar > 10001 and intChar <= 15000
intProd = "10k-15k"
Case intChar > 15001 and intChar <= 20000
intProd = "15k-20k"
Case intChar > 20001 and intChar <= 25000
intProd = "20K-25k"
Case intChar > 25001 and intChar <= 30000
intProd = "25k-30k"
Case intChar > 30001 and intChar <= 35000
intProd = "30k-35k"
Case intChar > 35001 and intChar <= 40000
intProd = "35k-40k"
Case intChar > 40001 and intChar <= 45000
intProd = "40k-45k"
Case intChar > 45001 and intChar <= 50000
intProd = "45k-50k"
Case intChar > 50001 and intChar <= 55000
intProd = "50k-55k"
Case intChar > 55001 and intChar <= 60000
intProd = "55k-60k"
Case intChar > 60001 and intChar <= 65000
intProd = "60k-65k"
Case intChar > 65001 and intChar <= 70000
intProd = "65k-70k"
Case intChar > 70001 and intChar <= 75000
intProd = "70k-75k"
Case intChar > 75001 and intChar <= 80000
intProd = "75k-80k"
Case intChar > 80001 and intChar <= 85000
intProd = "80k-85k"
Case intChar > 85001 and intChar <= 90000
intProd = "85k-90k"
Case intChar > 90001 and intChar <= 95000
intProd = "90k-95k"
Case intChar > 95001 and intChar <= 100000
intProd = "95k-100k"
Case Else
intProd = "100K"
End Select
End Function

bpaul833
Thu Aug 30 10:54:03 PDT 2007

Also, on a second thought, when I have my IE popup open, I get that dang
yellow bar that blocks my script from running, what do I need to do to allow
it to run uninterupted?

PascalB
Fri Aug 31 00:17:52 PDT 2007

"bpaul833" <bpaul833@discussions.microsoft.com> wrote in message
news:B4EC604A-7471-4742-A584-9C27B6807621@microsoft.com...
> Ok, I know how to pull information from a database with a record set, but
> I
> was wondering if you could actually copy multiple files with it.
>
> I'm trying to go through a series of folders that search for a reference
> number, but the file ext can be different and the file isn't always jut
> the
> ref number.
> examples:
> 12345.zip
> 12345.ba1
> 12345.bad
> 12345pan.zip
> etc.
>
>
> folders include
> prod
> proto
> hold
>
> I have a script that will go through and find just the .zip files... I'll
> post it incase anyone sees a easier fix.
>
> This script is still in it's infant stages so I haven't gone through and
> added everything it needs to do.
>
> Thanks
>
> On Error Resume Next
>
> Set objExplorer = WScript.CreateObject _
> ("InternetExplorer.Application", "IE_")
> Set objShell=CreateObject("WScript.Shell")
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Dim strZippedFile ' The full path/filename of the file to unzip
> strCommandLine = "pspecs " & strONum
> strView = "c:\view\"
>
>
> objExplorer.Navigate "file:///C:\Scripts\dropdown.htm"
> objExplorer.ToolBar = 0
> objExplorer.StatusBar = 0
> objExplorer.Width = 250
> objExplorer.Height = 175
> objExplorer.Visible = 1
>
> Do While (objExplorer.Document.Body.All.OKClicked.Value = "")
> Wscript.Sleep 250
> Loop
>
> strType = objExplorer.Document.Body.All.OKClicked.Value
> strONum = objExplorer.Document.Body.All.fldOrderNum.Value
> objExplorer.Quit
> Wscript.Sleep 250
>
> If strType = "Cancelled" Then
> Wscript.Quit
> End If
>
> If strONum = NULL Then
> wscript.echo "Order number required"
> Wscript.Quit
> End If
> ' =====================================================================
> ' Locates file and copies it over
> ' =====================================================================
> objShell.Run strCommandLine, 1, true
> if strType = "Production" then
> Dim strCopy, StrK, StrO, strcamb, strHold, strProd
> strCopy = "c:\view\" & strONum & ".zip"
> strK = "k:\prod\" & strONum & ".zip"
> strO = "o:\prod\" & strONum & ".zip"
> strcamb = "s:\cambak\prod\" & strONum & ".zip"
> strHold = "k:\hold\prod\" & strONum & ".zip"
> strProd = "o:\prod\" & intProd(strONum) & "\" & strONum & ".zip"
> wscript.echo strProd
> if objFSO.FileExists(strK) Then
> wscript.echo "Found in the K"
> objFSO.CopyFile strK, strCopy, true
> elseif objFSO.FileExists(strO) then
> wscript.echo "Found in the O"
> objFSO.CopyFile strO, strCopy, true
> elseif objFSO.FileExists(strcamb) then
> wscript.echo "Found in the CAMBAK"
> objFSO.CopyFile strcamb, strCopy, true
> elseif objFSO.FileExists(strHold) then
> wscript.echo "Found in the Hold"
> objFSO.CopyFile strHold, strCopy, true
> elseif objFSO.FileExists(strProd) then
> wscript.echo "Found in the " & intProd(strONum)
> objFSO.CopyFile strProd, strCopy, true
> else
> wscript.echo "File not found"
> End if
>
> else
> strCopy = strview & "P" & strONum & ".rel"
> strproto = "S:\MODEM\REL\P" & strONum & ".rel"
> objFSO.CopyFile strProto, strCopy, true
> wscript.echo "File Copied"
> end if
>
> strComLine2 = "c:\scripts\wzunzip c:\view\" & strONum & ".* c:\view"
> objShell.Run strComLine2, 1, true
>
>
>
>
> ' =====================================================================
> ' End
> ' =====================================================================
> Function intProd(ByVal intChar)
> Select Case TRUE
> Case intChar > 0 and intChar <= 5000
> intProd = "01k-5k"
> Case intChar > 5001 and intChar <= 10000
> intProd = "05k-10k"
> Case intChar > 10001 and intChar <= 15000
> intProd = "10k-15k"
> Case intChar > 15001 and intChar <= 20000
> intProd = "15k-20k"
> Case intChar > 20001 and intChar <= 25000
> intProd = "20K-25k"
> Case intChar > 25001 and intChar <= 30000
> intProd = "25k-30k"
> Case intChar > 30001 and intChar <= 35000
> intProd = "30k-35k"
> Case intChar > 35001 and intChar <= 40000
> intProd = "35k-40k"
> Case intChar > 40001 and intChar <= 45000
> intProd = "40k-45k"
> Case intChar > 45001 and intChar <= 50000
> intProd = "45k-50k"
> Case intChar > 50001 and intChar <= 55000
> intProd = "50k-55k"
> Case intChar > 55001 and intChar <= 60000
> intProd = "55k-60k"
> Case intChar > 60001 and intChar <= 65000
> intProd = "60k-65k"
> Case intChar > 65001 and intChar <= 70000
> intProd = "65k-70k"
> Case intChar > 70001 and intChar <= 75000
> intProd = "70k-75k"
> Case intChar > 75001 and intChar <= 80000
> intProd = "75k-80k"
> Case intChar > 80001 and intChar <= 85000
> intProd = "80k-85k"
> Case intChar > 85001 and intChar <= 90000
> intProd = "85k-90k"
> Case intChar > 90001 and intChar <= 95000
> intProd = "90k-95k"
> Case intChar > 95001 and intChar <= 100000
> intProd = "95k-100k"
> Case Else
> intProd = "100K"
> End Select
> End Function

Hi,

I hope to help you out with the different filetypes:

the script searches for every file who has '12345' in the filename and copy
it to the "c:\view" folder.

'==========
strComputer = "."
strFilename = "12345"

Set fso = CreateObject("Scripting.FileSystemObject")

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\"
& strComputer & "\root\cimv2")

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where
FileName like '%" & strFilename & "%'")

For Each objFile in colFiles
Set f = fso.GetFile(objFile.Name)
fso.CopyFile objFile.Name, "c:\view\" & f.name
Next
'===============

Cheers,
PascalB

urkec
Fri Aug 31 08:20:02 PDT 2007

"PascalB" wrote:

> the script searches for every file who has '12345' in the filename and copy
> it to the "c:\view" folder.
>
> '==========
> strComputer = "."
> strFilename = "12345"
>
> Set fso = CreateObject("Scripting.FileSystemObject")
>
> Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\"
> & strComputer & "\root\cimv2")
>
> Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where
> FileName like '%" & strFilename & "%'")
>
> For Each objFile in colFiles
> Set f = fso.GetFile(objFile.Name)
> fso.CopyFile objFile.Name, "c:\view\" & f.name
> Next
> '===============
>
> Cheers,
> PascalB
>
>
>

I have tried using Like with CIM_DataFile class before, but it is too slow
if you don't specify another condition in Where clause (CIM_DataFile.Path or
something else that narrows down the search). Maybe the query could be
something like this:

Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_DataFile " & _
"where (Path = '\\prod\\' " & _
"Or Path = '\\proto\\' " & _
"Or Path = '\\hold\\') " & _
"And FileName like '%" & strFilename & "%'")

--
urkec

dch35920
Sat Sep 01 14:45:41 PDT 2007

Here's something that I came up with (with some help) to copy files
that exist in the SAME folder, but with different file extensions.
There's always a family of three files and there's the possiblity that
the file name that we're going to use with .copyFile already exists.
The extensions are .txt, .atch, .dfts but it will work with any
extension.

The snippet's been edited to provide only the relevant code. Basically
it loops through each file in the designated folder
(strTargetTemplateFolder), splits the file name into file name and
file extension and then checks if the file name matches strFile. If
there's a match, then we check to see if the new file name 'COPY OF
strFileName' name exists. If the file name does exist, then we build
a new name using the 'COPY [n] OF' format.

strTargetTemplateFolder is a global variable pointing to the target
folder

strPrefix = "Copy"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFiles = objFSO.GetFolder(strTargetTemplateFolder).Files
For Each File in objFiles
strFSOSourceFileName = split(file.name, ".")
if strFSOSourceFileName(0) = strFile then
strFSODestinationFileName = file.parentFolder & "\" &
strPrefix & " of " & strFSOSourceFileName(0) & "." &
strFSOSourceFileName(1)
while objFSO.FileExists(strFSODestinationFileName) =
true
strFSODestinationFileName = file.parentFolder &
"\" & strPrefix & " " & i & " of " & file.name
i= i + 1
wend
objFSO.CopyFile file.path, strFSODestinationFileName,
false
end if
i = 1
next