Open XLS add Filter to selected columns

[Leosy]

Hello.

I need open XLS file and add "filter"/"autofilter" columns.
Could you help me with this ?

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]

Have a look at faq329-7301 for an example of opening an instance of Excel and manipulating a worksheet.

 

[Leosy]

can't see there anything about autofilter

 

[jges]

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]

What are you trying to do here...

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...

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

Set objExcel = Nothing

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

  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]

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

Your approch is STRANGE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

SkipVought

I used

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.

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]

"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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

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

It's work when VBS looks like this

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:

[b]Function OK()[/b]

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

[b]End Function[/b]

 

[SkipVought]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

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

 

[Leosy]

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

with ...
end with

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

 

[SkipVought]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Leosy]

crap it's not my day...

Error: Expected and statesment
code: 800A0401
Compilation error

according google

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]

Line: 2
Char: 10

 

[jges]

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

 

[Leosy]

Hi.
vbscript

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]

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

Function OK()
  dim wb [COLOR=red][s]as object[/s][/color]

  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]

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