Page 1 of 1

[Solved] Read all ods-Files from a directory & analyse cells

Posted: Tue Jul 11, 2017 11:43 am
by Kola
Hello,
I want to read all ods-files from a directory and want to analyse some cells in the files. My problem ist reading the cells of the opened ods-files to collect the data in the actuell sheet.
Have someone an easy solution ?

Code: Select all

Sub readfiles() 
    strPath = "D:\Snippings\test\"
    strExt = "*.ods"
    Dim strFile As String
    If strPath = "" Then
        Exit Sub
    Else
        strFile = Dir(strPath & strExt)
        Do While Len(strFile) > 0
        Filename=strPath & strFile
        sURL = ConvertToURL(Filename)
		oDoc = StarDesktop.loadComponentFromURL(sURL, "_blank", 0, Array())
            
                rechdoc = thisComponent '??????want to read a cell in the opened file not my current sheet
	        rechsheet = rechdoc.sheets(0)
		rechdat = rechsheet.getCellRangeByName("$F$16")
			
		' write data to cells in my current sheet
   
            oDoc.close(true)       
            strFile = Dir() ' next file
        Loop
    End If
End Sub
thanks for helping me!

Re: Reading all ods-Files from a directory and analyse cells

Posted: Tue Jul 11, 2017 2:02 pm
by FJCC
oDoc refers to the document you opened and ThisComponent refers to the document that calls the macro.

Code: Select all

    strPath = "D:\Snippings\test\"
    strExt = "*.ods"
    Dim strFile As String
    If strPath = "" Then
        Exit Sub
    Else
        strFile = Dir(strPath & strExt)
'#### New line of code
ActiveSheet = ThisComponent.CurrentController.ActiveSheet

        Do While Len(strFile) > 0
        Filename=strPath & strFile
        sURL = ConvertToURL(Filename)

oDoc = StarDesktop.loadComponentFromURL(sURL, "_blank", 0, Array())
           
          '      rechdoc = thisComponent '??????want to read a cell in the opened file not my current sheet
      rechsheet = oDoc.sheets(0)
      rechCell = rechsheet.getCellRangeByName("$F$16")
      rechdat = rechCell.Value 'or rechdat = rechCell.String if the cell contains text   
      ' write data to cells in my current sheet
      WriteCell = ActiveSheet.getCellRangeByName("A1") 'ActiveSheet is defined above
      WriteCell.Value = rechdat
   
            oDoc.close(true) 
......      

Re: Reading all ods-Files from a directory and analyse cells

Posted: Tue Jul 11, 2017 3:55 pm
by Kola
Many many thanks!!!!!!!!!!!!!!!!
It works fine!

Last question:
I have to program something new in OO next time. Is it advantageous to switch to Phyton or JS?

Re: Reading all ods-Files from a directory and analyse cells

Posted: Tue Jul 11, 2017 4:16 pm
by FJCC
Python is a much more powerful language but I think the main difficulty of writing macros is learning the API. For example, knowing that to get the active sheet you write something like

Code: Select all

ActiveSheet = ThisComponent.CurrentController.ActiveSheet
That is much the same in Basic or Python, though ThisComponent is specific to Basic. Basic is more forgiving about matching the case of commands, getbyname and getByName both work in Basic, and it is more direct in showing the location of errors. If you are new to programming, I suggest sticking with Basic for a while. Others may disagree. I find Java Script far more cumbersome to work with.