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

Creating a macro - Writing a Script - Using the API

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

Postby Kola » Tue Jul 11, 2017 11:43 am

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   Expand viewCollapse view
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!
Last edited by Kola on Tue Jul 11, 2017 5:09 pm, edited 1 time in total.
Open Office 4.1.3
Kola
 
Posts: 2
Joined: Tue Jul 11, 2017 11:14 am

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

Postby FJCC » Tue Jul 11, 2017 2:02 pm

oDoc refers to the document you opened and ThisComponent refers to the document that calls the macro.
Code: Select all   Expand viewCollapse view
    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)
......     
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6030
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby Kola » Tue Jul 11, 2017 3:55 pm

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?
Open Office 4.1.3
Kola
 
Posts: 2
Joined: Tue Jul 11, 2017 11:14 am

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

Postby FJCC » Tue Jul 11, 2017 4:16 pm

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   Expand viewCollapse view
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.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6030
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Macros and UNO API

Who is online

Users browsing this forum: Yahoo [Bot] and 7 guests