| View previous topic :: View next topic |
| Author |
Message |
jaango123
General User
Joined: 12 Apr 2012
Posts: 12
|
 Posted: Mon Apr 23, 2012 12:43 am Post subject: Looping through each file under a folder and dump the values |
 |
|
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 |
|
 |
patel
Power User
Joined: 14 Apr 2012
Posts: 54
Location: Italy
|
| Posted: Mon Apr 23, 2012 3:21 am Post subject: |
|
|
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 |
|
|
jaango123
General User
Joined: 12 Apr 2012
Posts: 12
|
| Posted: Tue Apr 24, 2012 1:10 am Post subject: |
|
|
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 |
|
|
patel
Power User
Joined: 14 Apr 2012
Posts: 54
Location: Italy
|
| Posted: Tue Apr 24, 2012 2:15 am Post subject: |
|
|
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 |
|
|
jaango123
General User
Joined: 12 Apr 2012
Posts: 12
|
| Posted: Tue Apr 24, 2012 3:40 am Post subject: |
|
|
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 |
|
|
patel
Power User
Joined: 14 Apr 2012
Posts: 54
Location: Italy
|
| Posted: Tue Apr 24, 2012 5:17 am Post subject: |
|
|
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 |
|
|
|
