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

Creating a macro - Writing a Script - Using the API

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

Postby MBT » Thu Feb 11, 2021 9:25 pm

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   Expand viewCollapse view
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

Postby MBT » Thu Feb 11, 2021 10:41 pm

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   Expand viewCollapse view
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
MBT
 
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

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

Postby robleyd » Fri Feb 12, 2021 12:05 am

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 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3950
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Postby MBT » Fri Feb 12, 2021 12:28 am

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

Postby MBT » Fri Feb 12, 2021 1:00 am

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
MBT
 
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

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

Postby robleyd » Fri Feb 12, 2021 1:29 am

You seem to have DIM embedded in a place where I wouldn't expect a DIM?
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3950
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Postby Villeroy » Fri Feb 12, 2021 1:37 am

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
User avatar
Villeroy
Volunteer
 
Posts: 29697
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MBT » Fri Feb 12, 2021 2:11 am

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
MBT
 
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

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

Postby Villeroy » Fri Feb 12, 2021 3:01 am

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
User avatar
Villeroy
Volunteer
 
Posts: 29697
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MBT » Fri Feb 12, 2021 3:07 am

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
MBT
 
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

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

Postby robleyd » Fri Feb 12, 2021 4:48 am

Code: Select all   Expand viewCollapse view
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 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3950
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Postby MBT » Fri Feb 12, 2021 5:25 am

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
MBT
 
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

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

Postby robleyd » Fri Feb 12, 2021 5:51 am

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 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3950
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Postby Villeroy » Fri Feb 12, 2021 8:49 am

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
User avatar
Villeroy
Volunteer
 
Posts: 29697
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MBT » Sat Feb 13, 2021 8:38 am

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
MBT
 
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm

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

Postby Zizi64 » Sat Feb 13, 2021 8:58 am

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.1.1;AOO4.1.9
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
Zizi64
Volunteer
 
Posts: 9838
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Villeroy » Sat Feb 13, 2021 12:13 pm

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
Villeroy
Volunteer
 
Posts: 29697
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Sat Feb 13, 2021 6:16 pm

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.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3055
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby MBT » Thu Feb 18, 2021 12:10 am

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   Expand viewCollapse view
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

Postby MBT » Thu Feb 18, 2021 12:14 am

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
MBT
 
Posts: 17
Joined: Thu Feb 11, 2021 9:19 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests