[Solved] Delete Sheets Not In a List

Creating a macro - Writing a Script - Using the API

[Solved] Delete Sheets Not In a List

Postby stickman68 » Mon Feb 19, 2018 10:59 am

I've been playing with macro code for a day trying to figure out how to translate this bit of VBA code into an OO Basic macro without notable success. I know there has to be an easy way to do this but I'm not finding it...
VBA code:
Code: Select all   Expand viewCollapse view
sub VBA_Original
   For i = Sheets.Count To 1 Step -1
        X = Application.Match(Sheets(i).Name, Worksheets("source info").Range("I2:I17"), 0)
        If IsError(X) Then
            Application.DisplayAlerts = False
            Sheets(i).Delete
            Application.DisplayAlerts = True
        End If
   Next
end sub

Any pointers would be appreciated.

Thanks!
Last edited by stickman68 on Mon Feb 19, 2018 10:50 pm, edited 1 time in total.
LibreOffice 6.0.1 on Windows 10/ LibreOffice 6.0.1 with MacOS 10.13 / LibreOffice 6.0.1 on Linux Mint 18
User avatar
stickman68
 
Posts: 4
Joined: Mon Feb 19, 2018 1:13 am
Location: NY, NY

Re: Delete Sheets Not In a List

Postby Villeroy » Mon Feb 19, 2018 2:13 pm

"Playing for a day" is a total waste of time. You could also let your cat walk over the keyboard until she wrote a poem by mere chance.
We are not the humanoid macro recorders for VBA snippets

3 options:
- learn how to program
- do your stuff manually
- stick with Excel
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25764
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete Sheets Not In a List

Postby stickman68 » Mon Feb 19, 2018 4:33 pm

Thanks for all the excellent help with your solution. I don't know how I didn't see it myself.
If you had bothered to look at my other post you would see that I can write a macro in OO Basic. I'm just missing something here.
LibreOffice 6.0.1 on Windows 10/ LibreOffice 6.0.1 with MacOS 10.13 / LibreOffice 6.0.1 on Linux Mint 18
User avatar
stickman68
 
Posts: 4
Joined: Mon Feb 19, 2018 1:13 am
Location: NY, NY

Re: Delete Sheets Not In a List

Postby Villeroy » Mon Feb 19, 2018 9:24 pm

Code: Select all   Expand viewCollapse view
sx = ThisComponent.getSheets()
a() = sx.getElementNames() 'simple array of names
rg = sx.getByName("source info").getCellRangeByName("I2:I17")
b() = rg.getDataArray() 'nested array of row values
uba = uBound(a())
ubb = uBound(b())
for i = 0 to uba
  for j = 0 to ubb
    if a(i) = b(j)(0) then exit for
  next j
  if j > ubb then sx.removeByName(a(i))
next i

The tricky part is that the DataArray b() is a nested array of the rows in one column. When you inspect the variable, you see that each element in b is another array with one element (because there is only one column in each row) and b(j)(0) addresses the first element of element b(j) which is the cell value or string.
When the loop runs until the last element without calling the "exit for" clause, j is by one greater than the uBound of array b() which means that the name a(i) was not found in b() in which case we remove the element named a(i) from the sheets collection sx.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25764
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete Sheets Not In a List

Postby stickman68 » Mon Feb 19, 2018 10:42 pm

:bravo: Wow! I would have never seen the two array solution. I was hung up on finding a replacement for the match function.
Thanks! :super:

Any idea why it exits the sub when it hits the objSheets.removeByName(aNames(i))

Code: Select all   Expand viewCollapse view
    While numSheets > numRows
   
      for i = 0 to ubaNames
         for j = 0 to ubaRows
            if aNames(i) = aRows(j)(0) then
               exit for
            end if
          next j
         if j > ubaRows then
            print aNames(i)
            objSheets.removeByName(aNames(i))
         end if
      next i
       numSheets = objSheets.Count
    wend
LibreOffice 6.0.1 on Windows 10/ LibreOffice 6.0.1 with MacOS 10.13 / LibreOffice 6.0.1 on Linux Mint 18
User avatar
stickman68
 
Posts: 4
Joined: Mon Feb 19, 2018 1:13 am
Location: NY, NY

Re: [SOLVED] - Delete Sheets Not In a List

Postby Villeroy » Tue Feb 20, 2018 3:36 pm

stickman68 wrote:Any idea why it exits the sub when it hits the objSheets.removeByName(aNames(i))

Only you can find out. The Basic debugging tools are the same as in VBA. I guess it is because NOT(numSheets > numRows)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25764
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests