| View previous topic :: View next topic |
| Author |
Message |
Chris_147
General User
Joined: 28 Aug 2003
Posts: 27
|
 Posted: Fri Oct 10, 2003 1:42 am Post subject: VB: converting Excel files to txt files |
 |
|
Hello all,
Since our IT departement is being hard about another Excel installation (costs too much  ) I wanted to do the following in Visual Basic with OOO.
Can you provide clues on where to look for the following actions:
1) check if OOO (calc) is installed
2) open Excel file
3) take first sheet and save it as tab-delimited text files.
Any help will be greatly appreciated...
Chris |
|
| Back to top |
|
 |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Fri Oct 10, 2003 6:28 am Post subject: |
|
|
Does Visual Basic allow you to trap errors? If so, then execute a line like this...
| Code: | | oServiceManager = CreateObject( "com.sun.star.ServiceManager" ) |
and see if you get an object back. The statement might fail if OOo is not registered on the machine, and cannnot be created by CreateObject. This would indicate the lack of OOo, or at least not a proper installation of OOo. (i.e. someone might mave mistakenly believed that merely copying the files to Program Files constitutes "installation".)
The above statement is for Microsoft Visual FoxPro. I don't know Visual Basic.
Once you have the ServiceManager, you can get the Desktop object like this...
| Code: | | oDesktop = oServiceManager.createInstance( "com.sun.star.frame.Desktop" ) |
The case of methods to OOo may be important, as in "createInstance". I know this is definitely the case for the Python-Uno bridge. I'm not sure if it is the case for the OLE-Uno bridge.
Once you have the Desktop object, you can call loadComponentFromURL to load your Excel document. Since I don't know VB, I'm going to guess at the syntax for creating an empty array, and passing an array argument.
| Code: | Dim oNoArgs()
oDocument = oDesktop.loadComponentFromURL( "file:///C:/Some/Path/File.xls", "_blank", 0, oNoArgs() ) |
If you absolutely can NOT declare an empty array, then you will need to pass an array containing at least one property value. This is what I have to do in Visual FoxPro. I create my own subroutine that creates any arbitrary UNO struct. I call it OOoCreateUnoStruct(). It works, basically like this....
| Code: |
* The parameter cTypeName is the type of struct to create
*
oCoreReflection = oServiceManager.createInstance( "com.sun.star.reflection.CoreReflection" )
oXIdlClass = oCoreReflection.forName( cTypeName )
oResult = .null. * create variable to hold the result
oXIdlClass.createObject( @ oResult ) * the @ is to pass param by reference
*
* oResult now contains the desired struct.
|
Now I can create a PropertyValue easily with a subroutine like this...
| Code: |
FUNCTION OOoPropertyValue( cName, uValue )
oPropertyValue = OOoCreateUnoStruct( "com.sun.star.beans.PropertyValue" )
oPropertyValue.Name = cName
oPropertyValue.Value = uValue
RETURN oPropertyValue
ENDFUNC
|
Now using this machinery, I can create a one element array with a property value like this, and then load the document...
| Code: | * Create a one element array of property values.
Dim oArgs( 0 )
* The only property value inidicates that the document is not hidden when opened,
* which is how most normal documents are opened -- so you can see them on screen.
oArgs( 0 ) = OOoPropertyValue( "Hidden", .F. )
oDocument = oDesktop.loadComponentFromURL( "file:///C:/Some/Path/File.xls", "_blank", 0, oArgs() ) |
In the case of files ending with the suffix XLS, OOo is smart enough to know what import filter to use. But you could specify a particular import filter to use as another property...
| Code: | * Create array of property values.
Dim oArgs( 1 )
oArgs( 0 ) = OOoPropertyValue( "Hidden", .F. )
oArgs( 1 ) = OOoPropertyValue( "FilterName", "MS Excel 4.0" )
oDocument = oDesktop.loadComponentFromURL( "file:///C:/Some/Path/File.xls", "_blank", 0, oArgs() ) |
I posted a complete list of filter names here...
http://www.oooforum.org/forum/viewtopic.php?p=11744#11744
I just arbitrarily picked "MS Excel 4.0" for the above example.
Once you have loaded the document, you can save it using StoreToURL.
| Code: | | oDocument.storeToURL( "file:///C:/Some/Path/NewName.xyz", oArgs() ) |
I used the suffix ".xyz" because I don't know what suffix you want. Again, the oArgs needs to be an array of property values. So you could export using a filter, sort of like this...
| Code: | Dim oArgs( 0 )
oArgs( 0 ) = OOoPropertyValue( "FilterName", "Text - txt - csv (StarCalc)" )
oDocument.storeToURL( "file:///C:/Some/Path/NewFile.csv", oArgs ) |
Lastly, you can close the document by calling oDocument.dispose(). I believe that this is no longer the preferred technique to close a window. I believe there is a close method that takes a boolean parameter. I have not had enough caffeine (or more likely am too lazy) to look it up. See other posts in the Macros and API section regarding how to close a document.
Also look in the API docs and note the difference between storeToURL and storeAsURL.
I pretty much ignored the "first sheet" aspect of your question. Depending on the export filter you use, such as CSV, this may not matter.
Hope this helps. Sorry I don't know Visual Basic.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
Guest
|
| Posted: Mon Oct 13, 2003 5:44 am Post subject: |
|
|
| Quote: | | I have not had enough caffeine (or more likely am too lazy) to look it up |
Too lazy...?
Not to me, thanks for the lengthy and insightfull answer.
I'll try to make something right away!
Chris |
|
| Back to top |
|
|
avantman42
Super User
Joined: 28 Jul 2003
Posts: 751
Location: Staffordshire, UK
|
| Posted: Mon Oct 13, 2003 6:22 am Post subject: |
|
|
Chris,
Two other things that might help:
Andrew Pitonyak's Macro Document has a section on closing OOo documents and OOo itself (section 5.7.1)
DannyB (the same one that wrote the earlier reply) has written a document converter, which will convert a batch of documents from any OOo supported format to any other OOo supported format. It's written in OOo Basic, so might help with converting the FoxPro code that Danny supplied in his reply.
Both are available at OOoMacros (see my sig)
Russ |
|
| Back to top |
|
|
Chris_147
General User
Joined: 28 Aug 2003
Posts: 27
|
| Posted: Thu Oct 16, 2003 6:57 am Post subject: |
|
|
Hi all,
I tried what you described. Using your CreateUnoStruct was a bit difficult to translate to VB. But then I saw Gibson's Mailmerge examples!
So I added that to it and got this
| Code: |
'Creating service manager
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
'Creating a public object to use reflection for structured properties
Set objCoreReflection = objServiceManager.createInstance("com.sun.star.reflection.CoreReflection")
'Creating a Desktop to open files
Set Desktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
'Open the file
Set Document1 = Desktop.LoadComponentFromURL("file:///C:/test1.xls", "_blank", 0, args0)
'Setting properties
Set args1(0) = OOoNamedValue("FilterName", "Text - txt - csv (StarCalc)")
'Store the file
Document1.storeToURL "file:///C:/test1.txt", args1(0)
|
With these functions:
| Code: |
Function OOoNamedValue(cName, uValue)
Dim oPropertyValue As Object
Set oPropertyValue = createStruct("com.sun.star.beans.NamedValue")
oPropertyValue.Name = cName
oPropertyValue.Value = uValue
Set OOoNamedValue = oPropertyValue
End Function
Function createStruct(strTypeName)
Dim classSize As Object
Set classSize = objCoreReflection.forName(strTypeName)
Dim aStruct
classSize.CreateObject aStruct
Set createStruct = aStruct
End Function
|
But I got a type mismatch when running the storeToURL function. (Damn)
After looking up the storeToURL function I saw it needs a PropertyValue iso NamedValue.
So I added the following function:
| Code: |
Function OOoPropertyValue(cName, uValue)
Dim oPropertyValue As Object
Set oPropertyValue = createStruct("com.sun.star.beans.PropertyValue")
oPropertyValue.Name = cName
oPropertyValue.Handle = -1
oPropertyValue.Value = uValue
oPropertyValue.State = 0
Set OOoPropertyValue = oPropertyValue
End Function
|
and adapted the call for setting the properties.
But NO, it still does not work "type mismatch"
Where oh where did I go wrong?
Another question: what's the relation between a NamedValue and a PropertyValue?
And yet another question: how to navigate the documentation of the SDK (API)? I can't seem to get the hang of it. Searching everything on my HD is not the best way...
When everything is new, everything is hard it seems.
Chris |
|
| Back to top |
|
|
avantman42
Super User
Joined: 28 Jul 2003
Posts: 751
Location: Staffordshire, UK
|
| Posted: Thu Oct 16, 2003 7:33 am Post subject: |
|
|
Chris,
I think the problem is probably in the OOoPropertyValue function:
| Code: |
Function OOoPropertyValue(cName, uValue)
Dim oPropertyValue As Object
Set oPropertyValue = createStruct("com.sun.star.beans.PropertyValue")
oPropertyValue.Name = cName
oPropertyValue.Handle = -1
oPropertyValue.Value = uValue
oPropertyValue.State = 0
Set OOoPropertyValue = oPropertyValue
End Function
|
Try commenting out the oPropertyValue.Handle & oPropertyValue.State lines. I'm sure you only need to set the Name & Value of oPropertyValue.
Russ |
|
| Back to top |
|
|
Chris_147
General User
Joined: 28 Aug 2003
Posts: 27
|
| Posted: Thu Oct 16, 2003 7:47 am Post subject: |
|
|
Tried that before, didn't work  |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Thu Oct 16, 2003 9:38 am Post subject: |
|
|
| Quote: | | But I got a type mismatch when running the storeToURL function. |
| Code: | | Document1.storeToURL "file:///C:/test1.txt", args1(0) |
It appears that you are passing element zero of the array, a property value or named value as the second argument. A definite type mismatch.
The second argument to storeToURL is an ARRAY, not a property value. An ARRAY of property values (or named values). Gibson's mail merge example is an excellent example of creating an array of several property values.
Write something like this instead....
| Code: | | Document1.storeToURL( "file:///c:/.......", args1 ) |
pass the entire array args1 as second argument. Don't pass its zero'th element args1(0).
I wish VB had the anonymous arrays of StarBasic. I generally don't like to dimension an array followed by a block of separate assignment statements, I like a single large function that constructs an anonymous (unnamed) array like this....
oDoc.storeToURL( ...., Array( MakePropertyValue(..., ...), MakePropertyValue( ..., ... ) ) )
My second argument to storeToURL would be an array of two properties, and each property has a name and a value.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
Chris_147
General User
Joined: 28 Aug 2003
Posts: 27
|
| Posted: Thu Dec 18, 2003 8:24 am Post subject: |
|
|
I've been busy for some time with other things, but now had some time, and SOLVED my problems!
First a big thanks to DannyB, because you gave me all the pointers that I needed.
If I look now at my code I don't understand why it took me this long to make the little program below.
As said before this program just converts an Excel file to a Tab-delimited text file.
Calc stays hidden and is closed afterwards.
Of course I want to give back to you, so here is my code:
| Code: |
Option Explicit
Dim objServiceManager As Object
Dim objCoreReflection As Object
Dim oResult As Object
Private Sub Command1_Click()
Dim Desktop As Object
Dim Document1 As Object
Dim OpenParams(1)
Dim SaveParams(2)
'Creating service manager
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
'Set the parameters for opening the file
Set OpenParams(0) = OOoPropertyValue("Hidden", True)
Set OpenParams(1) = OOoPropertyValue("ReadOnly", True)
' See http://api.openoffice.org/docs/common/ref/com/sun/star/document/MediaDescriptor.html
'Creating a Desktop to open files
Set Desktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
' See http://api.openoffice.org/docs/common/ref/com/sun/star/frame/XComponentLoader.html
'Open the file
Set Document1 = Desktop.LoadComponentFromURL("file:///C:/test1.xls", "_blank", 0, OpenParams)
'Setting properties
Set SaveParams(0) = OOoPropertyValue("FilterName", "Text - txt - csv (StarCalc)") ' Gives comma delimited with ""
Set SaveParams(1) = OOoPropertyValue("FilterOptions", "9,,0,1,10")
' See http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.htm#1+2+2+3+Filter+Options
Set SaveParams(2) = OOoPropertyValue("Overwrite", True)
'Store the file
Document1.storeToURL "file:///C:/test1.txt", SaveParams
' See http://api.openoffice.org/docs/common/ref/com/sun/star/frame/XStorable.html#storeToURL
'Close Calc
Document1.Close True
End Sub
Function OOoPropertyValue(cName, uValue)
Dim oPropertyValue As Object
Set oPropertyValue = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oPropertyValue.Name = cName
oPropertyValue.Value = uValue
Set OOoPropertyValue = oPropertyValue
End Function
|
Only one question left, how can I check if OpenOffice is installed with VB code?
Is there some registry key or something like that?
Doing a harddrive search for soffice.exe doesn't seem like the best way...  |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Thu Dec 18, 2003 9:53 am Post subject: |
|
|
| Chris_147 wrote: | Only one question left, how can I check if OpenOffice is installed with VB code?
Is there some registry key or something like that?
Doing a harddrive search for soffice.exe doesn't seem like the best way... |
I don't think there is any "official" way to check for OOo being installed.
There probably *is* some registry key you could check for, but I wouldn't know what. Do a "regdump" before installing OOo. Then do a "regdump" after installing OOo. Then "diff" the two text files to see what registry keys were added. Pick a likely candidate. I would suggest something having to do with the uno bridge for windows automation. There might be a registry key with a pathname to soffice.exe. I would not only check for the presence of that registry key, but then I would look at the pathname it points to and see if the soffice.exe file at that location actually exists. If so, then it is likely that OOo is installed.
Another approach, I think I mentioned before, is to simply try to CreateObject() on the service manager, and trap for an error.
Glad to hear you got your problem solved. Each and every OOo migration gives me joy.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
Chris_147
General User
Joined: 28 Aug 2003
Posts: 27
|
| Posted: Fri Dec 19, 2003 1:32 am Post subject: |
|
|
I think I've found a likely candidate in the registry:
\HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\App Paths\soffice.exe
It has two keys:
Default: contains the full path + soffice.exe
Path: contains the full path
I'll make use of that. |
|
| Back to top |
|
|
hfrank
General User
Joined: 04 Jan 2004
Posts: 5
|
| Posted: Sun Jan 04, 2004 11:29 pm Post subject: "Document1.Close True " Error |
|
|
Thanks chris for your post.
It help me to understand the VB&OO.
However, I got an error on line " Document1.Close True " when I run your program.
Would you tell me the reason? |
|
| Back to top |
|
|
Chris_147
General User
Joined: 28 Aug 2003
Posts: 27
|
| Posted: Mon Jan 05, 2004 5:24 am Post subject: |
|
|
No.
No really what error do you get?
But I doubt I can help you... (are you using 1.1.0?)
By the way, it seems my code isn't perfect either.
When I tried in on some pc's here, one gave me the following error:
"The application failed to initialize properly (0xc0000022). Click on ok to terminate"
Seems to be a Window error.
And on other pc's I got a Visual Basic error on the StoreToURL call:
"Runtime error '91': Object Variable or With block variable not set"
Strange thing is that these pc's are all installed from a Ghost copy. So different behaviour is very strange...
Guess I'll have to debug some more.
Maybe it's better to learn Python and do it in that language. After all, VB is dead after version 6. (VB.NET is just C# with another flavor, better do it in C# than VB.NET then)
Python is also cross-platform (like OOo) so that could help also... |
|
| Back to top |
|
|
Marcio de Souza
Guest
|
| Posted: Thu May 27, 2004 6:03 am Post subject: Automation openoffice |
|
|
| Somebody knows as to modify the formattings of a table in writer? |
|
| Back to top |
|
|
|
|
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
|
