Excel filter error

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Excel filter error

Logged in as: Guest

arrSession:exec spGetSession 2,2,62137

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Excel filter error
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Excel filter error - 7/2/2008 11:34:49 PM

stubar

Posts: 48
Score: 0
Joined: 7/14/2005
Status: offline

Hello

I have an hta that helps check multiple login scripts. It goes off to the PDC copies the bat files into a folder and generates a specific line in them, if it exists, with an option to view the whole script. Everything works as it should.
However, when I added a sub to export the data to Excel I ran into a problem.
I thought if recorded a macro and then used that in my script it would work, alas no. Some does, some doesn't.

This is the sub and the section in red generates the error. REM that line and it works.

Sub ExportFiles
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2
objExcel.Cells(1, 1).Value = "O Drive"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("Export.txt", 1)
Do Until objFile.AtEndOfStream
   StrODrives = objFile.ReadLine
         objExcel.Cells(intRow, 1).Value = StrODrives
         intRow = intRow + 1
        ' next
Loop
   objExcel.Columns("A:A").Select
   objExcel.Cells.EntireColumn.AutoFit
objExcel.Selection.AutoFilter
  ' REM objExcel.Selection.AutoFilter Field:=1, Criteria1:="=*persi*", Operator:=xlAnd
   objExcel.Range("d12").Select

End Sub

Post #: 1

RE: Excel filter error - 7/3/2008 12:23:20 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

The := notation is not valid in VBScript. Instead, read the documentation for the autofilter Method and just give the parameters in the proper order.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to stubar)

Post #: 2

RE: Excel filter error - 7/3/2008 12:41:30 AM

stubar

Posts: 48
Score: 0
Joined: 7/14/2005
Status: offline

Thanks
I didn't think it was valid and I've now found the answer is to replace that line with objExcel.Selection.AutoFilter 1,"=*persi*", 1

What documentation do you refer to? I looked all over on line and couldn't find much and there's not much help in excel either.

(in reply to ebgreen)

Post #: 3

RE: Excel filter error - 7/3/2008 12:45:29 AM

ebgreen

Posts: 4613
Score: 31
Joined: 7/12/2005
Status: offline

Look in the Office install folder. For me it is :

C:\Program Files\Microsoft Office\OFFICE11\1033

In there you will find several help files. In your case the VBAXL10.CHM file is the Excel VBA object model that would tell you what you need.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to stubar)