Jump to content
Sign in to follow this  
Dizzy

How to act with ShowAllData in Excel

Recommended Posts

Dizzy

Hi,

i've a little problem with filter in excel.

I want to open an existing *.xlsx document and delete a special range.

Do to this, i've to clean any filters.

This schould work whether filters are set or not.

Here is a piece of code:

Opt("MustDeclareVars", 0)
Global $excelfile = @ScriptDir & "\new.xlsx"

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oExcel.Workbooks.Open ($excelfile)

;This won't work
$oExcel.ActiveSheet.ShowAllData

$oExcel.Range ("A1").Select
$oExcel.ActiveWorkbook.Save
$oExcel.Workbooks.close
$oExcel.Quit

Thx

Dizzy

Share this post


Link to post
Share on other sites
Dizzy

OK - i found a solution in VBA. :D

I didn't have to use ShowAllData ...

Opt("MustDeclareVars", 0)

Global $exceldatei = @ScriptDir & "\new.xlsx"

$oExcel = ObjCreate("Excel.Application")

$oExcel.Visible = 0

$oExcel.Workbooks.Open ($exceldatei)

; Set AutoFilterMode to False if it was used

If $oExcel.ActiveSheet.AutoFilterMode then $oExcel.ActiveSheet.AutoFilterMode=False

$oExcel.Range ("A1").Select

$oExcel.ActiveWorkbook.Save

$oExcel.Workbooks.close

$oExcel.Quit

Thx

Dizzy

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×