Clicky

I have an Excel spreadsheet that has one macro it is:

Sub Button1_Click()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
   ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

When I try to use the spreadsheet on my Linux box using OpenOffice the macro won't run because the macro is in VB. Is there a way to rewrite this macro to work with my linix computers?

Thanks,
Tony

asked 01/18/2011 02:48

tonkyman's gravatar image

tonkyman ♦♦


8 Answers:
Ok, I have to admit that I know nothing about programing or writing macros. I was hoping someone could tell me what needs to change.
link
tonkyman's gravatar image

tonkyman

okay try this


Sub Button1_Click()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ThisComponent.CurrentController.ActiveSheet.PivotTables.Count
   ThisComponent.CurrentController.ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

link
hollecar's gravatar image

hollecar

hollecar, I did try it but the macro window pops up at the line:

Application.DisplayAlerts = False

And the macro stops. I have a newer version of Open office at home so I'll try it there tonight.

link
tonkyman's gravatar image

tonkyman

I also thing you should check out the link I sent with some very simple steps to use the open office macro GUI...

http://wiki.services.openoffice.org/wiki/Documentation/OOoAuthors_User_Manual/Getting_Started/How_to_run_a_macro

Its very user friendly and designed for novice users...
link
hollecar's gravatar image

hollecar

I did look at that link. To record a macro it appears to me that you need to be able to manually complete the task and I can't seem to find a way to refresh the tables.

I thought this was going to be a simple task since this macro does nothing more than update some totals on one of my sheets.... but I was wrong lol!
link
tonkyman's gravatar image

tonkyman

The objects used in Open Office are very different than the VBA objects used in Excel.  You cannot just open an Excel Macro in OOo.Calc and have it work.  Pivot Tables are called Data Pilot Tables in Calc.  It appears that your code is just used to refresh the Pivot Tables.  Below is some code that will refresh the Data Pilot Tables in OOo Calc

-Bear
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
sub RefreshDP
	Dim objSheetsEnum as object
	Dim objSheet as object
	Dim objDataPilots as object
	Dim objDataPilot as object

	' Turn off Auto Calculation	
	thisComponent.enableAutomaticCalculation(False)
	
	' Get Access to all the Sheets
	objSheetsEnum = thisComponent.Sheets.createEnumeration()
	
	' Loop Through the Sheets
	While objSheetsEnum.hasMoreElements()
		objSheet = objSheetsEnum.nextElement()
		' Get any Data Pilots on this Page
		objDataPilots = objSheet.getDataPilotTables.createEnumeration()
		
		' Loop through all Found DataPilots
		While objDataPilots.hasMoreElements()
			objDataPilot = objDataPilots.nextElement()
			objDataPilot.Refresh
		Wend
	Wend
	
	' Turn Auto Calculation Back ON
	thisComponent.enableAutomaticCalculation(True)
	
	Set objDataPilot = Nothing
	Set objDataPilots = Nothing
	Set objSheet = Nothing
	Set objSheetsEnum = Nothing
End Sub
link
ltlbearand3's gravatar image

ltlbearand3

This worked well.... Thank You!
link
tonkyman's gravatar image

tonkyman

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 01/18/2011 02:48

Seen: 642 times

Last updated: 01/19/2011 02:01