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

VB: converting Excel files to txt files

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Chris_147
General User
General User


Joined: 28 Aug 2003
Posts: 27

PostPosted: Fri Oct 10, 2003 1:42 am    Post subject: VB: converting Excel files to txt files Reply with quote

Hello all,

Since our IT departement is being hard about another Excel installation (costs too much Smile) 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
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Fri Oct 10, 2003 6:28 am    Post subject: Reply with quote

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
View user's profile Send private message
Guest






PostPosted: Mon Oct 13, 2003 5:44 am    Post subject: Reply with quote

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
Super User


Joined: 28 Jul 2003
Posts: 751
Location: Staffordshire, UK

PostPosted: Mon Oct 13, 2003 6:22 am    Post subject: Reply with quote

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
View user's profile Send private message
Chris_147
General User
General User


Joined: 28 Aug 2003
Posts: 27

PostPosted: Thu Oct 16, 2003 6:57 am    Post subject: Reply with quote

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
View user's profile Send private message
avantman42
Super User
Super User


Joined: 28 Jul 2003
Posts: 751
Location: Staffordshire, UK

PostPosted: Thu Oct 16, 2003 7:33 am    Post subject: Reply with quote

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
View user's profile Send private message
Chris_147
General User
General User


Joined: 28 Aug 2003
Posts: 27

PostPosted: Thu Oct 16, 2003 7:47 am    Post subject: Reply with quote

Tried that before, didn't work Sad
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Thu Oct 16, 2003 9:38 am    Post subject: Reply with quote

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
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Oct 25, 2003 2:24 pm    Post subject: Reply with quote

Chris_147 wrote:
I tried what you described. Using your CreateUnoStruct was a bit difficult to translate to VB. But then I saw Gibson's Mailmerge examples!


See this....

http://www.oooforum.org/forum/viewtopic.php?t=3510

Use Bridge_GetStruct(), and then you don't have to mess with CoreReflection and building your own "createStruct" function.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Chris_147
General User
General User


Joined: 28 Aug 2003
Posts: 27

PostPosted: Thu Dec 18, 2003 8:24 am    Post subject: Reply with quote

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... Wink
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Thu Dec 18, 2003 9:53 am    Post subject: Reply with quote

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. Smile
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Chris_147
General User
General User


Joined: 28 Aug 2003
Posts: 27

PostPosted: Fri Dec 19, 2003 1:32 am    Post subject: Reply with quote

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
View user's profile Send private message
hfrank
General User
General User


Joined: 04 Jan 2004
Posts: 5

PostPosted: Sun Jan 04, 2004 11:29 pm    Post subject: "Document1.Close True " Error Reply with quote

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
View user's profile Send private message
Chris_147
General User
General User


Joined: 28 Aug 2003
Posts: 27

PostPosted: Mon Jan 05, 2004 5:24 am    Post subject: Reply with quote

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
View user's profile Send private message
Marcio de Souza
Guest





PostPosted: Thu May 27, 2004 6:03 am    Post subject: Automation openoffice Reply with quote

Somebody knows as to modify the formattings of a table in writer?
Back to top
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API 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