Sending data from multiple files to only one file via macro

Discuss the spreadsheet application
Post Reply
qfito
Posts: 6
Joined: Tue Aug 07, 2018 5:04 am

Sending data from multiple files to only one file via macro

Post by qfito »

I know that maybe someone else asked you something similar. However, I have not been able to find the specific solution to my problem. That's why I ask for your help, hoping you can support me.

Below I summarize what I would like to do through a macro with OOo basic:

1. The files will be stored in a directory, a several Origin files and only one Destination file.

I imagine that for convenience the macro could be executed in the same directory where the files are located, no matter what. As the following vba lines:

Code: Select all

Folder = ActiveWorkbook.Path & "\"

nFile = 1

Filename = Dir(Folder & "RVTools_" & "*.xl*")

'...

'More loop to go file by file. With the intention of copying the selected columns of different sheets in workbook from the file originA to the first sheet of destinationA.

'As:

Do while file <> ""
	Workbooks.Open
	For each sheet in Workbooks(file).Worksheets
	allsheets = Workbooks(OriginX).Worksheets.Count
	Next file
	Workbooks(file).close
	File = Dir()
	
Loop

2. Each source file will have several columns per sheet and several sheets per workbook.Type the following vba lines:

Code: Select all

Workbooks(File).Worksheets(Sheet.Name).Copy after:=Workbooks(OriginX).Worksheets(allsheets)


3. Copy the contents of some columns of the different sheets of the OriginA, OriginB, OriginC, etc,. workbooks to the Destination file.

OriginA multiple sheets --> Destination Sheet1
OriginB multiple sheets --> Destination Sheet2
.
.
.
OriginN multiple sheets --> Destination SheetN

Thanks again and I hope someone has an idea of how to solve this problem, because I'm lost.
Last edited by qfito on Tue Aug 07, 2018 11:29 am, edited 1 time in total.
OpenOffice 6.0.5.2 on Manjaro
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Sending data from multiple files to only one file via ma

Post by Zizi64 »

As the following vba lines:
Try your MS VBA macro in the LibreOffice. It has a littlebit higher compatibility with the foreign fileformats and with the VBA macros.

But it is better to rewrite all of your macros based on the API functions.
API: Application Programming Interface.
Start the studying of the API functions by Andrew Pitonyak' free macro books:
http://www.pitonyak.org/oo.php
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Sending data from multiple files to only one file via ma

Post by Lupp »

First of all:
-1- Let us regard the document collecting the data as the "active object". To actually get the documents supplying data to send them would require to organize a process to open them for starting the "sending". The collecting sheet is the "natural place" to run a respective process from.
-2- Dont't think Excel-VBA. The Dir command, however, should work the same way in AOO / LibO Basic.

Did you study viewtopic.php?f=21&t=77069 ?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
qfito
Posts: 6
Joined: Tue Aug 07, 2018 5:04 am

Re: Sending data from multiple files to only one file via ma

Post by qfito »

Zizi64 wrote:
As the following vba lines:
Try your MS VBA macro in the LibreOffice. It has a littlebit higher compatibility with the foreign fileformats and with the VBA macros.

But it is better to rewrite all of your macros based on the API functions.
API: Application Programming Interface.
Start the studying of the API functions by Andrew Pitonyak' free macro books:
http://www.pitonyak.org/oo.php

Thank you very much for your prompt response. Actually, I set vba code example because it is the one I found when trying to solve my problem. In some forums they also suggest the documentation of Andrew Pitonyak however I think it would take longer to learn OOobasic / LibObasic to develop the code from scratch.

I have found something written in OOobasic however I do not know how to adapt it for my specific problem because I'm not an expert programmer. Anyway, I appreciate your suggestion.

The OOobasic code that I mentioned previously is in the following links:

https://ask.libreoffice.org/pt-br/quest ... via-macro/

viewtopic.php?t=55228
OpenOffice 6.0.5.2 on Manjaro
qfito
Posts: 6
Joined: Tue Aug 07, 2018 5:04 am

Re: Sending data from multiple files to only one file via ma

Post by qfito »

Lupp wrote:First of all:
-1- Let us regard the document collecting the data as the "active object". To actually get the documents supplying data to send them would require to organize a process to open them for starting the "sending". The collecting sheet is the "natural place" to run a respective process from.
-2- Dont't think Excel-VBA. The Dir command, however, should work the same way in AOO / LibO Basic.

Did you study viewtopic.php?f=21&t=77069 ?
An apology for putting VBA code but for obvious reasons there are more examples of code written for the windows software that you already know.

I had not seen this information, I'm going to study it. Thank so much for your help.
OpenOffice 6.0.5.2 on Manjaro
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Sending data from multiple files to only one file via ma

Post by Zizi64 »

In some forums they also suggest the documentation of Andrew Pitonyak however I think it would take longer to learn OOobasic / LibObasic to develop the code from scratch.
The API functions are NOT related directly to the StarBasic (OOobasic / LibObasic). You can use them (you can call them) from all of supported programming languages and environments. It is a difference between the API and the VBA.

Yes, the most of Andrew's macro examples was written in the built-in Basic IDE. But there are many samples on this Forum written in Python, Java (script) and more...
(IDE : Integrated Development Environment.)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sending data from multiple files to only one file via ma

Post by MrProgrammer »

Hi, and welcome to the forum.

As a start …
Merge sheets of spreadsheet documents
Merge sheets of one spreadsheet document

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
qfito
Posts: 6
Joined: Tue Aug 07, 2018 5:04 am

Re: Sending data from multiple files to only one file via ma

Post by qfito »

Zizi64 wrote:The API functions are NOT related directly to the StarBasic (OOobasic / LibObasic). You can use them (you can call them) from all of supported programming languages and environments. It is a difference between the API and the VBA.
Thanks, I'll investigate if I can do this more easily using some other language such as javascript or python.

Regards!
OpenOffice 6.0.5.2 on Manjaro
qfito
Posts: 6
Joined: Tue Aug 07, 2018 5:04 am

Re: Sending data from multiple files to only one file via ma

Post by qfito »

MrProgrammer wrote:Hi, and welcome to the forum.

As a start …
Merge sheets of spreadsheet documents
Merge sheets of one spreadsheet document

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
I will review the macros made by Villeroy. I appreciate your help and welcome me to the forum.
OpenOffice 6.0.5.2 on Manjaro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sending data from multiple files to only one file via ma

Post by Villeroy »

qfito wrote:Thanks, I'll investigate if I can do this more easily using some other language such as javascript or python.
The Python version of the important routine is almost identical to the Basic version.
The language does not make any difference in this particular case which consists of nothing but API calls.

Code: Select all

import uno
from com.sun.star.sheet.CellInsertMode import DOWN as INSERT_DOWN
from com.sun.star.sheet.FillDirection import TO_BOTTOM
from com.sun.star.sheet.FillMode.SIMPLE as FILL_SIMPLE

def processFile(sURL, sfile):
	smgr = uno.ComponentContext.ServiceManager
	StarDesktop = smgr.createInstance('com.sun.star.frame.Desktop')
	doc = StarDesktop.loadComponentFromURL(sURL, "_blank", 0, aOptions)
	urg = getUsedRange(doc.Sheets.getByIndex(nIndex))
	src = urg.getRangeAddress()
	src.StartRow = src.StartRow + nSkip
	rg = getRangeByAddress(doc, src)
	a = rg.getDataArray()
	x = a.count() -1
	y = a[0].count() -1
	adr = oTargetRg.getRangeAddress()
	adr.EndColumn = adr.StartColumn + y +1
	adr.StartRow = adr.EndRow +1
	adr.EndRow = adr.StartRow + x
	sh = oTargetRg.getSpreadsheet()
	sh.insertCells(adr, INSERT_DOWN)
	adr.StartColumn = adr.StartColumn +1
	rg = getRangeByAddress(sh, adr)
	rg.setDataArray(a())
	adr.StartColumn = adr.StartColumn -1
	adr.EndColumn = adr.StartColumn
	rg = getRangeByAddress(sh, adr)
	rg.getCellByPosition(0,0).setString(sFile)
	rg.fillSeries(TO_BOTTOM, FILL_SIMPLE,0,0,0)
	doc.close(True)
	oTargetRg = getCurrentRegion(oTargetRg)
with the called helper routines

Code: Select all

def getRangeByAddress(obj,oAddr):
    """Get a sub-range within given doc, sheet or range by given range address.
    If a sheet or range is passed then the sheet of the address is ignored."""
    if obj.supportsService("com.sun.star.sheet.SpreadsheetDocument"):
        oParent = obj.getSheets.getByIndex(oAddr.Sheet)
    else:
        oParent = obj
        
    try:
        return oParent.getCellRangeByPosition(
            oAddr.StartColumn,
            oAddr.StartRow,
            oAddr.EndColumn,
            oAddr.EndRow
            )
    except:
        return None
    
def getCurrentRegion(oRange):
    """Get current region around given range."""
    oCursor = oRange.getSpreadsheet().createCursorByRange(oRange)
    oCursor.collapseToCurrentRegion()
    return oCursor
and some global variables aOptions, nIndex, nSkip which need to be set.

I embedded this code in a customizable template, so it does not require any rewrite. Filll out the cells (path and file name pattern at least) and save the document with its specific settings, so you can repeat the same import with a new set files at any time.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
qfito
Posts: 6
Joined: Tue Aug 07, 2018 5:04 am

Re: Sending data from multiple files to only one file via ma

Post by qfito »

Wow... :shock: I'm very impressed by your expertise. I'm very grateful that you took the time to send this code, which for me is quite complex and sincerely I don't understand it at all, but I'm committed to analyzing what each line does.

I will try it as soon as possible.

Thank you very much!
OpenOffice 6.0.5.2 on Manjaro
Post Reply