Error in VB Scripting

[vijay.vizzu]

Dear All


Please find below code. I want to use this as Task Schedular script. So i saved this as .vbs. When i have first executed it, it works fine & next time it gives error like "The extract range has a missing or illegal field name." in the specified file. If i open that excel file and executive the macro "AdvanceFilter" it works. Why this error appears while executing through script file. I can't able to find out the error.


From last two days, i am scratching my head, but still can't resolve. So please help me to solve this


Below is my code which i am using it as script

[pre]

' ================================================
dim objExcel,objFSO,objFolder,objFileName

set ObjExcel = createobject("excel.application")
set objFSO = createobject("scripting.filesystemobject")

set objFolder = objfso.getfolder("w:DPS 14-02-13TESTING")

for each objFilename in objfolder.files
objexcel.workbooks.open (objfilename)
objexcel.visible = false

objexcel.run "AdvanceFilter"
objexcel.run "ReadyReport"

objexcel.activeworkbook.close false
objexcel.quit

next

objexcel.workbooks.open("W:Quote PendencyQP.xlsm")
objexcel.visible = false

objexcel.run "extractData"
objexcel.activeworkbook.close false
objexcel.quit

[/pre]
Regards

vijay

 

[crouchsw]

There is only one file in the TESTING folder? If there are more, they all need the AdvanceFilter and ReadyReport macro in them. What line exactly is it failing on?

 

[vijay.vizzu]

Dear crouchsw


When i try to executive the script file, every time my code will error in the line .AdvancedFilter (became highlighted in yellow color) error was illegal field name. But now i have changed the code (Latest code) and it works. There are around 15 files in Testing folder. In every file, there is a Macro called AdvanceFilter and ReadyReport.


But still i am in confusion that, if i tried the intial code in that file (04X-South), then it works, but while executing through Script file, it gives error.


In my opinion, both the codes will do same thing, but why it throws error in Script file. If you know, then please let me know


Intial Code :

================================================================================

Sub AdvanceFilter()

'This code will display pendency in current sheet

Dim filterws As Worksheet

Dim dataws As Worksheet


Set filterws = Sheets("filters")


Range("b6").CurrentRegion.Clear

Set dataws = Sheets("04X-SOUTH")

With dataws.Range("fltRange")

.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=filterws.Range("fltcriteria"), copytorange:=Range("b6"), unique:=False

End With

Range("b7").Select

'MsgBox ("Total No. of Records: " & IIf(Range("b7") = "", 0, Range(Selection, Selection.End(xlDown)).Count))

End Sub

---------------------------------------------------------------------------------

Latest Code :

==================================================================================

Sub AdvanceFilter()

Sheets("filters").Select

Range("b6").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Clear


Worksheets("04X-SOUTH").Range("fltRange").AdvancedFilter _

Action:=xlFilterCopy, _

criteriarange:=Worksheets("Filters").Range("fltCriteria"), _

copytorange:=Range("b6"), _

unique:=False

Range("b7").Select

'MsgBox ("Total No. of Records : " & IIf(Range("b7") = "", 0, Range(Selection, Selection.End(xlDown)).Count))


End Sub


After change the code, now my script running successfully without any error

Thanks to all


Regards

Vijay