I finally got my macro up and running. All I have to do now, is to log in to my bank, highlight the summary (the whole thing), open my spreadsheet and then click a big button on top of the spreadsheet.
The macro will then read from the ClipBoard (thanks to Mr. Pitonyak who answered to a similar question on the forum that I linked to last time, see below), find the values of the last column, convert it to useable numbers (remove thousands delimiter dots and convert decimal comma to a decimal point), convert the resulting strings to numbers and then put the values at their right places. Here are the subroutines and functions alltogether: Sub ExtractInfoFromClipBoardToNewRow Dim ClipText As String Dim OutString(7) As String Dim i As Integer, j As Integer Dim TabPosition As Integer, NewlinePosition As Integer ClipText=ConvertClipToText TabPosition=1 NewlinePosition=1 For i=0 To 7 For j=1 To 4 If TabPosition=0 Then Print "The contents of the clipboard doesn't seem to be what we thought it would" Exit Sub EndIf TabPosition=InStr(TabPosition+1, ClipText, Chr(9)) Next j NewlinePosition=Instr(NewlinePosition+1, ClipText, Chr(10)) If NewlinePosition=0 Then If i<7 Then Print "The contents of the clipboard doesn't seem to be what we thought it would" Exit Sub Else NewlinePosition=Len(ClipText+1) EndIf EndIf If NewlinePosition>TabPosition Then OutString(i)=RemoveDots(Mid(ClipText, TabPosition+1, NewlinePosition-TabPosition)) EndIf Next i EnterRow(OutString) End Sub Function ConvertClipToText As String ' This is a function I found and modified a bit for my needs. ' The original function can be found here: ' http://www.oooforum.org/forum/viewtopic.phtml?t=10060&start=0&postdays=0&postorder=asc&highlight=systemclipboard Dim oClip As Object, oClipContents As Object, oTypes As Object Dim oConverter, convertedString As String Dim i As Integer, iPlainLoc As Integer iPlainLoc = -1 oClip = createUnoService("com.sun.star.datatransfer.clipboard.SystemClipboard") oConverter = createUnoService("com.sun.star.script.Converter") oClipContents = oClip.getContents() oTypes = oClipContents.getTransferDataFlavors() Dim msg As String msg = "" For i=LBound(oTypes) To UBound(oTypes) If oTypes(i).MimeType = "text/plain;charset=utf-16" Then iPlainLoc = i Exit For End If Next i If (iPlainLoc >= 0) Then convertedString = oConverter.convertToSimpleType(oClipContents._ getTransferData(oTypes(iPlainLoc)), _ com.sun.star.uno.TypeClass.STRING) ConvertClipToText=convertedString End If End Function Function RemoveDots(InText As String) As String Test=InStr(InText, ".") While Test<>0 InText=Left(InText, Test-1)+_ Right(InText, Len(InText)-Test) Test=InStr(InText, ".") Wend Test=InStr(InText, ",") If Test<>0 Then RemoveDots=Left(InText, Test-1)+"."+Right(InText, Len(InText)-Test) Else RemoveDots=InText EndIf End Function Sub EnterRow(Data As String) Dim Sheet As Object Dim Row As Integer, Col As Integer Sheet=ThisComponent.Sheets.getByName("Data") ' Get the current first empty row; it's located at Data.B1 Row=Sheet.getCellByPosition(1,0).getValue() ' Enter today's date Sheet.getCellByPosition(0,Row).setValue(Now()) ' Enter all the values For Col=1 To 8 Sheet.getCellByPosition(Col,Row).setValue(Val(Data(Col-1))) Next Col End Sub To be able to use this, you probably need the whole spreadsheet, because there are some formulas in some cells that matters more or less. I guess it doesn't matter much, since all I wanted to do was to tell you that I made it. It works now and I am very happy with it. ☺ J.R. 2009/1/1 Johnny Rosenberg <gurus.knu...@gmail.com> > > Oops… sorry, I think I found the answer by just googling for it. Haven't > tested yet, but the answer might be found > here<http://www.oooforum.org/forum/viewtopic.phtml?t=10060&start=0&postdays=0&postorder=asc&highlight=systemclipboard> > . > > Will try it out later. Thanks for reading anyway. > > J.R. > 2009/1/1 Johnny Rosenberg <gurus.knu...@gmail.com> > >> I want to do the following: >> >> With my web browser I go to my bank's web page and log in to my account. >> Then a summary of all my accounts shows up. I select all of its text and >> press Ctrl+c. >> >> Then I switch to OpenOffice.org and I want my macro to do things with >> what's in the clipboard. >> >> As for now I solved the problem in an ugly way, by just pressing Ctrl+v to >> paste it in some random cells, then select the cells and let the macro work >> with what it gets from there and then finally erase those cells. >> >> What I would like to do, is to get the information directly from the clip >> board. Is that possible? >> >> The data from my bank's web site seems to be plain text with \t (TAB >> character) to separate the columns from each other. >> >> I don't know if it's relevant, but I guess it won't hurt to tell you what >> I want to do with the information from the clipboard: >> >> I only want the last column, which is the sums of my accounts. Since my >> bank use a character for a thousands delimiter, a dot actually, >> OpenOffice.org thinks I am dealing with text if I paste it to a cell. >> >> Since I'm Swedish, a number with two decimals can look like this: # ### >> ##0,00 >> In the clipboard, it looks like this: #.###.##0,00 (some use dot between >> each three numbers, but spaces are also common and looks nicer if you ask >> me) >> In OpenOffice.org BASIC, I need to use this format, to make it work >> properly: ######0.00 >> So my macro will simply remove all dots, which makes it look like this: >> ######0,00. Then it will replace the comma with a dot, like this: >> ######0.00. Setting the value of a cell to something in that format, like >> 1234.57, will make the cell to be numerical. If I look in the cell directly >> on the spreadsheet, it will look like 1234,57, since my default language is >> set to Swedish, but obviously OpenOffice.org BASIC doesn't care about that. >> >> Well, there you have some unnecessary information, but perhaps it explains >> a bit things like what and why… >> >> So, to sum it up, I need to read from the clipboard, perhaps row by row, >> determining the last column of each row, convert it to a format that makes >> more sense and then paste the converted values to some cells in the >> spreadsheet. The macro I've already written already deals with the >> formatting and it works fine, but it reads the information from other cells >> rather than from the clipboard, and I want to obtain the information >> DIRECTLY from the clipboard. >> >> I hope this is not too confusing… >> >> What I need to know is just how to get what's currently in the clipboard… >> >> Best regards >> >> Johnny Rosenberg >> > >
