[Solved] Copy cells from spreadsheets in a folder to a File

Creating a macro - Writing a Script - Using the API

[Solved] Copy cells from spreadsheets in a folder to a File

Postby malcolmr » Sun Oct 01, 2017 7:01 pm

Hey guys/gals can you help me with this. By using a macro, I want to copy from specific cells from lots of spreadsheets by looping through all of them inside a folder and store the data into a single spreadsheet where I will give the location of the cells.

Example:

Code: Select all   Expand viewCollapse view

Sub S_import_from_A_C

oCdoc=thiscomponent
sURLFolder=replace(oCdoc.url,oCdoc.title,"")
oCSheet=oCdoc.sheets(0)
dim i as integer

i=1

Dim FileProperties(2) As New com.sun.star.beans.PropertyValue
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="scalc: Text - txt - csv (StarOffice Calc)"
FileProperties(1).Name = "USE_CONFIG"
FileProperties(1).Value ="3"
FileProperties(2).Name = "Hidden"
FileProperties(2).Value = True

sURLFolderA=sURLFolder+"*.ods"

Do While Len(sURLFolderA)>0
oAdoc = StarDesktop.loadComponentFromURL(sURLFolderA, "_blank", 0, FileProperties())

oASheet=oAdoc.sheets(0)'Tabelle 3
oArange=oASheet.getCellRangeByName("K3")'source
oAarray=oArange.getDataArray()
oCrange=oCSheet.getCellRangeByName("A" & i)'target
oCrange.setDataArray(oAarray)
oArange=oASheet.getCellRangeByName("K4")'source
oAarray=oArange.getDataArray()
oCrange=oCSheet.getCellRangeByName("B" & i)'target
oCrange.setDataArray(oAarray)
oAdoc.close (-1)

i=i+1

Loop

End Sub


I'm getting an error while using the above code, while trying to loop through all the files one by one.
Last edited by malcolmr on Mon Oct 02, 2017 5:18 pm, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
malcolmr
 
Posts: 4
Joined: Sun Oct 01, 2017 6:37 pm

Re: Copy cells from spreadsheets in a folder to a Single Fi

Postby Villeroy » Sun Oct 01, 2017 8:03 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25763
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy cells from spreadsheets in a folder to a Single Fi

Postby malcolmr » Mon Oct 02, 2017 1:02 pm



Can I get a more simpler code like the one above. I just need to loop through all of the Spreadsheets in a folder. Maybe someone can modify the code above and make it more simpler.

Thank You in Advance.
OpenOffice 4.1.3 on Windows 10
malcolmr
 
Posts: 4
Joined: Sun Oct 01, 2017 6:37 pm

Re: Copy cells from spreadsheets in a folder to a Single Fi

Postby Villeroy » Mon Oct 02, 2017 4:44 pm

There is nothing simplier than getDataArray from the used range of a sheet and then setDataArray on the target sheet. It is a hundred times faster and easier than looping through cells.
Walking through a directory in Basic is possible but awkward.
Non-recursive example:
Code: Select all   Expand viewCollapse view
d = "/tmp/test/"
a = Array("*.xls", "*.ods", "*.xlsx", "*.xlsm")
for each s in a
   f = dir(d & s)
   while len(f) > 0
      print f
      f = dir
   wend
next

With my template you would have to specify the 4 patterns *.xls, *.ods, *.xlsx, *.xlsm one after the other in cell B3 named "Pattern" and run the macro 4 times.

Side note: You can write macros in Python which is a real programming language for adults.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25763
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy cells from spreadsheets in a folder to a Single Fi

Postby malcolmr » Mon Oct 02, 2017 5:17 pm

Thank you very much for your help. I have "googled" the problem and found that the solution to be much simpler in Microsoft Excel.
OpenOffice 4.1.3 on Windows 10
malcolmr
 
Posts: 4
Joined: Sun Oct 01, 2017 6:37 pm

Re: Copy cells from spreadsheets in a folder to a Single Fi

Postby Villeroy » Mon Oct 02, 2017 7:10 pm

malcolmr wrote:Thank you very much for your help. I have "googled" the problem and found that the solution to be much simpler in Microsoft Excel.

Easier than downloading a sheet, fill out 2 or 3 cells and run it once for each file name pattern?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25763
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy cells from spreadsheets in a folder to a Single Fi

Postby malcolmr » Mon Oct 02, 2017 9:31 pm

Villeroy wrote:Easier than downloading a sheet, fill out 2 or 3 cells and run it once for each file name pattern?


I guess so :).

Code: Select all   Expand viewCollapse view

Sub ImportKeyDataFromCSVs()
'Author:    Jerry Beaucaire
'Date:      3/27/2014
'Summary:   Import specific data from all CSV files from a folder into a single sheet
Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet
Dim fPath   As String
Dim fCSV    As String
Dim NR      As Long

fPath = "C:\2010\Import\"                       'path to CSV files, include the final \ in this string
Set wsMstr = ThisWorkbook.Sheets("MasterCSV")   'sheet in thisworkbook to collate data into
                                                'optionally clear prior data, skipping row1 titles
If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _
    = vbYes Then wsMstr.UsedRange.Offset(1).ClearContents
NR = wsMstr.Range("A" & Rows.Count).End(xlUp).Row + 1   'next empty row to add

Application.ScreenUpdating = False  'speed up macro
fCSV = Dir(fPath & "*.csv")         'start the CSV file listing
    Do While Len(fCSV) > 0
        Set wbCSV = Workbooks.Open(fPath & fCSV)    'open a CSV file
                                                    'copy key data cells into master sheet and close source file,
                                                    'add as many key cells in this section as needed
        wsMstr.Range("A" & NR).Value = Range("H7").Value
        wsMstr.Range("B" & NR).Value = Range("M7").Value
        wsMstr.Range("C" & NR).Value = Range("N7").Value
        wsMstr.Range("D" & NR).Value = Range("F7").Value
        wsMstr.Range("E" & NR).Value = Range("J7").Value
        wsMstr.Range("F" & NR).Value = Range("K7").Value
       
        wbCSV.Close False           'close the opened CSV
        NR = NR + 1                 'increment next target row
        fCSV = Dir                  'ready next CSV filename
    Loop

Application.ScreenUpdating = True
End Sub



Btw here's the code which I've found online. I will be forever grateful to you if you can help me convert it, so that I can use it in OpenOffice Calc.
OpenOffice 4.1.3 on Windows 10
malcolmr
 
Posts: 4
Joined: Sun Oct 01, 2017 6:37 pm

Re: [Solved] Copy cells from spreadsheets in a folder to a

Postby Villeroy » Tue Oct 03, 2017 12:57 pm

You know that this macro does a completely different thing?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25763
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests