TRC is primarily funded by ad revenue. If you like the content you find here, please do not block our ads. Thank you.
Results 1 to 10 of 10

Thread: Editing Macros in Spreadsheets

  1. #1
    Should Get Out More Velocette's Avatar
    Join Date
    Jan 2008
    Location
    Bristol
    Posts
    9,125
    Like
    1
    Liked 3 in 2 posts
    Blog Entries
    3

    Default Editing Macros in Spreadsheets

    OK just to be awkward I am using Symphony but the principles must be the same.

    i have created a macro by recording and it half works.

    I have hundreds of lines on a spread sheet with a duplicated Alphanumeric Identifier (eg B29) separated by a space hyphen space that I need to remove the second instance of the identifier.

    e.g.
    Smoke Detector B29 - B29 03E North West
    Smoke Detector B30 - B30 Room
    Smoke Detector B31 - B31 Room
    Smoke Detector B32 - B32 Room
    Smoke Detector B33 - B33 Room
    Smoke Detector B34 - B34 3D North East
    Smoke Detector B35 - B35 Switchroom 1
    Smoke Detector B36 - B36 Room 4D
    Smoke Detector B37 - B37 3D Centre
    Smoke Detector B38 - B38 3E West
    Smoke Detector B39 - B39 3E South West
    Smoke Detector B40 - B40 3E South
    Smoke Detector B41 - B41 Room
    Smoke Detector B42 - B42 Room
    If I run the macro as recorded on the first cell and then run it on the second cell it removes the second instance of the number all fine and dandy but it replaces all the text in the cell including the number i want to keep with the contents of the first cell.

    One I tried earlier, same thing:

    REM ***** BASIC *****

    Sub Main

    End Sub



    sub removeserial
    rem ----------------------------------------------------------------------
    rem define variables
    dim document as object
    dim dispatcher as object
    rem ----------------------------------------------------------------------
    rem get access to the document
    document = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelpe r")

    rem ----------------------------------------------------------------------
    dim args1(0) as new com.sun.star.beans.PropertyValue
    args1(0).Name = "StringName"
    args1(0).Value = "Smoke Detector D120 - Detector South "

    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())

    rem ----------------------------------------------------------------------
    dim args2(0) as new com.sun.star.beans.PropertyValue
    args2(0).Name = "StringName"
    args2(0).Value = "Smoke Detector D121 - Detector North "

    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())

    rem ----------------------------------------------------------------------
    dim args3(0) as new com.sun.star.beans.PropertyValue
    args3(0).Name = "StringName"
    args3(0).Value = "Smoke Detector D122 - Detector South "

    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())

    rem ----------------------------------------------------------------------
    dim args4(0) as new com.sun.star.beans.PropertyValue
    args4(0).Name = "StringName"
    args4(0).Value = "Smoke Detector D123 - Detector North "

    dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())


    end sub

    Any help apreciated
    Register to join this UK Motorbike Forum, start a motorbike blog, or use our free motorbike classifieds!


  2. TRC is primarily funded by ad revenue. If you like the content you find here, please do not block our ads. Thank you.
  3. #2
    Too orangey for crows Olly's Avatar
    Join Date
    Jul 2009
    Location
    Darlington
    Posts
    14,650
    Like
    21
    Liked 21 in 16 posts

    Default Re: Editing Macros in Spreadsheets

    What's wrong with a simple formula? This is Excel, but other spreadsheets will work similarly:

    Code:
    =LEFT(A1,FIND("-",A1)-2)&RIGHT(A1,LEN(A1)-FIND("-",A1)-4)
    Register to join this UK Motorbike Forum, start a motorbike blog, or use our free motorbike classifieds!

  4. #3
    Should Get Out More Mussels's Avatar
    Join Date
    Jan 2008
    Location
    Not in London
    Posts
    13,500
    Like
    18
    Liked 6 in 5 posts

    Default Re: Editing Macros in Spreadsheets

    Quote Originally Posted by Velocette View Post
    OK just to be awkward I am using Symphony but the principles must be the same.
    The principles probably are and what Olly has put is perfect as long as there's never a hyphen in the stock item's name and the alphanumeric identifier is always three characters.
    As Symphony is by IBM/Lotus then don't expect it to be similar to anything else or to have even half of the functionality you want.

  5. #4
    Too orangey for crows Olly's Avatar
    Join Date
    Jul 2009
    Location
    Darlington
    Posts
    14,650
    Like
    21
    Liked 21 in 16 posts

    Default Re: Editing Macros in Spreadsheets

    Quote Originally Posted by Mussels View Post
    The principles probably are and what Olly has put is perfect as long as there's never a hyphen in the stock item's name and the alphanumeric identifier is always three characters.
    As Symphony is by IBM/Lotus then don't expect it to be similar to anything else or to have even half of the functionality you want.
    Yeah - here's a formula which works with any length of duplicated identifier name:

    Code:
    =LEFT(LEFT(A1,FIND("-",A1)-2),LEN(LEFT(A1,FIND("-",A1)-2))-FIND(" ",MID(A1,FIND("-",A1)+2,999)))&" - "&MID(A1,FIND("-",A1)+2,999)
    Register to join this UK Motorbike Forum, start a motorbike blog, or use our free motorbike classifieds!

  6. #5
    Should Get Out More Velocette's Avatar
    Join Date
    Jan 2008
    Location
    Bristol
    Posts
    9,125
    Like
    1
    Liked 3 in 2 posts
    Blog Entries
    3

    Default Re: Editing Macros in Spreadsheets

    Quote Originally Posted by Olly View Post
    Yeah - here's a formula which works with any length of duplicated identifier name:

    Code:
    =LEFT(LEFT(A1,FIND("-",A1)-2),LEN(LEFT(A1,FIND("-",A1)-2))-FIND(" ",MID(A1,FIND("-",A1)+2,999)))&" - "&MID(A1,FIND("-",A1)+2,999)

    Thanks, I will have a go after Lunch. I have only just started using Formulae and I can use LEFT, MID, RIGHT, PROPER, CONCATENATE etc but I have not managed to string them together as yet as I don't know the syntax to use.
    Register to join this UK Motorbike Forum, start a motorbike blog, or use our free motorbike classifieds!

  7. #6
    depilating peaches inewham's Avatar
    Join Date
    Jan 2012
    Location
    Nottingham / Derby
    Posts
    996
    Like
    3
    Liked 0 in 0 posts

    Default Re: Editing Macros in Spreadsheets

    Being lazy... i'd probably just save it as a csv, open it up in vim, substitue out the dupes, save & reopen faster than working out the macro leaving time to go make a brew and do something more interesting.

  8. #7
    Too orangey for crows Olly's Avatar
    Join Date
    Jul 2009
    Location
    Darlington
    Posts
    14,650
    Like
    21
    Liked 21 in 16 posts

    Default Re: Editing Macros in Spreadsheets

    This formula puts the hyphen after the identifier name, rather than before:

    Code:
    =LEFT(A1,FIND("-",A1)+1)&RIGHT(A1,LEN(RIGHT(A1,LEN(A1)-FIND("-",A1)-1))-FIND(" ",RIGHT(A1,LEN(A1)-FIND("-",A1)-1)))


    Ping me if you want help with it
    Register to join this UK Motorbike Forum, start a motorbike blog, or use our free motorbike classifieds!

  9. #8
    Should Get Out More 1888's Avatar
    Join Date
    Mar 2010
    Location
    East Lothian
    Posts
    20,166
    Like
    6
    Liked 19 in 16 posts

    Default Re: Editing Macros in Spreadsheets

    Must be Dereks day off today.

  10. #9
    Should Get Out More Velocette's Avatar
    Join Date
    Jan 2008
    Location
    Bristol
    Posts
    9,125
    Like
    1
    Liked 3 in 2 posts
    Blog Entries
    3

    Default Re: Editing Macros in Spreadsheets

    Quote Originally Posted by Mussels View Post
    The principles probably are and what Olly has put is perfect as long as there's never a hyphen in the stock item's name and the alphanumeric identifier is always three characters.
    As Symphony is by IBM/Lotus then don't expect it to be similar to anything else or to have even half of the functionality you want.
    Luckily that hyphen only appears between the device and the description but take your point. A lot of the differences are subtle and just for the sake of it such as commas in Excel formulae have to be semi colons in Symphony. Control and F with Regular expressions is more powerful in Symphony but also too complex to be bothered with! So I just use .?* etc.

    To use Excel at work I have to start my KVM Windows environment and if I am doing stuff like email etc on Linux it tends to make both slow and unstable. Also I have recently lost the sharing capability between the VM and Linux so have to use Google Docs to transfer stuff!
    Register to join this UK Motorbike Forum, start a motorbike blog, or use our free motorbike classifieds!

  11. #10
    Should Get Out More Velocette's Avatar
    Join Date
    Jan 2008
    Location
    Bristol
    Posts
    9,125
    Like
    1
    Liked 3 in 2 posts
    Blog Entries
    3

    Default Re: Editing Macros in Spreadsheets

    Quote Originally Posted by Olly View Post
    This formula puts the hyphen after the identifier name, rather than before:

    Code:
    =LEFT(A1,FIND("-",A1)+1)&RIGHT(A1,LEN(RIGHT(A1,LEN(A1)-FIND("-",A1)-1))-FIND(" ",RIGHT(A1,LEN(A1)-FIND("-",A1)-1)))


    Ping me if you want help with it
    Brilliant. you are owed many pints!
    Register to join this UK Motorbike Forum, start a motorbike blog, or use our free motorbike classifieds!

Go Back to Forum My Forum