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

Who can I pay to convert one macro from excel to calc?

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


Joined: 15 Apr 2006
Posts: 3

PostPosted: Sat Apr 15, 2006 2:47 pm    Post subject: Who can I pay to convert one macro from excel to calc? Reply with quote

The code is not crazy, in fact, it is probably pretty crappy code, as I did it myself, I am no programmer, and basically suck at excel. But, there is more of a help audience for the excel market, and through lots of hand holding, I was able to accomplish my task.

Basically, the code renames photos from an excel or calc list, from a defined area of a document the user chooses. It has pre determined photo names, and then it renames the original photos to like R394434.jpeg All automatically, and requires no through whatsoever and the end users part. and if there are multiple pictures based on like 1-5, then it adds a letter to the end to clarify it. I would like to change this a little, but getting it to work as is is the big thing.

How hard would this be to convert... and how much is it going to cost me? Or if nothing else, something from scratch in calc that is cleaner, and again, still does the same thing. Budget is not big, and I cannot find any people specifically saying they would do this. Enterprise staroffice is not an option. We are moving to openoffice specifically because it is free, and adding 15 users onto office, again, is not in the budget.

Here are some snippets of code:


Code:
Rem     On Error Resume Next
Rem     
Rem     For I = 1 To Cells(Rows.Count, "E").End(xlUp).Row
Rem         nPos = InStr(Cells(I, "G").Value, "-") - 1
Rem         If nPos > 0 Then
Rem             nStart = Left(Cells(I, "G").Value, nPos)
Rem             nEnd = Right(Cells(I, "G").Value, Len(Cells(I, "G").Value) - nPos - 1)
Rem         ElseIf IsNumeric(Cells(I, "G").Value) Then
Rem             nStart = Cells(I, "G").Value
Rem             nEnd = Cells(I, "G").Value
Rem         Else
Rem             MsgBox "Invalid Photo Value"



Code:
Rem Sub Renaming()
Rem Dim OldName As String
Rem Dim NewName As String
Rem Dim LastRow As Long
Rem Dim I As Long
Rem On Error Resume Next
Rem 
Rem LastRow = 200
Rem 
Rem     For I = 1 To LastRow
Rem         OldName = Range("A" & I).Value
Rem         NewName = Range("B" & I).Value
Rem         Name OldName As NewName
Rem     Next I
Rem     
Rem End Sub
Rem Sub opendirectory()
Rem     Range("K6").Select
Rem     ActiveCell.FormulaR1C1 = ActiveWorkbook.Path
Rem End Sub



I need this part to run, automatically when the program is opened, and call the other subs

Code:
Rem ChDir ThisWorkbook.Path
Rem strfile = Application.GetOpenFilename
Rem 
Rem Set wbopen = Workbooks.Open(strfile)
Rem wbopen.Sheets("PictureSheet").Range("A6:B105").Copy Workbooks("thissheet.xls").Sheets("renaming_program").Range("E1")
Rem wbopen.Close


That's like 75% of the code, I didn't paste all of the first part, it's long, and boring and just if then statements saying a1 will equal b1, loop for all etc etc

I can provide actual documents if it should be rebuilt and tested, and there won't be any non-disclosures signed, nothing too proprietary going on here.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Apr 15, 2006 4:01 pm    Post subject: Re: Who can I pay to convert one macro from excel to calc? Reply with quote

Not saying you shouldn't do it, but I use this freeware constantly when dealing with photos as jpegs:

http://www.bulkrenameutility.co.uk/Main_Intro.php

Also, and incidentally, this great viewer/utility will bulk rename/convert,...

http://www.faststone.org/FSViewerDetail.htm

... but I haven't tried that aspect, using it primarily for family to select photos for putting onto CD [once a folder is selected, a simple look, then copy by pressing C transfers the photo to a collection for burning.

David.
Back to top
View user's profile Send private message
ravdav
Newbie
Newbie


Joined: 15 Apr 2006
Posts: 3

PostPosted: Sun Apr 16, 2006 7:30 am    Post subject: Reply with quote

I appreciate the response. I am into freeware too (ooo hehe)

But... the list containing the specific file names is in an excel document that already has to be made. Running as a macro, the additional in document formulas, everything is idiot proof. A secondary program, even with excel import capabilities will not fully achieve what I need to.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Apr 16, 2006 8:20 am    Post subject: Reply with quote

As far as I can see so far, this VBA code is pointless, because you can implement the same thing with links and formulas.
If "pictureSheet" contains pictures you may create a dynamic hyperlink. Click that hyperlink, press Ctrl-C, activate the target and press Ctrl+V.
Copied from http://www.oooforum.org/forum/viewtopic.phtml?t=26620&highlight=hyperlink
Named reference "Path"
refersto: MID(CELL("FILENAME";A1);2;SEARCH("/[^/]+'#";CELL("FILENAME";A1))-1)

=HYPERLINK(Path&"SubDir/Data.ods#Sheet1.$A$1:$F$1000")
creates a hyperlink to sheet1.A1:F1000 in <this doc's path>SubDir/Data.ods
Back to top
View user's profile Send private message
ravdav
Newbie
Newbie


Joined: 15 Apr 2006
Posts: 3

PostPosted: Sun Apr 16, 2006 1:34 pm    Post subject: Reply with quote

The picturesheet contains data like this:

a b c d
1 B25193 1-3
1 B23743 4-6
1 B26139 7-8
1 B49283 9-14
1 B36092 15-22



So, the macro, has additional information on the sheet. It has the default picture names (DSCN0001.jpeg) So, this person has 22 pictures in the example above, right now named DSCN0001.jpeg through DSCN0022.jpeg. The photos need to be renamed to:

DSCN0001.jpeg B25193.jpeg
DSCN0002.jpeg B25193_a.jpeg
DSCN0003.jpeg B25193_b.jpeg
DSCN0004.jpeg B23743.jpeg


etc

etc

etc

I looked through your hyperlink file, and I cannot see how it would accomplish what I need. There are TWO seperate sheets, one that contains the example data from above, and then the one with the macros that will import the data, then rename the file names from the originals to the new ones with the extensions of necessary.

Is there just not a market for people to convert macros? It seems everyone either knows vba, or ooobasic.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Apr 17, 2006 6:30 am    Post subject: Reply with quote

David just tried to give a hint that you may use some apropriate tool for picture/file management. What I tried to explain is: If you really want to do this conversion of file-names by spreadsheet then your VBA-stuff is like using a crook, while having roller skates under your feet.
I took some minutes and made a quick draft without any macros. Notice the message I'll send you.
It works like this:
The document contains some linked dummy data.
Simply pick one of your files from Edit>Links, button Change....
My dummy-data will be replaced with your data from PictureSheet.A6:B105 from your selected file. That's all you have to do.

First sheet provides a calculated hyperlink to the current source-file.
Second sheet shows transformed file names.
It's just a quick draft, but most of it should work with *any* spreadsheet-program able to read the file.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Apr 17, 2006 12:40 pm    Post subject: Reply with quote

[Sent a nasic-free draft to OP]
Now the macro-part: Why not writing an API-independant cell-function? This works just like VBA (ok, not that many builtin functions).
Function MYCONVERT(CellVal)
sVal = cStr(CellVal)
iPos = instr(sVal;"-")
[...]
MYCONVERT = sResult
End Function
Formula B2 =MYCONVERT($A2)
Notice that you just get a value of type String or Long passed from the referred cell A2.
Refer to the online-help on starbasic. Because you get the string to be modified from the referred spreadsheet-cell, you don't have to deal with API-stuff. StarBasic is very much like VBA without class-modules (you can use real object-oriented languages like C++, Python and Java). The huge difference is the API you talk to.
I would save the function in library "Standard" of a template. "Standard" is loaded automatically and the function keeps on working when you use the file elsewhere. Excel however refuses to use that, even if the formula is VBA-compatible. My XL97 crashes when you try to edit a starbasic-module. It may work with some newer version, but MS certainly has no interest to make this happen.
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