Page 1 of 1
[Solved] Copy multiple files to one
Posted: Sun May 03, 2015 6:19 am
by 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
Re: Copy multiple files to one
Posted: Sun May 03, 2015 2:27 pm
by 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.
Re: Copy multiple files to one
Posted: Mon May 04, 2015 12:52 am
by ltorres
Thanks friend for your time.
Answer wasn't i needed, maybe someone else will read, and sure will help an excel dummie.
Re: Copy multiple files to one
Posted: Mon May 04, 2015 4:26 pm
by Villeroy
ltorres wrote:Thanks friend for your time.
Answer wasn't i needed, maybe someone else will read, and sure will help an excel dummie.
Use Excel?
Re: Copy multiple files to one
Posted: Mon May 04, 2015 10:01 pm
by 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.
Re: Copy multiple files to one
Posted: Mon May 04, 2015 11:47 pm
by Villeroy
ltorres wrote: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.
Re: Copy multiple files to one
Posted: Mon May 04, 2015 11:50 pm
by ltorres
Sure my friend.
But meanwhile i Will try someone here help me.
Thanks again.
Re: Copy multiple files to one
Posted: Mon May 04, 2015 11:58 pm
by Villeroy
You know how many files you could copy/paste while waiting for an answer?
Re: Copy multiple files to one
Posted: Tue May 05, 2015 12:05 am
by 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
Re: Copy multiple files to one
Posted: Sat May 09, 2015 3:56 pm
by clag
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
Re: Copy multiple files to one
Posted: Sat May 09, 2015 11:08 pm
by Villeroy
More features with less code:
viewtopic.php?f=21&t=77069
Re: Copy multiple files to one
Posted: Mon May 18, 2015 7:07 am
by ltorres
clag wrote: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
Re: Copy multiple files to one
Posted: Wed May 20, 2015 3:20 pm
by 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
Re: Copy multiple files to one
Posted: Thu May 21, 2015 10:01 pm
by 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.
everyone's answer or comment is welcome.
greets
clag
Re: Copy multiple files to one
Posted: Sun Jun 21, 2015 5:54 am
by ltorres
clag wrote: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:
"20150
501-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.
Re: Copy multiple files to one
Posted: Thu Mar 03, 2016 9:41 am
by 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
Re: Copy multiple files to one
Posted: Tue Apr 12, 2016 3:23 pm
by 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.
Re: Copy multiple files to one
Posted: Tue Apr 12, 2016 4:41 pm
by RoryOF
Please upload the entire macro code you are using.
Re: Copy multiple files to one
Posted: Tue Apr 12, 2016 5:51 pm
by Sadi
Do you want me to upload "SheetCollector_r4.ods"?
For that's what I'm using.
Re: Copy multiple files to one
Posted: Tue Apr 12, 2016 5:57 pm
by RoryOF
Not if you are using it exactly as written. The error you are getting is possibly because of a typo.
Re: Copy multiple files to one
Posted: Tue Apr 12, 2016 6:10 pm
by 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.