OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Programatically selecting a subset of a column of data

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
SRD
General User
General User


Joined: 06 Mar 2007
Posts: 10

PostPosted: Tue Mar 06, 2007 7:37 pm    Post subject: Programatically selecting a subset of a column of data Reply with quote

Hi,

I have a column of product codes. I wish to select a subset of these, based on a smaller set of codes elsewhere, either in a text file or in another column.

Is a macro the best way to do this?

Thanks,

SRD
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Tue Mar 06, 2007 10:40 pm    Post subject: Reply with quote

By "select" you mean "select the cells containing the data" or "display the data in another part of the spreadsheet" or something else? A tangible example would help.
_________________
search forum by month
Back to top
View user's profile Send private message
SRD
General User
General User


Joined: 06 Mar 2007
Posts: 10

PostPosted: Tue Mar 06, 2007 10:49 pm    Post subject: Reply with quote

Sorry, I want to select the cells and copy and paste the cell contents into another column.

The column contains product codes for a marine and boating shop. Only some of the products have a jpeg image at the moment. The problem is that there is no pattern to what codes have an image available, so the task of manually picking out each code that does have a jpeg image, and pasting that code into the jpeg column (the jpeg will be [product code].jpg) is tedious.

However, I do have the list of specific product codes that do have a matching jpeg image in a seperate text file, which I'm wondering could be used somehow in a macro or a procedure to select all cells in the first product code column that match those in the text file or an array of some kind. Once these cells are selected, they can then be copied and pasted into the jpeg column.

The list is long, hence the preference for an automated solution.
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Tue Mar 06, 2007 11:14 pm    Post subject: Reply with quote

If you can get the list from the text file into the spreadsheet in regular order, your task will be a lot simpler. By regular order I mean a column of names and a column of corresponding codes.

Once the data is in the spreadsheet, you can use it as a lookup range. The VLOOKUP function would be adequate.

You may have to use a medium like a Writer table to get the data into regular order. That's the main hurdle but it's a lot easier, I suspect, than trying to devise script to read and compare a text file and a spreadsheet range - unless someone has already done that.
_________________
search forum by month
Back to top
View user's profile Send private message
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Tue Mar 06, 2007 11:55 pm    Post subject: Reply with quote

Hi

I'm a great believer of only doing a job once. The following macro checks for the existence of the jpg for you and then enters the location. It asumes that you have you product codes in Sheet 1 column A, and post the results into column B:
Code:

sub check_images
Dim r as Integer
Dim img_dir as String
Dim jpg as String

img_dir = "/home/bainm/test/images/"
r=0

while thiscomponent.Sheets(0).getCellByPosition(0,r).String<>""
  jpg = img_dir & thiscomponent.Sheets(0).getCellByPosition(0,r).String & ".jpg"
  if (Dir(jpg) <> "") then
    thiscomponent.Sheets(0).getCellByPosition(1,r).String = jpg
  end if
  r=r+1
wend
end sub

Obviously you'll need to change the img_dir variable to match your own set up.

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
SRD
General User
General User


Joined: 06 Mar 2007
Posts: 10

PostPosted: Wed Mar 07, 2007 4:04 am    Post subject: Reply with quote

Hi Mark,

That is brilliant, thanks!

A question: can the img directory be in windows format. ie. img_dir = "E:\WebApplications\site\images\" ?

This is the only variable I've changed and the macro's not worked as yet.

I'm on WinXP.

Thank you,

Steve
Back to top
View user's profile Send private message
SRD
General User
General User


Joined: 06 Mar 2007
Posts: 10

PostPosted: Wed Mar 07, 2007 7:59 pm    Post subject: Reply with quote

I've also tried the following format:
"file:///e|/WebApplications/site/images/"

and, a relative path
"/images/"

but they're not working.

What is the standard path syntax in OO macros?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Mar 08, 2007 8:19 am    Post subject: Reply with quote

Try:
Code:

[...]
  jpg = ConvertToURL(img_dir & thiscomponent.Sheets(0).getCellByPosition(0,r).String & ".jpg")
[...]

A cell-function is more flexible. It can test any given set of file(s) by dragging down a formula:
Code:

REM should work with URLs and system paths
Function EXISTS(sFile$) As Boolean
 EXISTS = FileExists(sFile)
End Function

C1 =EXISTS(B1)
where B1 may be something like =FileURLPath&A1 and A1 has the requested file name "pic.png".

Named references, calculating the URL and the Path of this document:
cFilename =CELL("FILENAME")
FileURL =MID(cFilename;2;SEARCH("'#";cFilename)-2)
FileURLPath =MID(cFilename;2;SEARCH("/[^/]+'#";cFilename)-1)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
SRD
General User
General User


Joined: 06 Mar 2007
Posts: 10

PostPosted: Thu Mar 08, 2007 7:47 pm    Post subject: Reply with quote

Thanks Villeroy

The ConvertToURL function fixed it.

I will delve into the latter code when I need to.

Thank you (all).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group