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("Wscript.Shell")
strUserProfile = oShell.ExpandEnvironmentStrings("%USERPROFILE%")

strPath = strUserProfile &"\Desktop\Excel_Files_To_Print"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName (objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
objworkbook.worksheets("Assessment").Activate

objworkbook.worksheets("Assessment").cells(F60).va lue = <3

objWorkbook.worksheets("Assessment").Printout
objWorkbook.Close 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.GetExtensionName (objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
objworkbook.worksheets("Assessment").Activate

objworkbook.worksheets("Assessment").cells(F60).va lue = <3

objWorkbook.worksheets("Assessment").Printout
objWorkbook.Close 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("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName (objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
objworkbook.worksheets("Assessment").Activate

objworkbook.worksheets("Assessment").Range("F60"). value = <3

objWorkbook.worksheets("Assessment").Printout
objWorkbook.Close 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.GetExtensionName(objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
naam = objWorkbook.Name
objWorkbook.worksheets("Assessment").Activate
If objWorkbook.worksheets("Assessment").Range("F60"). Value <= 3 Then
objWorkbook.worksheets("Assessment").Printout
End If

objWorkbook.Close '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.GetExtensionName(objFile.Path) = "xls" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
naam = objWorkbook.Name
objWorkbook.worksheets("Assessment").Activate
If objWorkbook.worksheets("Assessment").Range("D56"). Value <3 or objWorkbook.worksheets("Assessment").Range("E55"). Value <3 or objWorkbook.worksheets("Assessment").Range("D45"). Value <3 Then
objWorkbook.worksheets("Assessment").Printout
End If

objWorkbook.Close '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