Set filter in excel file

[Bede]

Hi,

Using PB 9.0.3 and Excel8!

say i have 3 columns in an excel file: REF_ID, QUESTION, ANSWER

When connecting in PB through OLE, i want to only process the rows where
the 3rd column (ANSWER) has been entered.

I'm trying stuff like:

// Excel macro looks like this
// Selection.AutoFilter
// AutoFilter Field:=3, Criteria1:="<>"

// Filter only entered data
ole_excel.application.Workbooks(ls_excelfile).worksheets
(1).Selection.AutoFilter
ole_excel.application.Workbooks(ls_excelfile).worksheets
(1).Selection.AutoFilter.Field=3
ole_excel.application.Workbooks(ls_excelfile).worksheets
(1).Selection.AutoFilter.Criteria1="<>"

// So i thot i could use it in PB as such..
// but i'm getting an execution error at ".Selection"

obviously i'm not at all experienced in this area.
Can somebody give me a push in the right direction?

TIA,

Ben

[PascalB (Search4)]

Hi Bede,

All ole-functionality can be found in excel.
Start Excel, press ALT + F11 then press F2, this will popup the excel Object
Browser.

Else use the macro-recorder to get an idea on how the script should look.

Greets,
Pascal B
( PS: Say hi to Bart and Johan )

"Bede" <bede_nosp@m_sofico.be> wrote in message
news:MPG.1ececa069...@forums.sybase.com...

[PascalB (Search4)]

Hi Bede,

I didn't see that you mentioned the use of the macro recorder....it's also
monday morning for me :-)

Processing the rows; would this be in Powerbuilder?
- If so, why not reading the entire sheet into a datastore and then filter
the datastore!?
- If not, then the excel object browser hopefully should come up with
answers and solutions.

Greets,
Pascal B

"PascalB (Search4)" <ma...@mail.mail> wrote in message
news:446822b3$1@forums-1-dub...

[Bede]

thanks pascal,

- i was thinking about the first option (reading in a datastore &
filtering in PB) too, but importFile() doesn't seem to be able to read
an excel file (and not CVS) "as-is" into a PB datastore in one go. (i
cannot use OLE-DB/ODBC either, too much effort for the customer)
Can you explain how you read an excel into pb datastore in 1 go?
=> i can read row by row, but that's too slow. Since the excel file
would contain thousands of rows, it would make more sense to only
process the filled-in rows. In that sense pulling all rows into a pb
datastore would still mean a lot of processing for PB.. although i'm
sure PB would be able to process it faster than using OLE.

- excel object browser seems like an interesting starting point, but it
still doesn't explain how i should really code my OLE call in PB.

// Excel macro looks like this
// Selection.AutoFilter
// AutoFilter Field:=3, Criteria1:="<>"

I'm getting a NullObjectReference on the first line.. the lole_sheet
contains a reference to the ExcelSheet, which is valid. According to the
excel object browser i was assuming i needed to do something like this:

lole_sheet.AutoFilter.Filters.Item(3).Filter.On=True
lole_sheet.AutoFilter.Filters.Item(3).Filter.Criteria1="<>"

i've tried with "lole_sheet.AutoFilterMode=True" instead, but that also
fails

any ideas?
TIA,

Ben

PS: you have lots of "west-vlomse groeten" back from both of them!

In article <446826fa@forums-1-dub>, ma...@mail.mail says...

[PascalB (Search4)]

Hi Bede,

reading the entire file in 1 go to a datastore is not an option...
reading row by row thru ole takes a lot of time and intensive memory usage.

There is an alternative solution for fast processing and les memory usage.

1. get the used range of the sheet.(ole)
2. copy the sheet to clipboard (ole)
3. copy the clipboard into a string (PB)
4. let PB do the work

Sample script:

li_ret = ole_excel.ConnectToObject( ls_FileAndPath )
IF li_ret = 0 THEN
ole_excel.application.DisplayAlerts = False
ole_excel.application.Workbooks(ls_FileName).Activate
ole_excel.application.Workbooks(ls_FileName).Worksheets(1).Activate

ll_cols =
ole_excel.application.workbooks(ls_FileName).worksheets(1).Usedrange.columns
.count
ll_rows =
ole_excel.application.workbooks(ls_FileName).worksheets(1).Usedrange.rows.co
unt

// Copy the used range of data to the clipboard
ls_range = "A1:" + ls_lastcol + ":" + string(ll_rows)

ole_excel.application.workbooks(ls_filename).worksheets(1).Range(ls_range).C
opy()

ole_excel.application.Workbooks(ls_fileName).close
END IF
ole_excel.DisconnectObject()
IF IsValid(ole_excel) THEN DESTROY ole_excel

// copy sheet from clipboard back to string
ls_data = clipboard()

// Clear clipboard
clipboard("")

// Parse the data to arrays, datastore, etc....
....

Greets,
Pascal

"Bede" <bede_nosp@m_sofico.be> wrote in message

news:MPG.1ed2567f3...@forums.sybase.com...

[Bede]

thanks, i'm sure this will be faster, as long as windows doesn't create
a problem when selecting a big volume (e.g.15000 rows).
spose it would show immediately ;-)

In article <446840f4$1@forums-2-dub>, ma...@mail.mail says...

[PascalB (Search4)]

I tested this script against a file with 25000 rows / 3 columns ( 2MB file /
PB 10.2.1 / W2K ).

It only takes a few seconds for the ole-part instead of minutes...
The processing by Powerbuilder also takes a while, but it does not consume
memory as ole does.
(Just out of curiosity... look at the task-manager and compare the old
solution with the new one.)

So, this is a win-win operation ;-)

Greets,
Pascal

"Bede" <bede_nosp@m_sofico.be> wrote in message

news:MPG.1ed2666b4...@forums.sybase.com...

[Bede]

indeed ;-) here are the results of the flemish jury:

Total volume = 9406
Total usable rows= 17

OLE - Import ended after 116828 ms.
ClipBoard - Import ended after 2343 ms.

Say no more!! Thanks!

In article <44684da0$1@forums-2-dub>, ma...@mail.mail says...

[allo...@gmail.com]

Hi PascalB, can i get to contact you regarding this question?

My email is allo...@gmail.com

[bruce.arm...@gmail.com]

These google groups were originally feeds from the Sybase NNTP server. That server was shut down on March 28, 2013. Questions are now handled in the SAP Community Network PowerBuilder Developer Center:

http://scn.sap.com/community/developer-center/powerbuilder/content?filterID=contentstatus%5Bpublished%5D~objecttype~objecttype%5Bthread%5D