Ask Your Question

evileyeball's profile - activity

2019-08-13 21:41:10 +0100 received badge  Famous Question (source)
2019-08-13 21:41:10 +0100 received badge  Notable Question (source)
2018-09-19 17:03:04 +0100 received badge  Famous Question (source)
2018-05-16 11:08:38 +0100 received badge  Taxonomist
2017-10-19 00:53:40 +0100 received badge  Famous Question (source)
2017-05-18 17:01:03 +0100 received badge  Notable Question (source)
2017-05-18 17:01:03 +0100 received badge  Popular Question (source)
2017-02-02 13:06:27 +0100 received badge  Notable Question (source)
2017-02-02 13:06:27 +0100 received badge  Popular Question (source)
2016-12-02 21:06:19 +0100 received badge  Popular Question (source)
2016-11-29 10:17:41 +0100 asked a question Sort Calc Data on Multiple columns each with custom order?

Hey all, I have a calc file where I have implimented several custom sort orders What I want to do is

Sort by Column C with Custom Order A (Column is Text) Then by Column F with Non Custom Order (Column is Numbers) Then by Column E with Custom Order B (Column is Text)

From google it looks like Excel can achieve this because Excel allows you to select custom order for each column you sort by. Libre office Doesn't seem to support this

Hence My question is Is there any way to achieve this?

2016-11-27 07:29:51 +0100 received badge  Enthusiast
2016-10-15 16:53:46 +0100 answered a question Result of macro not displaying in Cell but does display in Function Wizard

I figured out what the problem was. What I thought was "2.30" was really "chr(10)2.30chr(10)" stripping off the first and last character of the string which were newline characters made it display properly.

I've changed the code now so it returns double values and the -1.0 values are getting changed to be different so I can tell where it errored (as a card's price should never be negative negative values make for a good error code)

2016-10-15 13:57:50 +0100 asked a question Result of macro not displaying in Cell but does display in Function Wizard

Hello Libre Office.

I'm having a bit of a fun time here. This is my first foray into Macros in Libreoffice I found a page online with a macro someone had built to find House words data of of a wiki page about a song of Ice and Fire and have modified the code they had for my attempt at fetching Magic The Gathering Prices

I have the following function

Public Function FETCHPRICE(sSet as String, sCard as String) as string


   sURL = "http://mtggoldfish.com/index/" & sSet & "#paper"
   dim sWords as String
   oSimpleFileAccess = createUNOService ("com.sun.star.ucb.SimpleFileAccess")
   oInpDataStream = createUNOService ("com.sun.star.io.TextInputStream")
   on error goto NoCardFound
   oInpDataStream.setInputStream(oSimpleFileAccess.openFileRead(sUrl))
   on error goto NoCardFound
   dim delimiters() as long
   sContent = oInpDataStream.readString(delimiters(), false)

   lStartPos = instr(1, sContent, "" )
   if lStartPos = 0 then
     Msgbox("Table Not found")
     FETCHPRICE = "-1.0"
     exit function
   end if   
   lEndPos = instr(lStartPos, sContent, "
") sTable = mid(sContent, lStartPos, lEndPos-lStartPos + 8) lStartPos = instr(1, sTable, sCard ) if lStartPos = 0 then Msgbox("Card " & sCard & " Not Found for Set " & sSet) FETCHPRICE = "-1.0" exit function end if lEndPos = instr(lStartPos, sTable, "") sRow = mid(sTable, lStartPos, lEndPos-lStartPos + 5) oTextSearch = CreateUnoService("com.sun.star.util.TextSearch") oOptions = CreateUnoStruct("com.sun.star.util.SearchOptions") oOptions.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP oOptions.searchString = "" oTextSearch.setOptions(oOptions) oFound = oTextSearch.searchForward(sRow, 0, Len(sRow)) If oFound.subRegExpressions = 0 then FETCHPRICE = "-1.0" exit function end if lStartPos = oFound.endOffset(0) + 1 lEndPos = instr(lStartPos, sRow, "") sPrice = mid(sRow, lStartPos, lEndPos-lStartPos) FETCHPRICE = sPrice exit function NoCardFound: Msgbox "Error Finding Card" FETCHPRICE = "-1.0" End Function

When I run it with Known values Like =FETCHPRICE("RAV","Circu, Dimir Lobotomist") it either Errors (which I assume is a result of me hitting thier page too frequently and in future revisions I can correct for) or it Gives me back a BLANK CELL I've inserted Breakpoints and put a watch on the sPrice Variable which is indeed a string variable with the correct price value in it when it is returns

Now the strange part is

When I open the Fuction wizard and enter the function it shows in the result window the correct resulting price. Click OK in there to make the formula insert into the cell and it Either gives me "Error Finding Card" or gives me a Blank cell

I tried several things to remedy this such as Returning "" & sPrice

Changing the function so it returns Double (and changing the "-1.0" returns to -1.0 But that didn't work because when I tried to cDbl(sPrice) it didn't like it It said Invalid Procedure call I saw that Cdbl to go from String to Double needs to be cDbl("12345") format so I tried making it cdbl("""" & sPrice & """")

to concatinate some "'s around it But no dice

I tried setting sPrice to "Price is: " & sPrice on the line before I returned it but no dice I ... (more)

2016-10-14 09:43:45 +0100 received badge  Editor (source)
2016-10-14 09:20:39 +0100 asked a question Replicate ImportHtml function of google sheets in calc.

Hello all. I have an issue i'm struggling with. I have a spreadsheet with a list of Magic The Gatherimg cards What I want is to scrape live price data at some interval from the web. I accomplished this easily be in Google sheets (see test file ) https://docs.google.com/spreadsheets/...

But my actual sheet uses other things that make using google sheets to house it impractical

I tried my damnedest to get web service and filterxml to pull me the data I want but had no luck Maybe one of you gurus knows how to pull this off. In the case that webservice and filter xml can't do this I'm not apposed to learning macros to do this

I haven't ever dealt with macros in libre but if that's what it takes I'm down to learn