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

[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!

[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) 
......      

[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?

[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.