[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
ravdav
Newbie

Joined: 15 Apr 2006
Posts: 3

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

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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Sat Apr 15, 2006 4:01 pm    Post subject: Re: Who can I pay to convert one macro from excel to calc? 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.
ravdav
Newbie

Joined: 15 Apr 2006
Posts: 3

 Posted: Sun Apr 16, 2006 7:30 am    Post subject: 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sun Apr 16, 2006 8:20 am    Post subject: 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 SubDir/Data.ods
ravdav
Newbie

Joined: 15 Apr 2006
Posts: 3

 Posted: Sun Apr 16, 2006 1:34 pm    Post subject: 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Mon Apr 17, 2006 12:40 pm    Post subject: [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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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