vbscript problem

[columbo1977]

Hi All

I have a vbscript problem, I am trying to lop through all excel files in folders and subfolders and if a certain range ie F60 is less than or equal to 2 either printout the document or save to a specific location.

any help would be appriciated.

Set oShell = CreateObject("W script.Shell")
strUserProfile = oShell.ExpandEn vironmentString s("%USERPROFILE %")

strPath = strUserProfile &"\Desktop\Exce l_Files_To_Prin t"

Set objExcel = CreateObject("E xcel.Applicatio n")
objExcel.Visibl e = True
objExcel.Displa yAlerts = False

Set objFso = CreateObject("S cripting.FileSy stemObject")
Set objFolder = objFso.GetFolde r (strPath)

For Each objFile In objFolder.Files

If objFso.GetExten sionName (objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbo oks.Open(objFil e.Path)
objworkbook.wor ksheets("Assess ment").Activate

objworkbook.wor ksheets("Assess ment").cells(F6 0).value = <3

objWorkbook.wor ksheets("Assess ment").Printout
objWorkbook.Clo se True 'Save changes
End If

Next

objExcel.Quit

Graham

[Nulli cedo]

What is the problem?

[columbo1977]

Hi

Sorry didnt realise I hadnt said :P

This part isnt working

If objFso.GetExten sionName (objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbo oks.Open(objFil e.Path)
objworkbook.wor ksheets("Assess ment").Activate

objworkbook.wor ksheets("Assess ment").cells(F6 0).va lue = <3

objWorkbook.wor ksheets("Assess ment").Printout
objWorkbook.Clo se True 'Save changes
End If

I need it to check the range to see if it is under 3 then print or save as if this condition is met.

Thanks for looking

Graham

[Nulli cedo]

Not much wrong really, you missed a "if/then" statement to print the workbook yes or no, and you used "cells" instead of "range" to point to the location of the cell in the sheet. Range always requires quotes "F60" if not populated as variable.

if it does not work on your side then let me know the errormessage.

Set objFso = CreateObject("S cripting.FileSy stemObject")
Set objFolder = objFso.GetFolde r (strPath)

For Each objFile In objFolder.Files

If objFso.GetExten sionName (objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbo oks.Open(objFil e.Path)
objworkbook.wor ksheets("Assess ment").Activate

objworkbook.wor ksheets("Assess ment").Range("F 60").value = <3

objWorkbook.wor ksheets("Assess ment").Printout
objWorkbook.Clo se True 'Save changes
End If

Next

objExcel.Quit

[Nulli cedo]

Sorry, wrong code! find the correct code below

For Each objFile In objFolder.Files

If objFso.GetExten sionName(objFil e.Path) = "xls" Then
Set objWorkbook = objExcel.Workbo oks.Open(objFil e.Path)
naam = objWorkbook.Nam e
objWorkbook.wor ksheets("Assess ment").Activate
If objWorkbook.wor ksheets("Assess ment").Range("F 60").Value <= 3 Then
objWorkbook.wor ksheets("Assess ment").Printout
End If

objWorkbook.Clo se 'True 'Save changes
End If

Next

[columbo1977]

Hey

That is good it works fine but.... :)

I Need it to check more than one range as there are different versions of the same spreadsheet ie this isnt working as it keeps printing them all??

For Each objFile In objFolder.Files

If objFso.GetExten sionName(objFil e.Path) = "xls" Then
Set objWorkbook = objExcel.Workbo oks.Open(objFil e.Path)
naam = objWorkbook.Nam e
objWorkbook.wor ksheets("Assess ment").Activate
If objWorkbook.wor ksheets("Assess ment").Range("D 56").Value <3 or objWorkbook.wor ksheets("Assess ment").Range("E 55").Value <3 or objWorkbook.wor ksheets("Assess ment").Range("D 45").Value <3 Then
objWorkbook.wor ksheets("Assess ment").Printout
End If

objWorkbook.Clo se 'True 'Save changes
End If

Next


also how can I tell it to check all subfolders as well?

Thanks for the help so far.

Graham