Need macro to iterate over rows and selectively copy some

Creating a macro - Writing a Script - Using the API

Need macro to iterate over rows and selectively copy some

Postby j2associates » Wed Sep 06, 2017 2:24 pm

Hello all,

I've written a fair amount of Excel VBA but am very new to Open Office macros. I need this quickly and hope someone can help me. Here is the scenario:

1. I have a data entry sheet which contains approximately 1600 detail lines, 1 record per row
2. I have a fairly complex print sheet which prints records similarly to labels, 2 sets of 5 across per page, left to right and top to bottom
The data for each individual record on the print sheet is spread out over 23 rows and 6 columns
3. The user indicates which data rows they want to print by placing an x in column A of the data entry sheet, usually around 30-50 rows from many non-contiguous areas of the sheet - row 5 and row 1595 are valid possible selections

What I have done is set up a staging area sheet which is the source for the print sheet. The print sheet will be populated by using the offset function to retrieve data from the staging area sheet. I need a macro that does the following:

1. Clear out the existing staging area data, preferable retaining the header row although that is not necessary if it makes the code a lot easier
2. Iterate over the data entry sheet and copy requested data into the next available row in the staging area sheet

Here is some pseudo VBA code if I were doing it in Excel
Code: Select all   Expand viewCollapse view
With StagingAreaSheet
    lEndRow = .Cells.SpecialCells(xlEndData).Row
    lEndCol = .Cells.SpecialCells(xlEndData).Column
    .Range(.Cells(2, "A"), .Cells(lEndRow, lEndCol)).Clear
End With

lNextStagingAreaRow = 2
With DataEntrySheet
    .Activate
    lEndRow = .Cells.SpecialCells(xlEndData).Row
    For lRow = 2 To lEndRow   
        ' So I can watch it while debugging.
        .Activate
        ' Transfer the data to the staging area sheet.
        If LCase$(Trim$(.Cells(lRow, "A").Value)) = "x" Then
            ' Save off data entry values.
            sName = Trim$(.Cells(lRow, "B").Value
            sAddress = Trim$(.Cells(lRow, "C").Value
            ' etc
            With StagingAreaSheet
                ' So I can watch it while debugging.
                .Activate
                ' Transfer data entry values to associated staging area values for use by print sheet.
                .Cells(lNextStagingAreaRow, "D").Value = sName
                .Cells(lNextStagingAreaRow, "B").Value = sAddress
                ' etc
                lNextStagingAreaRow = lNextStagingAreaRow + 1
            End With
        End If
    Next
End With

I have already done the data entry to print sheet conversion for a different application, but in that case every data entry row needed to be printed so there was no need for a macro to transfer the requested data to the staging area. I have the print sheet set up with formulas to handle printing 1000 staging area rows. The staging area indicates how many print sheet pages of data there are so the user will just print pages 1-5, 1-21, etc based on what they have selected.

Bonus question 1 - I don't mind iterating over all of the rows in the detail sheet, but it is slow. In Excel, you could suck all of the values of a Range object into a variant array and process the array which is much faster. Is there equivalent functionality in Open Office?
Code: Select all   Expand viewCollapse view
Dim vRow As Variant
With DataSheet
    vRow = .Range(.Cells(lRow, "A"), .Cells(lRow, lEndCol)).Value
    For lIndex = LBound(vRow) to UBound(vRow)
    Next
End With

Bonus question 2 - In Open Office and in my macro, could I filter on the x in column A and just process the 30-50 user selected rows?

Thanks in advance for any ideas, suggestions or especially code samples :D
Open Office 4.1.3 on Windows 10
j2associates
 
Posts: 4
Joined: Tue Sep 05, 2017 12:24 pm

Re: Need macro to iterate over rows and selectively copy som

Postby MrProgrammer » Wed Sep 06, 2017 3:21 pm

j2associates wrote:1. I have a data entry sheet which contains approximately [N] detail lines, 1 record per row
2. I have a fairly complex print sheet which prints records similarly to labels …
3. The user indicates which data rows they want to print by placing …
This is exactly what Mail Merge does. Read about that in my link or in Help → Index or by reviewing some of the thouansa of topics about it in the forum.

If you want to re-invent Mail Merge yourself, see [Tutorial] Sorting and Filtering data with formulas.

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.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3740
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Need macro to iterate over rows and selectively copy som

Postby j2associates » Wed Sep 06, 2017 8:26 pm

Hello MrProgrammer,

Thanks for the quick response and helpful links. In an effort to make an already lengthy post shorter, I omitted a couple things. In actuality, I have to merge 2 different sheets into my staging area sheet. For purposes of discussion, let's call them Inbound and Outbound. Though formatted similarly, the Inbound and Outbound layouts are not identical, nor are they related in any way. There is no correlation between them as to what is or is not selected in either sheet. The only requirement is that the same number of items be selected in both. The data from the Inbound and Outbound sheets which is merged into the staging area sheet is used in various places on the print sheet (e.g. Inbound is not all on the left, Outbound is not all on the right, etc).

What I planned to do was to run the macro twice, once for the Inbound sheet and once for the Outbound sheet. Once I merged them into the staging area sheet, then everything would be good to go as the Print sheet uses formulas to point back into the staging area sheet for it's data.

Thanks again for your interest in this thread.
Open Office 4.1.3 on Windows 10
j2associates
 
Posts: 4
Joined: Tue Sep 05, 2017 12:24 pm

Re: Need macro to iterate over rows and selectively copy som

Postby Villeroy » Wed Sep 06, 2017 9:51 pm

Where do the data come from?
Text (csv)? Where does the csv come from?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26858
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need macro to iterate over rows and selectively copy som

Postby j2associates » Wed Sep 06, 2017 10:07 pm

1. Data is static
2. Inbound and Outbound sheets were created from around 1000 rows of randomized industry names and addresses for a model railroad club
3. Inbound and Outbound sheet data is formatted similarly but not identically
4. User selects x number of entries from both Inbound and Outbound sheets
a. Selection is totally arbitrary and unrelated to anything except the whim of the user
b. Inbound and Outbound selection counts must be equal
5. Selected Inbound and Outbound rows need to be merged by copying them to a Staging Area sheet
6. The Print sheet makes extensive use of Offset function formulas to populate itself from the Staging Area sheet
7. User prints as many Print sheet pages as they selected, 10 rows per Page, 5 across and 2 down
8. Printed sheets are then cut into 10 individual cards for use by the Model Railroad club
Open Office 4.1.3 on Windows 10
j2associates
 
Posts: 4
Joined: Tue Sep 05, 2017 12:24 pm

Re: Need macro to iterate over rows and selectively copy som

Postby Villeroy » Wed Sep 06, 2017 10:23 pm

This is a typical database job. If you implemented that in MS Excel then use MS Excel. It makes no sense to rewrite the program for every inadequate arithmetic tool.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26858
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need macro to iterate over rows and selectively copy som

Postby j2associates » Wed Sep 06, 2017 11:54 pm

I didn't implement it in Excel, but I have done many similar things in Excel, hence the pseudo code. They are using Open Office because they are trying to keep costs down. I need the macro so that I don't have to be around to allow the merge to take place. I'm trying to work myself out of a job in this case.
Open Office 4.1.3 on Windows 10
j2associates
 
Posts: 4
Joined: Tue Sep 05, 2017 12:24 pm

Re: Need macro to iterate over rows and selectively copy som

Postby Villeroy » Thu Sep 07, 2017 12:22 pm

Good luck.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26858
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: YaCy [Bot] and 1 guest