OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Looping through each file under a folder and dump the values

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
jaango123
General User
General User


Joined: 12 Apr 2012
Posts: 12

PostPosted: Mon Apr 23, 2012 12:43 am    Post subject: Looping through each file under a folder and dump the values Reply with quote

Hi,

I am looping through each of the .xls files and dumping the values to new workbook based on some conditions. I have done in excel vba macro. Currently we are moving from excel to open office. What is the equivalent for the below lines in Open office basic. ie, Declaring an object for creating new workbook, File system object, Scripting folder, Scripting.File and Excel Application. Also in MS excel we have to select Microsoft scripting runtime, by going to tools>reference from the IDE. What is the equivalent in OO. How to loop through the files in "D:/MyFolder", .xls files? Below is the complete vba code


Code:

Dim new_bk As Workbook
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim srcxlApp As New Excel.Application
Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder("D:\MyFolder\")
Set new_bk = Workbooks.Add
For Each FileItem In SourceFolder.Files
        srcfl_nm = "D:\MyFolder\" & FileItem.Name
        srcxlApp.Workbooks.Open (srcfl_nm)
For i = 2 To 40
            R_nm = Trim(srcxlApp.Sheets(1).Cells(i, "R").Value)
            A_nm = Trim(srcxlApp.Sheets(1).Cells(i, "A").Value)
        If R_nm <> "" Then
                    new_bk.Worksheets(1).Range("A" & AGcnt) = R_nm
                    new_bk.Worksheets(1).Range("B" & AGcnt) = A_nm
                    AGcnt = AGcnt + 1
        End If

           
    Next

    srcxlApp.Workbooks.Close
Next
srcxlApp.Quit
Back to top
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Mon Apr 23, 2012 3:21 am    Post subject: Reply with quote

maybe this can help you

Code:

Sub OpenDocFolder
Dim DocName as object, DocUlr as string, dummy()
sourcePath ="yourpath"
sPath = sourcePath & GetPathSeparator()
sFileName = Dir(sPath, 0)
Do While (sFileName <> "")
sFileName = Dir()
print sFileName
DocUrl = ConvertToURL(sourcePath & sFileName)
DocName = StarDesktop.loadComponentFromURL (DocUrl, "_blank",0, Dummy() )
Loop
End Sub

_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
jaango123
General User
General User


Joined: 12 Apr 2012
Posts: 12

PostPosted: Tue Apr 24, 2012 1:10 am    Post subject: Reply with quote

Thanks. Awesome. I was able to get the details from the document under the filepath. However the document gets open after StarDesktop.loadComponentFromURL, line. How can we close the spreadsheet at the end of the loop? Also How can I open a new open office spreadsheet and put these values there? The code I modified at the end.
Code:

DocUrl = ConvertToURL(sourcePath & GetPathSeparator() & sFileName)
DocName = StarDesktop.loadComponentFromURL (DocUrl, "_blank",0, Dummy() )
Dim oSheet
oSheet = DocName.Sheets(0)
s=oSheet.getCellByPosition(0,0).String
Back to top
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Tue Apr 24, 2012 2:15 am    Post subject: Reply with quote

you can close opened document
DocName = StarDesktop.loadComponentFromURL (DocUrl, "_blank",0, Dummy() )

with

Docname.close(true)
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
jaango123
General User
General User


Joined: 12 Apr 2012
Posts: 12

PostPosted: Tue Apr 24, 2012 3:40 am    Post subject: Reply with quote

Yeah. How to create a new spreadsheet using macro? I need to get these values and pput in that newly created spreadheet. In Excel vba I was using

Code:

Set new_bk = Workbooks.Add
For Each FileItem In SourceFolder.Files
        srcfl_nm = "D:\MyFolder\" & FileItem.Name
        srcxlApp.Workbooks.Open (srcfl_nm)
Back to top
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Tue Apr 24, 2012 5:17 am    Post subject: Reply with quote

sURL = "private:factory/scalc"
Docname = StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, dummy())
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group