Open File and restore it in another Format

[chesterluck]

Hello dear community,

I have the following problem:

I have to open a file (the file name is variing), but i know the share.
Then I have to restore it under the same directory but in a csv format.

Code: Select all

Sub OpenSpecificFolder()
   Dim FP As Object
   Dim ShExec As Object
   Dim X As Integer


   FP = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")

   Dim s as String    
   s = "file:///C:/"             
   FP.SetDisplayDirectory(s)
   FP.appendFilter("All files", "*.*")
   
                     '    X = FP.Execute()
   If X = 1 Then
      ShExec = createUnoService("com.sun.star.system.SystemShellExecute")
      ShExec.execute(FP.Files(0), "", 0)
      Dim Url as String
	  Url = convertToUrl(FP.Files(0))
	  MsgBox Url

   End If
' here i probably have to jump to the opened file   
dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = Url ' here i have to delete the ending and add .csv  
args1(1).Name = "FilterName"
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
args1(2).Value = "59,0,76,1"

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())

   
End Sub

The code doesn't work right:

1).SetDisplayDirectory doesn't work
2)I dont know how to delete the ending of the url and add .csv

It's my first day im working with OO Basic , please help

Thx in advance

Mischa

[rudolfo]

There is a Tutorial for OpenOffice Basic, read especially the section about Working with Documents. You will find an example that opens a .csv file. You just have to replace the .loadComponentFromURL() method with .storeAsUrl().
Looking at your code it looks like you are using the shell (on MS Windows the Windows Explorer) to open the document with the SystemShellExecute service. That's starting another instance and doesn't allow the control that you need. You might not be able to say when the document is completely loaded. Use .loadComponentFromURL() to open documents. All this methods have in common that they are using the Property-Array as parameter (args1 in your code generated by the macro recorder) to specify the file type and other option. And the URL parameter must be in the generic format with protocol specifier and forward slashes (file://...).
But the very handy functions ConvertToUrl() and ConvertFromUrl() will do this for you.

Edit: Forgot to mention that the file-Picker returns a URL like string for the chosen file. You can pass this string directly to loadComponentFromURL. If your code did open the document via ShellExecute you probably don't need any special options when opening the file and can use a empty dummy property array. (some examples for this are in the above mentioned tutorial)

Now for replacing the extension of a file, I use something like:

Code: Select all

  sUrl = oDoc.getURL
  sParts = Split(sUrl,".")
  n = Ubound(sParts)         ' Get the index of the last part (the extension)
  sParts(n) = "csv"          ' No period here!
  sNewUrl = Join(sParts,".")

Split, Join are documented in the F1 Online Help of OpenOffice, together with other string functions that are useful for such modifications.

[rudolfo]

Just for completeness, this is the code for a macro that opens one file with the file picker dialog and save it as .csv

Code: Select all

Sub ConvertFileToCsv()
   Dim oFileDialog as Object
   Dim iAccept as Integer
   Dim oDoc As Object
   Dim sUrl As String, sNewUrl As String
   Dim sParts() As String
   Dim n As Integer
   Dim Dummy() 'An (empty) array of PropertyValues
   Dim FileProperties(1) As New com.sun.star.beans.PropertyValue

   GlobalScope.BasicLibraries.LoadLibrary("Tools")

   oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
   With oFileDialog
      .appendFilter("Spreadsheets (*.ods)", "*.ods" )
      .appendFilter("All files", "*.*")
   End With

   oFileDialog.SetDisplayDirectory(ConvertToUrl("C:\ ")   ' Set your initial path here!

   iAccept = oFileDialog.Execute()

   If iAccept = 1 Then
      sUrl = oFileDialog.Files(0)

      oDoc = StarDesktop.loadComponentFromURL(sUrl, "_blank", 0, Dummy)

      sParts = Split(sUrl,".")
      n = Ubound(sParts)         ' Get the index of the last part (the extension)
      sParts(n) = "csv"          ' No period here!
      sNewUrl = Join(sParts,".")

      FileProperties(0).Name = "FilterName"
      ' "Text CSV" is displayed in the SaveAs Dialog, but the Macro Recorder uses:
      FileProperties(0).Value = "Text - txt - csv (StarCalc)"

      FileProperties(1).Name = "FilterOptions"
      ' The first column are Ascii Values of the separator and the Text Delimiter
      FileProperties(1).Value = "9,34,ANSI,1"                         ' Tab
      'FileProperties(1).Value = Asc(",") & "," & Asc("""") & ",0,1"  ' comma

      ' Changing the file format needs storeToURL, storeAsURL doesn't support this!
      oDoc.storeToURL(sNewUrl, FileProperties())
   End If
   oFileDialog.Dispose()
End Sub

The original Spreadsheet document is still open after the macro has finished. So for processing multiple files (the file picker can select multiple files into the oFileDialog.Files() array) this will need some modifications.
It is only tested with Spreadsheet documents with a single sheet. If you have multiple sheets in the document it might fail, or prompt you for confirmation or silently export only the first sheet, or ...
If the opened file is not in spreadsheet format other weird things might happen.

[namib]

Can I use sParts to change the name of the file as well as changing the extention?

[rudolfo]

Can I use sParts to change the name of the file as well as changing the extention?

sParts in the above code is an array with 2, 3 or whatever number of elements. The crucial point is that it is created with the help of split() with a period as separator. And it is common sense that an extension is the part of a filename after the last period: C:\long\path.with\directories\and.a-file.csv. This works also with filenames that were transformed by ConvertToURL() to something like file:///c:/long/path.with/directories/and.a-file.csv

In an analogous way you can separate the original string into protocol, folder and file segments if you use split() with a slash as separator. Of course when you glue the parts together again with join you have to use the same separator again.
Or you use the string tools from the macro tools library that comes instelled with OpenOffice. Just remember that you have to load it into your macro scope before you can use functions from tools:

GlobalScope.BasicLibraries.LoadLibrary("Tools")

Access to string functions in the tools macro library

tools-library.png (9 KiB) Viewed 5277 times

FileNameoutofPath might be a good candidate for what you want to do. Of course you don't click [Run], but

Edit:  and inspect it a bit closer to see if it does what you need (usually the comment above the function declaration is good enough for this)