renaming folder from db objects

[egr4wig]

Hi all,

Any help would be greatly appreciated, but I am trying to rename a folder using a couple of DB object variables and I'm having a tough go at it. I have tried to convert the objects to strings, but have found out that that doesn't really work.

My object variables are:
Dim Value1: Set Value1 = CreateObject("ADODB.Recordset")
Dim Value2: Set Value2 = CreateObject("ADODB.Recordset")
Set Value1 = connection.Execute("SELECT MAX(RUN_NUMBER)...")
Set Value2 = connection.Execute("SELECT RUN_CLIENTID...")

My object folder is the newest folder in:
Dim objFolder: Set objFolder = objFSO.GetFolder("C:\Program Files\Doc_Finder\Docs")

I find the newest with the following:
For Each objFolder in colFolders
If objFolder.DateCreated > dDate Then
dDate = objFolder.DateCreated
strNewestFolder = objFolder
End If
Next

WScript.Echo strNewestFolder

and finally I'm trying to rename the newest folder with the following statement:
fso.MoveFile objFolder & "\" & strNewestFolder, objFolder & "\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS"

As you can imagine I am getting a 'Type mismatch error' code: 800A000D

Any help would be greatly appreciated!
Thanks,
egr4wig

 

[guitarzan]

I'm not able to test, so I don't know that all the code below is correct, but this should at least get you towards working code

Dim rs, Value1, Value2
Set rs = connection.Execute("SELECT MAX(RUN_NUMBER)...")
Value1 = rs("field_name")

Set rs = connection.Execute("SELECT RUN_CLIENTID...")
Value2 = rs("field_name")

[green]'replace "field_name" with the correct field you want from in the query[/green]

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objFolder, objSubFolder
Set objFolder = objFSO.GetFolder("C:\Program Files\Doc_Finder\Docs")

For Each objSubFolder in objFolder.Subfolders
   If objSubFolder.DateCreated > dDate Then
      dDate = objSubFolder.DateCreated
      strNewestFolder = objSubFolder.Path
   End If
Next

And to rename the folder, I believe this will work:

objFSO.GetFolder(strNewestFolder).Name = "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS"

 

[egr4wig]

Thanks for the help guitarzan. It was throwing some errors while trying to write to a log so I just too that part out.
Thanks again!

 

[egr4wig]

Hi guitarzan,

I was hoping you'd be able to help me with one more thing. I am trying to zip the folder, but I'm getting an ActiveX component error with the code:

'Zip the folder
Dim FolderToZip
Set FolderToZip = CreateObject("N:\Client\Doc_Extractor\Docs\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS")
Dim zipFile
Set zipFile = CreateObject("N:\Client\Doc_Extractor\Docs\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS.zip")

'Dim sa:
set sa=CreateObject("Shell.Application")
'Dim zip:
Set zip=sa.NameSpace(zipFile)
'Dim Fol:
Set Fol=sa.NameSpace(FolderToZip)
zip.CopyHere(Fol.Items)
WScript.Sleep 2000 ' increase this if the folder is large

Any help would be greatly appreciated.

 

[PHV]

Replace this:
Set FolderToZip = CreateObject("N:\Client\Doc_Extractor\Docs\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS")
Set zipFile = CreateObject("N:\Client\Doc_Extractor\Docs\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS.zip")
with this:
FolderToZip = "N:\Client\Doc_Extractor\Docs\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS"
zipFile = FolderToZip & ".zip"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[egr4wig]

Hi PHV,

I'm getting a error from the zip.CopyHere(Fol.Items) line stating:
Object required
Code 800A01A8

Any ideas?

 

[PHV]

I'd try this:
zip.CopyHere Fol.Items
or this:
Call zip.CopyHere(Fol.Items)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[egr4wig]

Mmmm, neither of those seem to produce a different result. Still getting the object required error. Would there be any other methods I should try?

 

[PHV]

Does the zip file exists prior this ?
Set zip=sa.NameSpace(zipFile)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[egr4wig]

I am trying to zip a folder, but yes the variable 'zipFile' is declared before hand. I have commented out the set statement as per previous instructions:

'Zip the folder
Dim FolderToZip
'Set FolderToZip = CreateObject("N:\Alternative_Service_Concepts\Doc_Extractor\Docs\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS")
FolderToZip = "N:\Alternative_Service_Concepts\Doc_Extractor\Docs\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS" & ".zip"
Dim zipFile
'Set zipFile = CreateObject("N:\Alternative_Service_Concepts\Doc_Extractor\Docs\" & "CE_" & Value2 & "_" & Value1 & "_PROD_ATTACHMENTS.zip")

Dim sa: 
set sa=CreateObject("Shell.Application")
Dim zip: 
Set zip=sa.NameSpace(zipFile)
Dim Fol: 
Set Fol=sa.NameSpace(FolderToZip)
'zip.CopyHere(Fol.Items)
'zip.CopyHere Fol.Items
Call zip.CopyHere(Fol.Items) 
'zip.CopyHere("Fol")

Thanks for your continued help! I really do appreciate it.

 

[guitarzan]

egr4wig:
It looks like you didn't implement PHV's advice earlier (to properly assign values to FolderToZip and zipFile). But in addition, some searches indicate that creating a zip file natively requires more steps than you are doing.

If you still have troubles, check this thread; includes some ideas to zip files natively in vbscript as well as mentioning 7Zip, a free program that may work better for you.
thread329-1532406

 

[PHV]

Have a look here:
thread329-1231429

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886