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


 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
  Exit Sub
  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
  Exit Sub
  If NewlinePosition>TabPosition Then
  OutString(i)=RemoveDots(Mid(ClipText, TabPosition+1,
 Next i
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:

 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 =
 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)), _
 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, ".")
 Test=InStr(InText, ",")
 If Test<>0 Then
  RemoveDots=Left(InText, Test-1)+"."+Right(InText, Len(InText)-Test)
End Function

Sub EnterRow(Data As String)
 Dim Sheet As Object
 Dim Row As Integer, Col As Integer


' Get the current first empty row; it's located at Data.B1

' Enter today's date

' Enter all the values
 For Col=1 To 8
 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.



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

Reply via email to