The following code does almost all I want it to: Starts OOoCalc Inserts spreadsheet named test Inserts many values into cells etc. But I cannot get it to format cell A7 in sheet names test - it fails at runtime with error at line 80
All of this is because I cannot create a Locale, which is needed to format First the code, followed by error detail. Code: Imports System Imports System.Collections.Generic Imports System.Windows.Forms Imports unoidl.com.sun.star.lang Imports unoidl.com.sun.star.uno Imports unoidl.com.sun.star.bridge Imports unoidl.com.sun.star.frame Imports unoidl.com.sun.star.container Imports unoidl.com.sun.star.sheet Imports unoidl.com.sun.star.beans Imports unoidl.com.sun.star.table Imports unoidl.com.sun.star.util Imports unoidl.com.sun.star.reflection Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 'Call the bootstrap method to get a new ComponentContext 'object. If OpenOffice isn't already started this will 'start it and then return the ComponentContext. ' Dim FileName As String = "F:\odtfiles est.odt" Dim FileName As String = "C:\testCLIOO.ods" ' oDoc = unoidl.com.sun.star.sheet.XSpreadsheetDocument ' oDoc.getSheets() Dim localContext As unoidl.com.sun.star.uno.XComponentContext = uno.util.Bootstrap.bootstrap() 'Get a new service manager of the MultiServiceFactory type 'we need this to get a desktop object and create new CLI 'objects. Dim multiServiceFactory As unoidl.com.sun.star.lang.XMultiServiceFactory = DirectCast(localContext.getServiceManager(), unoidl.com.sun.star.lang.XMultiServiceFactory) 'Create a new Desktop instance using our service manager 'Notice: We cast our desktop object to XComponent loader 'so that we could load or create new documents. Dim componentLoader As XComponentLoader = DirectCast(multiServiceFactory.createInstance("com.sun.star.frame.Desktop"), XComponentLoader) 'Create a new blank writer document using our component 'loader object. Dim xComponent As XComponent = componentLoader.loadComponentFromURL("private:factory/scalc", "_blank", 0, New unoidl.com.sun.star.beans.PropertyValue(-1) {}) Dim oDoc As unoidl.com.sun.star.sheet.XSpreadsheetDocument oDoc = xComponent Dim oSheets As unoidl.com.sun.star.sheet.XSpreadsheets ' Dim oSheet As unoidl.com.sun.star.sheet.XSpreadsheet Dim oSheet As XSpreadsheet Dim oSheetsIA As XIndexAccess Dim oCell As XCell oSheets = oDoc.getSheets oSheetsIA = oSheets Dim Sheet1 As XSpreadsheet Dim Sheet2 As XSpreadsheet Dim cSeries As unoidl.com.sun.star.sheet.XCellSeries oSheets.insertNewByName("test", 0) oSheet = oSheetsIA.getByIndex(0).Value ' Now using sheet: test oCell = oSheet.getCellByPosition(0, 0) oCell.setFormula("Test") oSheet.getCellByPosition(1, 0).setFormula("Sales") oSheet.getCellByPosition(2, 0).setFormula("Month") oSheet.getCellByPosition(3, 0).setFormula("Year") cSeries = oSheet.getCellRangeByPosition(3, 3, 5, 3) cSeries.fillSeries(unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, unoidl.com.sun.star.sheet.FillMode.LINEAR, unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2.0, 6.0) oCell = oSheet.getCellByPosition(0, 1) oCell.setValue(54321.0) oSheet.getCellByPosition(0, 2).setValue(625.0) oCell = oSheet.getCellByPosition(0, 3) oCell.setFormula("=SUM(A1:A3") oCell = oSheet.getCellByPosition(0, 5) oCell.setFormula("=DATE(2009;8;10)") oCell = oSheet.getCellByPosition(0, 6) ' cell A7 oCell.setFormula("=40035") ' numerical value for 08/10/2009 in A7 ' Dim oFormatter As unoidl.com.sun.star.util.XNumberFormatter Dim oFormatter As XNumberFormatter ' Dim oFormat As unoidl.com.sun.star.util.XNumberFormatsSupplier Dim oFormatSupp As XNumberFormatsSupplier oFormatSupp = oDoc Dim oTypes As XNumberFormatTypes Dim oLocale As unoidl.com.sun.star.lang.Locale oTypes = oFormatSupp.getNumberFormats() Dim nFormat As uno.Any Dim oFormat As NumberFormat Const dateFormat = unoidl.com.sun.star.util.NumberFormat.DATE oLocale.Language = "en" ' this is the line 80 referred to in error detail oLocale.Country = "US" oLocale.Variant = "Traditional_WIN" ' Now using sheet: Sheet3 Sheet1 = oSheetsIA.getByIndex(3).Value ' Sheet1 = oSheets.getByName(SheetName) Sheet1.getCellByPosition(0, 1).setValue(9876) ' Now using sheet: Sheet1 oSheet = oSheetsIA.getByIndex(1).Value oCell = oSheet.getCellByPosition(0, 4) oCell.setValue(2.0) oCell = oSheet.getCellByPosition(1, 4) oCell.setValue(4.0) cSeries = oSheet.getCellRangeByPosition(0, 4, 5, 4) ' cSeries.fillSeries(unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, unoidl.com.sun.star.sheet.FillMode.LINEAR, unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2.0, 6.0) cSeries.fillAuto(unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, 2) oSheet.getCellByPosition(0, 5).setFormula("Monday") oSheet.getCellByPosition(1, 5).setFormula("Tuesday") cSeries = oSheet.getCellRangeByPosition(0, 5, 5, 5) cSeries.fillAuto(unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, 2) ' Now using sheet: Sheet2 Sheet2 = oSheetsIA.getByIndex(2).Value Sheet2.getCellByPosition(0, 1).setValue(4532) Sheet2.getCellByPosition(1, 1).setValue(2345) End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click End End Sub End Class Error detail: System.NullReferenceException was unhandled Message="Object reference not set to an instance of an object." Source="CLIOOCalc" StackTrace: at CLIOOCalc.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Brad\My Documents\Visual Studio 2005\Projects\CLIOOCalc\CLIOOCalc\Form1.vb:line 80 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at CLIOOCalc.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() Note that while entering code, before I execute, VB 2005 shows error message at line 80: oLocale is used before it has been assigned a value. A null reference exception could result at runtime. But line 80 does assign a value -- View this message in context: http://www.nabble.com/CLI-Uno-MS-visual-basic-2005---cannot-format-cell-programatically-tp24943447p24943447.html Sent from the openoffice - users mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org