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

Inserting a row into a calc spreadsheet using visual basic

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
mgrubb
Newbie
Newbie


Joined: 01 Dec 2006
Posts: 2

PostPosted: Fri Dec 01, 2006 11:11 am    Post subject: Inserting a row into a calc spreadsheet using visual basic Reply with quote

I am trying to update an application that we use that currently ftps a comma delimited flat file from a legacy system and opens it in Excel and then inserts a row at the top of the spreadsheet and then populates that row with heading.

I am updating the application to enable users to use Open Office Calc instead of Excel, but am having difficulty figuring out how to add a row to the sheet and update the cells when the user chooses to use Calc.

I'm not having a problem opening the document, that is working great. I just need to be able to add a row at the top of the spreadsheet and then populate the cells with headers. I've attached my code, can anyone help or point me in the right direction? I've reviewed the developers guide, but most of the info is for java and unfortunately I must do this in VB. Thanks in advance.

Dim oSM 'Root object for accessing OpenOffice from VB
Dim oDesk, oDds, oSheet, oDispatch As Object 'First objects from the API oSheet
Dim arg() 'Ignore it for the moment !
Dim todaysDate
Dim oOpenArgs(1)

todaysDate = Date

todaysDate = DatePart("yyyy", todaysDate) & DatePart("m", todaysDate) & DatePart("d", todaysDate) & DatePart("h", todaysDate) & DatePart("n", todaysDate)

'Instanciate OOo : this line is mandatory with VB for OOo API
Set oSM = CreateObject("com.sun.star.ServiceManager")
'Create the first and most important service
Set oDesk = oSM.CreateInstance("com.sun.star.frame.Desktop")
'Set oDispatch = CreateObject("com.sun.star.frame.DispatchHelper")

'Dim oOpenArgs(0)
Set oOpenArgs(0) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
Set oOpenArgs(1) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oOpenArgs(0).Name = "FilterName"
oOpenArgs(0).value = "Text - txt - csv (StarCalc)"
oOpenArgs(1).Name = "FilterOptions"
oOpenArgs(1).value = "44,34,0,1"


Set oDds = oDesk.loadComponentFromURL("file:///" & localFileName, "_blank", 0, oOpenArgs)
Set oSheet = oDds.GetSheets().getByIndex(0)
'success = oDispatch.executeDispatch(oSheet, ".uno:InsertRows", "", 0, Array())
Back to top
View user's profile Send private message
Trex78
General User
General User


Joined: 30 Aug 2006
Posts: 24
Location: France

PostPosted: Sun Dec 03, 2006 12:35 am    Post subject: Reply with quote

Hello,

You can use the "insertByIndex" function. To do this, you should :
Get the last used line :
Code:
' From Andrew's macro information 6.23
Dim oCell As Object
Dim oCursor As Object
Dim aAddress As Variant
   oCell = oSheet.GetCellbyPosition( 0, 0 )
   oCursor = oSheet.createCursorByRange(oCell)
   oCursor.GotoEndOfUsedArea(True)
   aAddress = oCursor.RangeAddress

Create a CellRange covering the whole sheet :
Code:
oCellRange = oSheet.getCellRangeByPosition(0,0, aAddress.Column,aAddress.Row)

Insert 1 blank line using "insertByIndex" to ".Rows" of this range :
Code:
oRows = oCellRange.Rows
oRows.insertByIndex(0,1)

Then, you fill this line with your titles.

Remarks :
- I extracted these lines from my own working code, so it might not work "as is"
-Checking insertByIndex syntax, I found an easier method which should be checked : http://christianwtd.free.fr/index.php?rubrique=LBasLoto10
It's written in french, so I can have a deeper look if you wish. Basically, it seems that you can use ".Rows" of the (0,0) cell to apply insertByIndex, without having to define a CellRange.
- But, adding the titles in front of data when creating the csv file might be a better solution : you ensure titles are consistent with data, in case of a change, and if users can choose between OO and another spreadsheet, everybody has the same titles.

Hope this helps.
Back to top
View user's profile Send private message
mgrubb
Newbie
Newbie


Joined: 01 Dec 2006
Posts: 2

PostPosted: Mon Dec 04, 2006 10:58 am    Post subject: Reply with quote

Trex78,

Thanks for your reply, it was what I needed. I also reviewed the document from the link you sent and between the 2 I was able to complete the task quickly.

And I agree that it would be better if the ERP vendor would make a change so the column headings were output with the data, but apparently they either don't know how or aren't interested in doing so. So instead, in the UI to the utility, the user is prompted to pick the report type and the headings are added based on their selection. Plus, it allows me to change the properties of cells, etc.

Again, thanks for the help, it is really appreciated!
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 Macros and API 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