Ask Your Question
0

How to open MS excel file in Libre office macro

asked 2015-06-18 07:17:20 +0200

Lion gravatar image

updated 2015-06-18 08:22:03 +0200

karolus gravatar image

Hello Guys,

I am using macro in libreoffice , I need some requirement to open .CSV file in macro coding , and I could it . Now these time I need to open MS Excel file as a input , and I don't know how to open it in macro coding

here I paste coding of open file of .CSV

Sub TestMacro
   Dim oDocument as Object
   FName="D:\Projects\Input_file" 
   fnURL=ConvertToURL(FName+".csv")
   'Create new document and import data'
   oDocument = StarDesktop.LoadComponentFromURL( fnURL, "_blank", 0, _
   Array(MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ), _
   MakePropertyValue( "FilterOptions", "59/9,34,0,1,1/1/1/1/1/1/1/1" )

   'property function'
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
   oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   EndIf
  If Not IsMissing( uValue ) Then 
      oPropertyValue.Value = uValue 
  EndIf
MakePropertyValue() = oPropertyValue 
End Function

Now anybody can figure it out that How can open Excel file instead of CSV file then let me know

Thnks Lion

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-06-18 07:54:29 +0200

pierre-yves samyn gravatar image

Hi - Something like this:

Sub OpenXLS

Dim oDoc As Object
Dim sUrl As String

Dim Prop(0) as New com.sun.star.beans.PropertyValue

Prop(0).name="FilterName"
Prop(0).value="MS Excel 97"

sUrl = convertToURL("c:\Test\foo.xls")

if fileExists(sUrl) then
    oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, Prop())
else
    msgbox "Not found"
end if
End Sub

See : API and Filter options

Regards

edit flag offensive delete link more

Comments

Hello Dear,

Really appreciate, Thank you so much

Do me favor pls Just tell me How can I open CSV file without using that function which I have mentioned in above code

function name is ; "MakePropertyValue"

actually this code is given by one friend and I don't understand much more this function and It's seems complicated

So do needful for same How to open CSV file without using that function..?

Thnks Lion

Lion gravatar imageLion ( 2015-06-22 08:01:25 +0200 )edit

This is good but here I facing one issue now If I am opening " MS Excel 97 " format file then Its opening but If file is "Microsoft Excel Work sheet " then its not opening

what's matter..? anybody knows I mean what should be filter name there..?

Thnks

Lion gravatar imageLion ( 2015-06-22 11:57:57 +0200 )edit

I do not have Excel for years. All I can say is that the filter list is:

  • MS Excel 2003 XML
  • MS Excel 4.0
  • MS Excel 4.0 Vorlage/Template
  • MS Excel 5.0/95
  • MS Excel 5.0/95 Vorlage/Template
  • MS Excel 95
  • MS Excel 95 Vorlage/Template
  • MS Excel 97
  • MS Excel 97 Vorlage/Template

You find the right one :)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-06-22 15:39:36 +0200 )edit

Hello Dear Thanks for reply, Actually I know all of filter options for Excel and tried with all option but it didn't work But when I am trying ...I just remove filter option and Its works Strange but true ,Code look like

    Sub OpenXLS

Dim oDoc As Object
Dim sUrl As String

Dim Prop(0) as New com.sun.star.beans.PropertyValue

sUrl = convertToURL("c:\Test\foo.xls")

if fileExists(sUrl)
Lion gravatar imageLion ( 2015-06-23 07:25:46 +0200 )edit

Let me ask here finaly

How to open Text file in libre office macro..?

I have checked all filter option but not found

Lion

Lion gravatar imageLion ( 2015-06-23 07:51:09 +0200 )edit

LibreOffice usually find the filter to use. We define the option especially when you want to specify options. So, same for opening a text file. But your question is vague: to open a text in writer filter is not needed (this is the default), in Calc is the same filter for CSV. You can also open a text by the basic Open statement to manipulate by program (it is not displayed in the interface).

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-06-23 09:15:39 +0200 )edit
0

answered 2015-06-22 08:26:17 +0200

Lion gravatar image

updated 2015-06-22 09:46:08 +0200

pierre-yves samyn gravatar image
    Sub TestMacro

Dim oDoc As Object
Dim sUrl As String

Dim Prop(1) as New com.sun.star.beans.PropertyValue

Prop(0).name="FilterName"
Prop(0).value="Text - txt - csv (StarCalc)"
Prop(1).name="FilterOptions"
Prop(1).value="44,34,76,1,,0,false,true,true,false"

sUrl = convertToURL("D:\Projects\abc.csv")

if fileExists(sUrl) then
    oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, Prop())
else
    msgbox "Not found"
end if
End Sub

Is this correct..code?

edit flag offensive delete link more

Comments

Hi - I fixed 3 lines (1 instead of zero):

  • Dim Prop... to dimension 2 elements (numbering starts at zero)
  • Prop(1)... for FilterOptions (with zero you replace the first declared)
pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-06-22 09:53:12 +0200 )edit

Thnks Dude I will check it

thnks for answer

Lion gravatar imageLion ( 2015-06-22 11:58:56 +0200 )edit

Hello what is filter name for "Microsoft Excel Worksheet"

I know the filter name is "Microsoft Excel 97-2003 Worksheet" that filter name is "MS Excel 97" but pls tell me filter name of Microsoft Excel Worksheet"

thnks

Lion gravatar imageLion ( 2015-06-22 12:01:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-06-18 07:17:20 +0200

Seen: 1,956 times

Last updated: Jun 22 '15