Macro to copy range from workbook 1 & append in workbook 2

Creating a macro - Writing a Script - Using the API
Post Reply
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Macro to copy range from workbook 1 & append in workbook 2

Post by MBT »

I am new to macros in OpenOffice. I am having trouble figuring out what is wrong with my code. It comes up with a basic syntax error looking for a comma. Can anyone help me figure out what is wrong with my below code?

Code: Select all

Option Explicit
	Sub Copy_Paste_Below_Last_Cell()
	Dim wsCopy As Worksheet
	Dim wsDest As Worksheet
	Dim lCopyLastRow As Long
	Dim lDestLastRow As Long
	Set wsCopy = Workbooks("PullPart.xls").ThisComponent.Sheets.getByName()("Export")
	Set wsDest = Workbooks("Invent.xls").ThisComponent.Sheets.getByName()("Data")
	lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
	lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
	wsCopy.Dim oSheet as Object[n]oSheet = ThisComponenet.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)("A2:F" & lCopyLastRow).Copy
	wsDest.ThisComponent.CurrentController.ThisComponenet.CurrentController.ActiveSheet.getCellDim oSheet as Object[n]oSheet = ThisComponenet.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)ByName(("A" & lDestLastRow).PasteSpecial Paste:=xlPast).Values
	wsDest.Activate
	End Sub
OpenOffice 4.1.1 on Windows 7 and 10
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

I realized my code above may not be completely visible due to screen with limitations. Also, I realize what these lines of code are doing might not be obvious. So I have pasted below code that should fit on the whole screen and includes comments on what the programming should do at each step.

Code: Select all

Option Explicit
	Sub Copy_Paste_Below_Last_Cell()
	'Find the last used row in both sheets and copy and paste data below existing data.
	Dim wsCopy As Worksheet
	Dim wsDest As Worksheet
	Dim LCopyLastRow As Long
	Dim LDestLastRow As Long
	'Set variables for copy and destination sheets
	'The data to be exported will be copied from the PullPart file, Export worksheet
	Set wsCopy = Workbooks("PullPart.ods").ThisComponent.Sheets.getByName()("Export")
	'The data to be imported will be copied to the Invent file, Data worksheet
	Set wsDest = Workbooks("Invent.xls").ThisComponent.Sheets.getByName()("Data")
	'1. Find last used row in the copy range based on data in column B
	LCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xLUp).Row
	'2. Find first blank row in the destination range based on data in column B
	'Offset property moves down 1 row  
	LDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xLUp).Offset(1).Row
	'3. Copy & Paste Data
	wsCopy.Dim oSheet as Object[n]oSheet = ThisComponenet.CurrentController.ActiveShee
	t[n]oSheet.getCellRangeByName($1)("A2:F" & LCopyLastRow).Copy
	wsDest.ThisComponent.CurrentController.ThisComponenet.CurrentController.ActiveShe
	et.getCellDim oSheet as Object[n]oSheet = ThisComponenet.CurrentController.ActiveShee
	t[n]oSheet.getCellRangeByName($1)ByName(("A" & LDestLastRow).PasteSpecial Paste:=xLPa
	st).Values
	'Optional - Select the destination sheet
	wsDest.Activate
	End Sub
OpenOffice 4.1.1 on Windows 7 and 10
User avatar
robleyd
Moderator
Posts: 4424
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macro to copy range from workbook 1 & append in workbook

Post by robleyd »

You have overlooked telling where the syntax error occurs and what it is; the text of the actual error message would be useful.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 15 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 15 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

The error occurs at #3, copy & paste data. It then highlights oSheet right after the wsCopy.Dim words and the error that comes up is "BASIC syntax error. Expected: ,."
OpenOffice 4.1.1 on Windows 7 and 10
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

What I am trying to do is tell the macro to:
(1) go to a named worksheet ("Export") in a named workbook ("PullPart.ods") then
(2) copy a range of cells (A2 to the last filled cell in the bottom-right) then
(3) go to a named worksheet ("Data") in a DIFFERENT workbook ("Invent.xls") then
(4) append the copied data below the last used row (changes each time macro is run)
OpenOffice 4.1.1 on Windows 7 and 10
User avatar
robleyd
Moderator
Posts: 4424
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macro to copy range from workbook 1 & append in workbook

Post by robleyd »

You seem to have DIM embedded in a place where I wouldn't expect a DIM?
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 15 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 15 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
Villeroy
Volunteer
Posts: 30799
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to copy range from workbook 1 & append in workbook

Post by Villeroy »

If you want to manipulate xls files with VBA, then Excel is the one and only solution.
If you want to copy cells from one sheet to another, a couple of keystrokes do the job with any spreadsheet application.

We are no human macro recorders nor VBA translators.
A macro to be configured with named cells: viewtopic.php?f=21&t=77069
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
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

Villeroy, I appreciate the Excel sales pitch (it is my FAVORITE program), but I am REQUIRED to use OO. Also, the files could either both be in OO or in XLS & OO, either combination. As a highly advanced Excel user, I know how to use "keystrokes". What I need is to know how to get the macro to work, not unkind commentary (as I've noted you've given to many others posting here). If you are not going to provide the solution, and only going to tell people to "just use a different program" or "use manual keystrokes" (which most people already know), please do not post a comment. Most of us work in a professional environment and expect professional responses. Thanks.
OpenOffice 4.1.1 on Windows 7 and 10
User avatar
Villeroy
Volunteer
Posts: 30799
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to copy range from workbook 1 & append in workbook

Post by Villeroy »

Take some 2 or 3 weeks of learning. This is by far more complex than MS Excel.
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
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

Again, you are not being helpful. Please supply the corrected macro code, if you are capable of figuring it out. I dare you! Lol
OpenOffice 4.1.1 on Windows 7 and 10
User avatar
robleyd
Moderator
Posts: 4424
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macro to copy range from workbook 1 & append in workbook

Post by robleyd »

Code: Select all

wsCopy.Dim oSheet as Object[n]oSheet = ThisComponenet.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)
Why is Dim used in this way? My experience with these types of Basic has been that variables are properly declared at the very beginning of a sub or function, not randomly in the middle of a statement.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 15 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 15 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

I am converting an Excel macro. But that doesn't matter so much. It is the task I need to do that matters. If someone has a way to do the four steps I outlined, even if it is completely different from what I have, and it works, it would be much appreciated.
OpenOffice 4.1.1 on Windows 7 and 10
User avatar
robleyd
Moderator
Posts: 4424
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Macro to copy range from workbook 1 & append in workbook

Post by robleyd »

Sorry, I was trying to help with your question about "a basic syntax error"; I can't help with writing a macro as specified however.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 15 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 15 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
Villeroy
Volunteer
Posts: 30799
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to copy range from workbook 1 & append in workbook

Post by Villeroy »

I provided a complete solution which can be adjusted easily.
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
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

Still looking for the solution to what I was asking for above if anyone can help.
OpenOffice 4.1.1 on Windows 7 and 10
User avatar
Zizi64
Volunteer
Posts: 10667
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to copy range from workbook 1 & append in workbook

Post by Zizi64 »

Your sample code is a mixed VBA - AOO/LO API code list. You are on an absolutely wrong way!

If you want to code in VBA, then use Excel. If you want use AOO/LO, then use one of available (suported) programming language (for example the built-in StarBasic and its IDE) and the API functions.
API: Application Programming Interface.

It is not enough to "modify a littlebit" or "translate" your code. You must totally rewrite it based on the AOO/LO API!

Please read Andrew Pitonyak's excellent macro programming books.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.3.5;AOO4.1.13
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
Villeroy
Volunteer
Posts: 30799
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to copy range from workbook 1 & append in workbook

Post by Villeroy »

I would prefer if those "Excel experts" would be chained to their product forever. wsCopy.Cells(wsCopy.Rows.Count, "B").End(xLUp).Offset(1).Row tells everything about their expertise.
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
User avatar
Lupp
Volunteer
Posts: 3249
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro to copy range from workbook 1 & append in workbook

Post by Lupp »

I would like to be helpful. It may be difficult, however, in this case, and surely it won't work out in the sense of my general motivation to contribute to a forum like this one.
First of all: Neither Apache (now very little active concerning OpenOffice) nor TheDocumentFoundation maintaining and developing the more vital LibreOffice branch try to lock-in their users. Therefore they don't force them to use a specific (rather powerful but ugly, badly structured, "proprietary") programming language if it comes to any kind of document automation.
As any software working on complex models should do, AOO/LibO provide access to the objects and specialized tools/methods... via an API which basically can be used with code written in different "languages" - if somebody constructed the needed "bridge". The Basic coming with AOO/LibO isn't the API itself, but a kind of simple but usable reference languge providing a short and wide bridge to the API, but very little additional power.
MBT wrote:I am new to macros in OpenOffice.
Welcome!
MBT wrote:I am having trouble figuring out what is wrong with my code.
Of course. You should expect everything wrong for the reasons I tried to explain in advance.
Excel-VBA is a programming language and a kind of API for Excel documents at the same time.
AOO/LibO Basic isn't similar insofar.

I don't know in what ways you can work with AOO/LibO documents while running VBA code started from an MS document, but if you have an open AOO/LibO document (say a spreadsheet model) you can open in addition a document from .xls or .xlsx. As soon as you did so, it is no longer Excel, but a model in RAM to which you have access via the API in exactly the same way as if it was loaded from .ods .

Trying to read your code, I cannot even see clearly with what you started. Was it Excel and VBA? If so I cannot help you at all. Was it a document opened by AOO or LibO? Did you open such a document in a specific way from a program written in an "alien" language? What is the object (variable) representing that model then?

You see: No starting point for specific help so far.
On Windows 10: LibreOffice 7.2 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

I am new both to OO and writing macros, but I've made some progress, which I will share below. I've noticed many threads here where requests for help only receive commentary but no actual, coded solution spelled out or explained (and sometimes the inquirer is made to feel stupid). Then one must wade through piles of such chatter to find a small tidbit of a solution here or there. So ... if anyone else could benefit from the macro I've come up with so far (or some of the lines of code within it), hopefully pasting it below will save them more time than I have had to spend so far. I've tried to comment on each line of code so other beginners can tell what each line is doing for projects they may be working on. Hope this helps everyone. When/If I come up with the rest of a solution for my question in this thread, I will add it here so everyone can benefit. In the meantime, if anyone has relevant solutions to offer (either to make the code below better or to hlep me solve the rest of my issue), it would be most appreciated. Cheers!
Working macro to save just one, specified sheet; but needs output to be saved independently and leave the template file open:

Code: Select all

REM  *****  BASIC  *****
	REM  I have put REM at the beginning of each blank line so each group of codes remains obvious (should help deciphering wordwrap also)
	REM
	Sub SavePull  'SavePull is the name I gave to this macro
	REM
	'The DIM lines below set up which user-defined variables will be used in the macro, like saying "I'm going to use X as a character string" or "sSheet as an object/place" (you can make up your own variable names)
	Dim sPath As String
	Dim sFileName As String
	Dim sSheets As Object
	Dim sSheet As Object
	Dim sDocument As Object
	Dim sSaveToURL As String
	Dim Propval(1) As New com.sun.star.beans.PropertyValue
	Dim document As Object
	Dim dispatcher As Object
	REM
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	dispatcher.executeDispatch(document, ".uno:Calculate", "", 0, Array())  'Refreshes the workbook before saving (ensures updated calculations)
	REM
	'The below lines prepare the sheet to be saved as a .CSV file
	Propval(0).Name = "FilterName"
	Propval(0).Value = "Text - txt - csv (StarCalc)"
	Propval(1).Name = "FilterOptions"
	Propval(1).Value ="44,34,0,1,1"   'ASCII  44 = ,  34 = " (selects the save options to be used as comma-delimited with text in quotes)
	REM
	'The next set of lines below is where you tell the macro what X equals, or in this case sPath, sFileName, etc.
	sPath = "file:///C:/Users/labs/Documents/Pulls-Current/"  'Assigns the path to be used for saving the .CSV file
	sFileName = thisComponent.getSheets.getByName("Update DMI").getCellRangeByName("H1").getString  'Uses the string in a named sheet (Update DMI) and cell (H1) as the filename (a calculated name that changes)
	sSaveToURL = ConvertToURL(sPath & sFileName)  'Combines the path and filename as a single string
	sFileURL = convertToURL(sSaveToURL)  'Converts the Path/Filename to a URL
	sDocument = ThisComponent  'Assigns the current document to the variable document
	sSheets = sDocument.Sheets  'Gets the container of all Sheets
	sSheet = sSheets.getByName("Update DMI")  'Assigns the sheet named "Update DMI"
	REM
	sDocument.CurrentController.setActiveSheet(sSheet)  'Sets "Update DMI" as the active sheet (so it will be the single sheet exported as a .CSV file)
	REM
	If sFileName = "" then Goto H1Blank  'If cell H1 is blank (which causes an error in the next line), jumps to the H1Blank label, returns the error message, and exits the macro
	REM
	sDocument.StoreAsURL(sFileURL, Propval())  'Saves the named sheet as a .CSV file under the defined path/filename (NOTE: since the sheet was saved as a .CSV file, the open workbook reflects that name, but the original file remains changed)
	REM
	If sFileName > "" then Goto sExit  'If cell H1 was NOT blank, skips the error message below and finishes the macro
	REM
	H1Blank:  'this line is known as a label, which you can use to tell a macro to jump to conditionally
	REM
	Const sText1 = "You must enter:" 'what you want the first line of the message to be
	Const sText2 = "       1) at least one PART number; and,"  'The second line of the message
	Const sText3 = "       2) the NAME of the person pulling the part/s."  'The third line of the message (you can add/subtract line this way)
	MsgBox(sText1 & Chr(13) & sText2 & Chr(13) & sText3)  'Formats the error message on three lines instead of a single, wrapped message
	REM
	sExit:  'Also a label line
	REM
	End Sub  'This is where the macro actually ends
	REM
	Function MakePropertyValue(Optional sName As String, Optional sValue) As com.sun.star.beans.PropertyValue
	'----------------------------------------------------------------------------------------
	' Create and return a new com.sun.star.beans.PropertyValue (used to facilitate saving in .CSV format; the macro jumps to the part mid-subroutine then continues above)
	'----------------------------------------------------------------------------------------
	REM
	Dim oPropertyValue As New com.sun.star.beans.PropertyValue
	REM
	If Not IsMissing(sName) Then
	oPropertyValue.Name = sName
	EndIf
	REM
	If Not IsMissing(sValue) Then
	oPropertyValue.Value = sValue
	EndIf
	REM
	MakePropertyValue() = oPropertyValue
	REM
	End Function
	REM  This is the end of the text used in the macro
Last edited by robleyd on Thu Feb 18, 2021 1:46 am, edited 1 time in total.
Reason: Added Code tags for improved readability
OpenOffice 4.1.1 on Windows 7 and 10
MBT
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

Re: Macro to copy range from workbook 1 & append in workbook

Post by MBT »

By the way, ignore the text right after the word cheers and before the code begins. That was just a note to myself for what else I may want the macro to do.
OpenOffice 4.1.1 on Windows 7 and 10
Post Reply