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

dragging a base text table into calc sheet within a macro

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Chris Snow
General User
General User


Joined: 28 Sep 2006
Posts: 15
Location: Staffordshire, UK

PostPosted: Thu Sep 28, 2006 1:02 pm    Post subject: dragging a base text table into calc sheet within a macro Reply with quote

Hi, hope someone can help with this. I'm a convert from MS Office so not too familiar with OOo calc.

I've created a text database in base with two tables (csv files), I'm wanting to automate the importing of those data into calc sheets.

The way I've tried to go about this is to ceate a macro which opens the data sources and then drags the table into the sheet (cell a1). This process works fine but when I run the resulting macro nothing happens.

Am I missing a step? Any help much appreciated!

Here is the macro if it helps:

REM ***** BASIC *****

Sub Main

End Sub


sub drag_in_data_source
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ViewDataSourceBrowser"
args1(0).Value = true

dispatcher.executeDispatch(document, ".uno:ViewDataSourceBrowser", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ViewDataSourceBrowser"
args2(0).Value = true

dispatcher.executeDispatch(document, ".uno:ViewDataSourceBrowser", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ViewDataSourceBrowser"
args3(0).Value = false

dispatcher.executeDispatch(document, ".uno:ViewDataSourceBrowser", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ViewDataSourceBrowser"
args4(0).Value = false

dispatcher.executeDispatch(document, ".uno:ViewDataSourceBrowser", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())


end sub
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Sep 28, 2006 1:26 pm    Post subject: Reply with quote

The macro recorder does not use the API. It calls a dispatcher similar to "doCommand(slot)" in old MSAccess.
Are you aware that Calc creates a named database-range when you drag a datasource-object into a sheet? This range is refreshable, so you can call Menu:Data>Refresh while the range (or a single cell within) is selected.
Another trick:
Have a look at the properties of the named database range (Data>Define...).
Option "Insert/Remove Cells" makes the import range resize on refresh and adjusts adjacent formula cells.
Option "Keep Formatting" keeps the formatting of the spreadsheet cells.
You may save a template with import ranges rather than writing macros.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Chris Snow
General User
General User


Joined: 28 Sep 2006
Posts: 15
Location: Staffordshire, UK

PostPosted: Thu Sep 28, 2006 1:58 pm    Post subject: Reply with quote

Thanks for the help.

I like the refresh range functionality. Also I like that I can add "Refresh Range" button to the toolbar and assign a function key.

Perhaps there is some way to make it refresh on opening the document also?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Sep 28, 2006 2:10 pm    Post subject: Reply with quote

Chris Snow wrote:
Thanks for the help.

I like the refresh range functionality. Also I like that I can add "Refresh Range" button to the toolbar and assign a function key.

Perhaps there is some way to make it refresh on opening the document also?

Tools>Options>Calc>General>Updating ...
Not shure if this applies to database ranges.

If not:
Code:
Sub refreshDBRanges()
arrNames() = Array("Import1","Import2","Import3")
For i = 0 to uBound(arrNames())
  thisComponent.DatabaseRanges.getByName(arrNames(i)).refresh
Next
End Sub

Tools>Customize>Events
Save in: <yourDoc.ods>
Event: Open Document
Point to <yourDoc.ods>/Library/Module/refreshDBRanges
There are two other related forums on this site and a search-form.
API and Macros
Code-Snippets (working code only)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org


Last edited by Villeroy on Sun May 09, 2010 11:25 am; edited 1 time in total
Back to top
View user's profile Send private message
Chris Snow
General User
General User


Joined: 28 Sep 2006
Posts: 15
Location: Staffordshire, UK

PostPosted: Fri Sep 29, 2006 10:31 am    Post subject: Reply with quote

Thanks again for your help.

I think I tried Tools>Options>Calc>General>Updating and found that it did not work for me in this context.

I will try to make sense of the remainder of your last after "If not:" and how to implement it because, as I wrote in my original submission, I'm not too familiar with OOo calc!
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Sep 29, 2006 11:22 am    Post subject: Reply with quote

Chris Snow wrote:
Thanks again for your help.

I think I tried Tools>Options>Calc>General>Updating and found that it did not work for me in this context.

I will try to make sense of the remainder of your last after "If not:" and how to implement it because, as I wrote in my original submission, I'm not too familiar with OOo calc!

Tools>Macros>Organize
Browse to your document > library "Standard"
If "Standard" has no Module: Button "New" will create one with default name "Module1".
Paste the code (Sub ... End Sub) into the Module.
Edit the array of database-ranges names, so it reflects all ranges to be refreshed at once:
arrNames() = Array("Import1","Import2","Import3")
Finally set up event "Document Open" as described above.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Chris Snow
General User
General User


Joined: 28 Sep 2006
Posts: 15
Location: Staffordshire, UK

PostPosted: Fri Sep 29, 2006 11:43 pm    Post subject: Reply with quote

Thank you!
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 Calc 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