INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I have never been to any technical site that shows concern just to anybody with problems...I look forward to also share in the future..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Languages > VBScript Forum

Open XLS add Filter to selected columns

CODE

Function OK()

Const ForReading = 1
Const ForWriting = 2

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

Set objFile = bjFSO.objExcel("C:\07-05-2012.xls", ForReading)

Excel.Range("A1:Z23").Autofilter

Set objFile = objFSO.objExcel("C:\Result.xls", ForWriting)

objFile.Write strNewContents
objFile.Close

Set objFile = Nothing
Set objFSO = Nothing

End Function

but it doesnt work :(

could you help me ?

jges (TechnicalUser)

8 May 12 8:18

Have a look at FAQ329-7301: How can I list all files with Extension XXX to an Excel Spreadsheet for an example of opening an instance of Excel and manipulating a worksheet.

Leosy (TechnicalUser)

8 May 12 8:43

can't see there anything about autofilter :(

jges (TechnicalUser)

8 May 12 9:03

Not specifically about autofilter, no; but it does demonstrate how to create an instance of Excel and write information to the file (which your current script does not do correctly).

SkipVought (Programmer)

8 May 12 9:23

What are you trying to do here...

CODE

Set objFile = bjFSO.objExcel("C:\07-05-2012.xls", ForReading)

This seems as if you are trying to READ a TEXT file. You can certainly do that, but if you do, there is NOTHING that relates to RANGE and AUTOFILTER. You only get these objects when opening the file using the Excel application like...

CODE

with objExcel.Workbooks.Open("C:\07-05-2012.xls")
   .worksheets(1).Range("A1:Z23").Autofilter
   .save
   .close
end with

Set objExcel = Nothing

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

Leosy (TechnicalUser)

8 May 12 9:24

CODE

  Function OK()

Const ForReading = 1
Const ForWriting = 2

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xls", ForReading)

objWorkbook.Cells(1, 100).Autofilter

Set objWorkbook = objExcel.Workbooks.Open("C:\07-05-2012.xls", ForWriting)

objExcel.Workbooks.Save
objExcel.Workbooks.Close
objExcel.Quit

End Function

I was trying to using this modification but nothing is happening...

SkipVought (Programmer)

8 May 12 9:27

There is no ForReading or ForWriting constant in the Open method.

Your approch is STRANGE!

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

Leosy (TechnicalUser)

8 May 12 9:34

SkipVought

I used

CODE

Set objExcel = CreateObject("Excel.Application")
with objExcel.Workbooks.Open("C:\test1.xls")
   .worksheets(1).Range("A1:Z23").Autofilter
   .save
   .close
end with

Set objExcel = Nothing

and it works perfectly.

But When I wanted to put it in Function (my soft needs this)

it doesn't work.

CODE

Function OK()

Set objExcel = CreateObject("Excel.Application")
with objExcel.Workbooks.Open("C:\2.xls")
   .worksheets(1).Range("A1:Z23").Autofilter
   .save
   .close
end with

Set objExcel = Nothing

End Function

SkipVought (Programmer)

8 May 12 9:55

"it doesn't work."

Meaning.....

1) nothing happens at all, no indications, utter silence, etc.

2) an error occurs

3) something different happens from what you expected

4) something else not included in 1) - 3)

Please be SPECIFIC!

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

Leosy (TechnicalUser)

8 May 12 9:58

answer 1)
It looks like script was executed but no "autofilter was added in to xls"

It's work when VBS looks like this

CODE

Set objExcel = CreateObject("Excel.Application")
with objExcel.Workbooks.Open("C:\test1.xls")
   .worksheets(1).Range("A1:Z23").Autofilter
   .save
   .close
end with

Set objExcel = Nothing

but doesn't work when I put it in to function:

CODE

SkipVought (Programmer)

8 May 12 10:10

You realize that you have consistently used TWO SEPARATE workbooks for what works and what does not.

Is that by design?

Did you try BOTH workbooks in BOTH procedures?

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

Leosy (TechnicalUser)

8 May 12 11:18

Yes.I used 2 workbooks.I tried both workbooks in both procedures
:(

Leosy (TechnicalUser)

9 May 12 3:05

I've tested again with different XLS... maybe it problem with with

CODE

with ...
end with

Is it possible to "change it" somewhow to different "source code" ?

SkipVought (Programmer)

9 May 12 7:28

CODE

Function OK()
  dim wb as object

  Set objExcel = CreateObject("Excel.Application")

  set wb = objExcel.Workbooks.Open("C:\2.xls")

  wb.worksheets(1).Range("A1:Z23").Autofilter
  wb.save
  wb.close

  set wb = nothing
  Set objExcel = Nothing
End Function

Skip,
_{Just traded in my old subtlety...
for a NUANCE!}

Leosy (TechnicalUser)

9 May 12 8:20

crap it's not my day...

Error: Expected and statesment
code: 800A0401
Compilation error

according google

Quote:

Introduction to Code 800A0401

Error code, 800A0401 occurs when you execute a VBScript. This is a compilation error, therefore check your punctuation. A wild guess, you have left out an ampersand (&), or possibly a comma.

Leosy (TechnicalUser)

9 May 12 8:34

Line: 2
Char: 10

jges (TechnicalUser)

9 May 12 8:37

Referencing line numbers won't help us unless you also post the code you are using 'as-is'.

Leosy (TechnicalUser)

9 May 12 8:39

Hi.
vbscript

CODE

Function OK()
  dim wb as object

  Set objExcel = CreateObject("Excel.Application")

  set wb = objExcel.Workbooks.Open("c:\Copy.xls")

  wb.worksheets(1).Range("A1:Z23").Autofilter
  wb.save
  wb.close

  set wb = nothing
  Set objExcel = Nothing
End Function

jges (TechnicalUser)

9 May 12 9:59

Get rid of the "as object" in the variable declaration. In VBScript there are no types only variants.

CODE

Function OK()
  dim wb ~~as object~~

  Set objExcel = CreateObject("Excel.Application")

  set wb = objExcel.Workbooks.Open("c:\Copy.xls")

  wb.worksheets(1).Range("A1:Z23").Autofilter
  wb.save
  wb.close

  set wb = nothing
  Set objExcel = Nothing
End Function

Leosy (TechnicalUser)

9 May 12 10:01

I see no error now but script didn't add autofilter to xls doc :(
my case depressed me now :(

Leosy (TechnicalUser)

9 May 12 10:05

Now it working

CODE

OK

Function OK()
  dim wb

  Set objExcel = CreateObject("Excel.Application")
  set wb = objExcel.Workbooks.Open("C:\BegoExcel\(Bego)-BONDUELLE-Rapport de sauvegarde du 07-05-2012 - Copy.xls")

  wb.worksheets(1).Range("A1:Z23").Autofilter
  wb.save
  wb.close

  set wb = nothing
  Set objExcel = Nothing
End Function

I've defined Function but haven't called it. Called it by putting the OK

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To VBScript

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Open XLS add Filter to selected columns

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

CODE

Quote:

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!