Copy multiple files to one

[ltorres]

Hi guys,
I am completely new for openoffice and I desperately need the help.
I have a folder "reports" that contains many calc files 20150401, 20150402, 20150403....(*.ods), each file has a spreadsheet called "sales".

I want to copy each spreadsheet "sales" of each file, to a unique file called "monthconsolidate.ods", with the name of the file copied .

I found this thread viewtopic.php?f=9&t=46200 , but the macro consolidate information in one sheet... and the code is very confused for me.

In ExcelVBA would be like this

Code: Select all

Sub Promedio(mySourcePath)
    Dim MyObject As New Scripting.FileSystemObject
    Dim mySource As Folder
    Dim Numero
   
    Numero = 0
    Set mySource = MyObject.GetFolder(mySourcePath) ' cargo en el objeto todos los archivos del directorio
    For Each myFile In mySource.Files ' para cada elemento del objeto = archivos en la carpeta
        If Right(myFile.Name, 4) = ".xls" Then ' |
            MsgBox myFile.Name ' pinto el nombre
            Workbooks.Open Filename:=mySourcePath & myFile.Name ' abro ese documento de excel
            Sheets("Hoja1").Activate ' activo la hoja 1
            Range("A1:P1").Copy ' copio el rango de celdas A1:P1
            'Vale = Cells(1, 1).Value
            ThisWorkbook.Activate ' activa el libro que tiene abierto actualmente
            Sheets("Hoja1").Activate ' activa la hoja 1
            Range("A" & (Numero + 1)).Select ' selecciona la celda A1, A2, A3, según cambia 'numero' en cada interacción del bucle
            ActiveSheet.Paste ' pega
            Workbooks(myFile.Name).Close ' cierra el documento de excel que abrió
            Numero = Numero + 1 ' vamos a por otro documento :)
        End If
    Next
   
End Sub

Really need your help, because i've read this forum (English/Spanish) but i can't understand and im going crazy
Please help me with this
if anybody has a solution with this;
It's most welcome !
Thanks. blessings
PD: Sorry for my english

[Villeroy]

The topic you linked includes a file named CollectData.ods by JohnSUN-Pensioner which is an almost complete macro suite to merge spreadsheets. If you can not handle that one, you should stick to Excel (forever). We are not here to spoon feed each and every non-programmer with perfectly tailored code nor are we human macro recorders for Excel dummies.

[ltorres]

Thanks friend for your time.
Answer wasn't i needed, maybe someone else will read, and sure will help an excel dummie.

[Villeroy]

Thanks friend for your time.
Answer wasn't i needed, maybe someone else will read, and sure will help an excel dummie.

Use Excel?

[ltorres]

i was working above Excel, but i need a macro into aoo for this task, because an external software generate .ods files and i dont understand as well to apply a macro.

[Villeroy]

i was working above Excel, but i need a macro into aoo for this task, because an external software generate .ods files and i dont understand as well to apply a macro.

Find (and pay) someone to write it or stick to Excel.

[ltorres]

Sure my friend.
But meanwhile i Will try someone here help me.
Thanks again.

[Villeroy]

You know how many files you could copy/paste while waiting for an answer?

[ltorres]

Jejejeje would be fantastic if this procedure i use only for one time... but I've been three weeks reading a code and I cant understand... Nor adapt to my need... i like programming. but I need a guide.

Regards

[clag]

Hello Itorres,

here, I have made a little helper tool for you.
That can collect sheets in the way like you had wished.

SheetCollector.ods

(16.64 KiB) Downloaded 525 times

have fun and let me know how it run for you


greets
clag

[Villeroy]

More features with less code: viewtopic.php?f=21&t=77069

[ltorres]

Hello Itorres,

here, I have made a little helper tool for you.
That can collect sheets in the way like you had wished.

have fun and let me know how it run for you

greets
clag

Hello Clag,i just say you're awesome!!! your file is exactly that i needed and works fine... but seems doesn't work with files with blocked cells
i was trying of diferent ways and with locked cells doesn't work... could you help me please what can i do?, and thank you again. really thanks

PD. attach mi project with testing with security and no security could you check please.
https://mega.co.nz/#!s8xjGCDZ!vbGbQ5tU8 ... pm69KTxzj0

[clag]

Hello ltorres,

I have the macro partly new written, now I use an other way, to get and transfer the data.
So it is possible to copy data also from writeprotected files. And some other changes in details

"SheetCollector_r3.ods" removed

have fun and let me know how it runs now.

greets
clag

edit:
I delet the link to the file, because I was to hurry with, this macro do not import all sheets how it should
but I will fix it soon

[clag]

Hello Itorres,

now I hope the macro work propperly, and import all sheets in the right way,
and the most likely input mistakes or file read errors are catched.

SheetCollector_r4.ods

(20.87 KiB) Downloaded 451 times

everyone's answer or comment is welcome.

greets
clag

[ltorres]

Hello Itorres,

now I hope the macro work propperly, and import all sheets in the right way,
and the most likely input mistakes or file read errors are catched.

SheetCollector_r4.ods

everyone's answer or comment is welcome.

greets
clag

Hello clag, sorry for late reply....
Well... let me say only a thing... You're great person very friendly, with your help i could go home early i was thankful.
The file works fine! Really useful, coworkers are impressed..

I was wondering if I could take any position in the file name, not the end, for example:

"20150501-corregidoms.ods"

and to take part in 501 to avoid having to rename the file and quit "-corregidoms".

And again, really, really thanks blessing for you.

[xdingi]

Hello Clag,

looks like a wonderful solution for my problem.
I cannot get you helper to work though. Every time I klick on "Start the import" the script tells me "Aktion nicht unterstützt. Ungültiger Prozeduraufruf".
What am I doing wrong?

Best regards
xdingi

[Sadi]

This looks very useful, but I couldn't get it working (tried all the available versions up to the latest r4).

Every time I get this error:

Code: Select all

BASIC runtime error.
Object variable not set.

And the macro window opens with this line highlighted:

Code: Select all

oDisplay = oDisplayAccess.getByIndex(0)

Any help would be much appreciated.

[RoryOF]

Please upload the entire macro code you are using.

[Sadi]

Do you want me to upload "SheetCollector_r4.ods"?
For that's what I'm using.

[RoryOF]

Not if you are using it exactly as written. The error you are getting is possibly because of a typo.

[Sadi]

I'm using it as I've downloaded, but I can attach a screenshot of how I've completed the form, although I've tried a number of other variations as well.