[Solved] Opening Calc from VB5

[Peterd51]

Hi,

I've found various bits and pices around the site and the internet but I'm now stuck.

I'm using Vb5 and OO 4.1.4 on a WIndows 10 PC.

Any ideas please?

Regards
Peter

Code: Select all

Private Sub Form_Load()

  Dim oSM As Object                   'Root object for accessing OpenOffice from VB
  Dim oDesk As Object                'First objects from the API
  Dim oDoc As Object                  'First objects from the API
  Dim OpenParam(1) As Object    'Parameters to open the doc
  Dim oPropertyValue As Object
 
  Set oSM = CreateObject("com.sun.star.ServiceManager")

  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
  
  Set oPropertyValue = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
  
  'Open existing doc
  oDoc = oDesk.loadComponentFromURL("file:///D:/OpenOffice/test1.csv", "_blank", 0, OpenParam)
  

'calc loads Ok But I can't seem to do anything with the spreadsheet
'...various things that I've tried so far, all give the same result:
'...error 91 Object variable or With block variable not set
  
  Dim oSheet As Object
  Dim oRange As Object

  oSheet = oDoc.Sheets(0)
  
  Set oSheet = oDoc.Sheets(0)

  Set oSheet = oDoc.Sheets.getByName("Sheet1")

  Set oRange = oSheet.getCellRangeByName("A1:B2")

  Call oSheet.getCellRangeByName("A2").setString("test")

  Set oSheet = oDoc.Sheets.getByName("Sheet1")

  Set oSheet = oDoc.getSheets().getByIndex(0)

  Set oRange = oSheet.getCellRangeByName("A1:B12")

End sub

[FJCC]

When you say "calc loads ok", do you mean you can see the the csv file? If so, a statement like

Code: Select all

Set oSheets = oDoc.getSheets()

should work. I would not expect code like

Code: Select all

  oSheet = oDoc.Sheets(0)
 
  Set oSheet = oDoc.Sheets(0)

to work because I think Sheets(0) is an OpenOffice Basic shortcut for oDoc.Sheets.getByIndex(0).

I don't use VB, so I have to guess about syntax problems. Why isn't there a Set before

Code: Select all

  oDoc = oDesk.loadComponentFromURL("file:///D:/OpenOffice/test1.csv", "_blank", 0, OpenParam)

[Villeroy]

CSV is a plain text database exchange format. No application can load arbitrary csv without lots of additional parameters such as encoding, delimiters and various other details that are not know to us.
Beyond the API
Loading csv directly and correctly into a spreadsheet is not easy, although most of the import parameters are stored so they are loaded with the import dialog when you open the next file.
You can link a csv file to a sheet via Insert>Sheet From File with link option so you have to provide the import details only once and then replace the csv with another file of the same csv flavour.
The Base component provides a very user friendly tool to load text files into spreadsheets where the user does not need to know anything about the files and you can link these database data from csv to spreadsheet ranges.
[Tutorial] Using registered datasources in Calc
[Example] Loading CSV into preformatted spreadsheets

If you desparately need to load csv directly and correcly into a sheet programatically:
First of all, load one of your csv files manually and learn how to apply the correct import settings so all numbers import as numbers, all dates as dates, times as times and text columns remain text even if they contain numeric text. This is essential if your spreadsheet import is intended to make any sense at all.
Hint: Ctrl+F8 displays numeric values in blue fonts.
Secondly, run the following StarBasic macro against your properly imported csv. It shows an input box with the FilterOptions string reflecting the import options that have been used to open the current document. Copy that string into your own code and use it as a property value named "FilterOptions" together with property named "FilterName" having the value "Text - txt - csv (StarCalc)" to indicate the type of file you are going to open.

Code: Select all

Sub showFilterOptions
Dim args(),i%
	args() = ThisComponent.getArgs()
	for i = 0 to uBound(Args())
		if args(i).Name = "FilterOptions" then inputbox "","",args(i).value
	next
End Sub 

[Peterd51]

Hi FJCC,

yes, I can see CALC and it displays the numbers that I placed in the test.csv file. I'll try your 'oDoc.getsheets()' and report back.

I tried all sorts of combinations and, as you suggest I wouldn't expect some to do any good at all, but I was hoping to see at least one line do something. This is VB5 from around 1995 (old but then so am I) and it doesn't have the same features as VB.net, etc. Some commands don't need 'set'...
that 'oDoc = Odesk' line works just as it is in my version of VB.

Hi Villeroy, thanks for the links and explanation. I've not had to think about data types with Excel as it just loads the .csv and displays everything in the right format.

I'm trying to get away from using Excel as I find Windows 10 to be very unstable, crashes the C: Drive every few months, and when I reload MSOffice I need to buy a new licence key every time (apparently they're 'one-shot keys). This wasn't a problem when they only cost a few pounds (Sterling) each but now the price is going up.

I'll play around with your suggestions.

If I can't get access to CALC from my VB programs then I have a couple of other options, use Flex-grids in VB or save each sheet to a CSV then open CALC and run a macro to laod each CSV into a sheet, but they would both be a bit messy.

One other point comes to my mind, is there a list of the '.' extensions to commands such as Doc, etc? Like oDoc.x, oDoc.y, etc, and what these extensions do?

Thanks to both of you.
Regards
Peter

[Villeroy]

Why don't you use Excel if Excel is the one tool that is working for you?

[Peterd51]

Hi,

I find Windows 10 is unstable, it's crashed around six times in 15 months, it wipes my C: drive each time and when I reload MS Office I have to buy a new licence key each time.

So I'm looking for another option.

Regards
Peter

[Zizi64]

So I'm looking for another option.

Maybe you need looking for another option for the VB5 too.

[JeJe]

I've got VB6. The following adapted code ran with no errors - an ods file being loaded and the word "test" appearing

with the alternative csv line it failed at the Set oSheet = oDoc.Sheets.getByName("Sheet1") but it was just a simple file with a couple of comma separated entries.

Code: Select all

Private Sub Form_Load()

  Dim oSM As Object                   'Root object for accessing OpenOffice from VB
  Dim oDesk As Object                'First objects from the API
  Dim oDoc As Object                  'First objects from the API
  Dim OpenParam(1) As Object    'Parameters to open the doc
  Dim oPropertyValue As Object

  Set oSM = CreateObject("com.sun.star.ServiceManager")

  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
 
  Set oPropertyValue = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
 'Dim args()
'Set objDocument = oDesk.loadComponentFromURL("private:factory/swriter", "_blank", 0, args)
  'Open existing doc
  Set oDoc = oDesk.loadComponentFromURL("file:///C:/Untitled 2.ods", "_blank", 0, OpenParam)
'Set oDoc = oDesk.loadComponentFromURL("file:///C:/Untitled 2.csv", "_blank", 0, OpenParam)


'calc loads Ok But I can't seem to do anything with the spreadsheet
'...various things that I've tried so far, all give the same result:
'...error 91 Object variable or With block variable not set
 
  Dim oSheet As Object
  Dim oRange As Object

  'oSheet = oDoc.Sheets(0)
 
  Set oSheet = oDoc.Sheets(0)

  Set oSheet = oDoc.Sheets.getByName("Sheet1")

  Set oRange = oSheet.getCellRangeByName("A1:B2")

  Call oSheet.getCellRangeByName("A2").setString("test")

  Set oSheet = oDoc.Sheets.getByName("Sheet1")

  Set oSheet = oDoc.getSheets().getByIndex(0)

  Set oRange = oSheet.getCellRangeByName("A1:B12")

End Sub

[Peterd51]

Hi Zizi64,

well I've been writing Quick Basic since the mid 80s and using the VB5 IDE since the mid 90s, so I have a lot of code, forms, etc, that I re-use. It's a major project to learn a new langauge and convert all of these. This is a first step.

Jeje: thanks for your message.

I can load a workbook, change sheets, select a cell or range of cells and read or write data to those cells.

Apologies to FJCC, yes the ' oDoc = oDesk...' line did need 'Set' in front, I got confused with all the different combinations that I tried. It actually loaded and started CALC but generated an 'error 91' that I was trapping in my error handler with a 'resume next'.

This works so far but I've noticed that as it loads a workbook with three sheets, CALC starts on 'Sheet1", I can select "Sheet2" and put data in a cell, etc, but the CALC display still shows "Sheet1". Is this normal please, as with Excel 'Sheets("Sheet2").select' will change the display to show sheet 2?

Regards
Peter

Code: Select all

  Dim oSM As Object             'Root object for accessing OpenOffice from VB
  Dim oDesk As Object           'First objects from the API
  Dim oDoc As Object            'First objects from the API
  Dim OpenParam(1) As Object    'Parameters to open the doc

  Set oSM = CreateObject("com.sun.star.ServiceManager")

  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
  
  'Open existing doc
  Set oDoc = oDesk.loadComponentFromURL("file:///D:/Open%20Office/test%201.ods", "_blank", 0, OpenParam)
  
  Dim oSheets As Object
  Dim oSheet As Object
  Dim oRange As Object

  Set oSheets = oDoc.getSheets()

'I tested various combinations below, all seem to work as expected: 
  Set oSheet = oSheets.getByIndex(1)
  Set oSheet = oSheets.getByName("Sheet2")

  Call oSheet.getCellRangeByName("A3").setString("test")
  Call oSheet.getCellRangeByName("B1")
  Call oSheet.getCellRangeByName("C1").setString("test")

  xfer1 = oSheet.getCellRangeByName("C1").GetString

  Set oRange = oSheet.getCellRangeByName("A1:B2")
  Call oSheet.getCellRangeByName("A3").setString("test")
  Call oSheet.getCellRangeByName("A1").setValue(2)
  
  Set oRange = oSheet.getCellRangeByName("A1")
  oRange.clearContents (2)
  oRange.clearContents (1)
  oRange.clearContents (255)

[Villeroy]

You don't need to learn a new language. You have to learn about an extremely complex API called Universal Network Objects (UNO).The same API is accessible via COM, Python, Java, JavaScript, C++ and potentially any other language for which someone has been written a comparatively simple bridge to the UNO API.
But first of all you should know the application very well, for instance how to import text data correctly.

[Peterd51]

Hi Villeroy,

thanks for the input. QB running in the VB5 IDE has worked well for years, no problem at all in XP and MS Office 2000, but there are things missing that I now find I need to work with later apps.

For instance I can't find any way of setting 'arg()' to create a new file, but I can load an empty base file off the drive, poke data to the cells and then save it under its 'working name', so I can work around that problem.

As I can now place data directly into CALC cells I shouldn't need to import a .csv, but if I do then I'll be looking at your previous suggestion about setting various data types.

Have you any suggestions to get CALC to show a specific sheet when I switch to it rather than staying on the sheet it loaded on, or is this how it works when accessing from an external program?

Regards
Peter

[JeJe]

A new document is
Set objDocument = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, args)

see https://www.openoffice.org/api/docs/com ... oader.html

With the help of the information about automating Openoffice and searching past threads here
and so on I should imagine things like switching sheets will just need a little searching.

[Villeroy]

http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html

[Peterd51]

Hi JeJe,

thanks, I don't know where I found the command to open a new Calc but it was wrong, this works:

Code: Select all

Dim arg(1) As Object

Set oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, arg())

Thanks to all who made suggestions, I can use this now so I'll mark it as 'solved'.

As I play around with it I may have other queries and I'll post a new question if required.

Regards
Peter