Excel and Quantity Link Script

  • Excel and Quantity Link Script

    Posted by Dan Diesing on September 19, 2023 at 10:49 am

    Hi All,

    I heard you guys might be able to help me or at least steer me in the right direction. I’ve never really looked into Excel scripting. I have been using Bluebeam Revu for about 6 months (career change due to injury), and I do mostly finish carpentry takeoffs with it. The small company I work for was old school before I got them on Bluebeam. The most time-consuming part of my estimating is using Quantity link. Having to right click and choose Count or length(main 2 i use) then subject and layer over and over again. Some projects are quite large and have 15+ Different floors or Buildings with 15 Parameters I track (ie. Different doors, different trims ect.). Once I have it into excel it makes everything very nice and fast.

    Is there a way to use VBA or a Macro to make quantity link faster?

    I use Subject (each Item, such as Baseboard), and layer (Each floor or Building) mostly in the quantity link.

    Subjects: Suite Entry Door, Exterior door, interior swing, Baseboard ect.

    Layers: L1, L2, L3 or B1, B2, B3

    So my Excel sheet,If there is a way I could have a macro(or VBA) that would take the Header of the table as the subject, and the First row of the table as the Layer to easily input them into the table. It would also have to dictate what type of measurement it is (Such as Count, Area, Length)

    If anyone could point me in the right direction that would be great!

    Thanks

    Vince French replied 5 months, 2 weeks ago 7 Members · 32 Replies
  • 32 Replies
  • Vince French

    Member
    September 19, 2023 at 1:05 pm
    Points: 11,999
    Rank: UC2 Brainery Blue Belt I UC2 Brainery Blue Belt I

    It appears like you are trying to use more than just the summary measure and subject in Excel. In this case the best thing to do is to not use Quantity Link and instead export your entire Markup List to a csv file and then import that into your Excel spreadsheet. This way you can use all of the data.

    In Excel, set yourself up a Table for pasting the information from the CSV file and then have all of your other sheets set up ready to use this information. I’ve posted on here about the different levels of information you can get from the exact same Markups – this also has a section on how using Quantity Link can have issues.

    Once you get into Excel, you can use a feature called Power Query to automate some of the process.

  • David Spergel

    Member
    September 19, 2023 at 1:12 pm
    Points: 645
    Rank: UC2 Brainery White Belt I UC2 Brainery White Belt I

    I don’t think the Quantity Link feature has been meaningfully touched since it was implemented back in 2018, so I think Vince is correct; if you need to do anything more advanced than linking simple values your best bet will be to export the whole .csv so you have all the data to work with.

    Power Automate Desktop might be useful here

    • Vince French

      Member
      September 19, 2023 at 1:29 pm
      Points: 11,999
      Rank: UC2 Brainery Blue Belt I UC2 Brainery Blue Belt I

      I’ve never used Power Automate. If you use Power Query, then this can pull in the information from a CSV file which means if you export from Revu you don’t even have to open the CSV file to get everything to work.

      Power Query will also let you do various manipulations, etc. with the info from the Markup List.

      • David Spergel

        Member
        September 19, 2023 at 2:12 pm
        Points: 645
        Rank: UC2 Brainery White Belt I UC2 Brainery White Belt I

        Power Automate is a really cool tool that lets you automate/script a lot of windows tasks; in this case I was thinking it could be used to automate the data import into Excel, at which point Power Query becomes extremely powerful as you suggested 🙂

        • Vince French

          Member
          September 19, 2023 at 2:28 pm
          Points: 11,999
          Rank: UC2 Brainery Blue Belt I UC2 Brainery Blue Belt I

          You really don’t need the Power Automate part.

          If you update something in Revu then just export the markup list to the csv file.

          Then with Power Query you can set it up to find the csv file so, all you have to do is press refresh and you’re done.

          So this is all done in a couple of seconds – literally.

          • David Spergel

            Member
            September 19, 2023 at 2:31 pm
            Points: 645
            Rank: UC2 Brainery White Belt I UC2 Brainery White Belt I

            Yeah I took another look at the workflow and you’re right, power automate is unnecessary 😅 FWIW I just discovered it so now I’m guilty of trying to shoehorn it into every solution to any problem LOL

            • Vince French

              Member
              September 19, 2023 at 2:40 pm
              Points: 11,999
              Rank: UC2 Brainery Blue Belt I UC2 Brainery Blue Belt I

              I think we’re all guilty of doing things like that! 🤣

        • Doug McLean

          Member
          September 19, 2023 at 3:17 pm
          Points: 12,674
          Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

          There isn’t a PA connector to Revu

          • David Spergel

            Member
            September 19, 2023 at 3:33 pm
            Points: 645
            Rank: UC2 Brainery White Belt I UC2 Brainery White Belt I

            Yeah I was thinking they could execute PA on the resulting .csv but it’s unnecessary

    • Doug McLean

      Member
      September 19, 2023 at 3:27 pm
      Points: 12,674
      Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

      Power Automate will be no use, as there is no connector.
      I’ve made several attempts at trying to extract the data directly from a PDF rather than exporting a Markup Summary, but I can’t find a way to do it.

      For a consistent data export, your best bet is to build a reusable BatchConfig file. This way, you’ll always get the columns you need in a consistent layout. It will be named the same, everything will be consistent. The only thing you’ll have to change is the save location.

      Once that’s done, Power Query is the way to go to get the outputs that you need.

  • David Cutler

    Member
    September 19, 2023 at 2:32 pm
    Points: 23,447
    Rank: UC2 Brainery Brown Belt III UC2 Brainery Brown Belt III

    Welcome to the Brainery @fc-estimation

    Sounds as if you have learned a good amount about Revu in your first 6 months! @vince and @doug mclean have built some amazing tools in Revu and workflows that use Power Query to process the information that they extract from their markups. That being said understand that they have been refining their tools and their workflows for years to get them to the level that they are at today.

    My tools are not as advanced as their tools are, and I’m just taking baby steps into using Power Query. My tools get what I need done though and that is the important part!

    We usually recommend people start with the end in mind – ask yourself “how am I going to use the data after I’m done capturing the information in Revu?” Some complete their entire estimates in Excel. Some transpose the information onto a piece of paper and re-key it into an estimating software (my boss for example). I export my markups as a .CSV, reduce the information in Excel (using a simple Power Query) and upload the “clean” data into our estimating software, B2W.

    The key point is to find what works for you, rough it out and then refine it over time. What works for you today (Quantity Link) may not be what you use in the future.

    From what I’ve found Quantity Link doesn’t provide the flexibility that I need with my data. I export my markups to a .CSV file an work with the data in Excel from there as others have suggested. If you haven’t already done so find a few video’s on how to use some of the simple data tools in Excel as a start and then learn about Power Query in time. Before you know it you will be teaching us what you have learned!

    Also make some time to watch some of the videos that our host, @troy_degroot has posted. That’s what really got me started!

    https://uchapter2.com/bluebeam-training-videos/

    • Doug McLean

      Member
      September 19, 2023 at 3:20 pm
      Points: 12,674
      Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

      finally diving in eh David?

      • David Cutler

        Member
        September 19, 2023 at 4:32 pm
        Points: 23,447
        Rank: UC2 Brainery Brown Belt III UC2 Brainery Brown Belt III

        I think I’d classify it as wading in @Doug McLean rather than diving. I’m getting comfortable setting up the queries, but I haven’t gotten deep enough in to figure out how re-use them…

        • Doug McLean

          Member
          September 20, 2023 at 3:52 pm
          Points: 12,674
          Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

          reusing the query is easy David.
          There are a few ways to do it actually.

          One I talk about in my presentation

          • David Cutler

            Member
            September 21, 2023 at 1:07 pm
            Points: 23,447
            Rank: UC2 Brainery Brown Belt III UC2 Brainery Brown Belt III

            I remember you saying that before @Doug McLean, thank you for the reminder! At this point I figure starting from scratch each time is good practice on how to use PQ… 🤣 – and it’s faster than using subtotals as a bonus…

    • Dan Diesing

      Member
      September 22, 2023 at 4:59 pm
      Points: 312
      Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

      Thanks David,

      Currently, everything is done in Excel. I have a decent template made up that once I had the data input into it with quantity link, it would populate all other pages with relevant data. All I would really need to do is update pricing for labor and materials.

  • Doug McLean

    Member
    September 19, 2023 at 3:16 pm
    Points: 12,674
    Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

    Well at least someone else has found my frustration with Quantity Link when doing something for the finishing trades.
    We can’t set up and use a template, so it becomes VERY time consuming to set up.

    As others have said, export the data to a csv file and then use probably Excel’s most powerful tool, Power Query to do the heavy lifting. It’ll take you a while to learn it, but once you get it, its amazing.

    Both Vince and myself have some pretty cool workflows using PQ, so if you need some help, just ask.

    • Troy DeGroot

      Organizer
      September 21, 2023 at 5:34 pm
      Points: 20,919
      Rank: UC2 Brainery Brown Belt UC2 Brainery Advanced Brown Belt Rank

      I’ve always liked Quantity Link when it’s appropriate, which is not all the time. Even I’m growing tired of the inconsistent functionality. Sometimes the links work, sometimes not. And there doesn’t seem to be a consistent reason either way. Frustrating when teaching and doing presentations. It’s powerful though, so I keep trying.

      • Doug McLean

        Member
        September 22, 2023 at 9:40 am
        Points: 12,674
        Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

        you’re just going to have to let Vince and I show you some basic stuff in PQ.
        😁😁

  • Dan Diesing

    Member
    September 22, 2023 at 12:31 pm
    Points: 312
    Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

    Thanks for the replies. I will be looking into PQ now!

    • Doug McLean

      Member
      September 22, 2023 at 12:41 pm
      Points: 12,674
      Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

      start with this guy
      Mike Girvin at Excelisfun on YouTube

    • Vince French

      Member
      September 22, 2023 at 12:51 pm
      Points: 11,999
      Rank: UC2 Brainery Blue Belt I UC2 Brainery Blue Belt I

      You won’t regret it!

      I would suggest playing around with it a bit first before you decide on exactly what you think you want to get out of it. Hopefully, you fill find that one measurement in Revu combined with Excel may replace several markups that you previously used.

      ‘Door Type 1’ in Revu can have the following information in a table in Excel: – size & type door, details for hinges, lock type, handle type, door lining details, architrave details, finish to the door, etc, etc, etc..

      The idea (from my point of view) is to keep Revu fairly simple so ‘Door Type 1’ never needs to change. The details in Excel are where you make changes, add new, items, etc.. For example, change the type & number of hinges to be used then hit refresh. Within a second or two all of your output information will be updated.

      • Dan Diesing

        Member
        September 22, 2023 at 4:38 pm
        Points: 312
        Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

        After browsing a few things today. This is exactly what I was looking for (even though not quantity link, didn’t need to be) but now it seems like I have a lot of learning to do! Should make everything more streamlined.

        After I get this figured out, I’ll look at ways to optimize my Revu counting/measuring. Hopefully find some more tips and tricks.

        • Don Walke

          Member
          September 25, 2023 at 11:02 am
          Points: 4,923
          Rank: UC2 Brainery Orange Belt IIII UC2 Brainery Orange Belt IIII

          Just curious, are you take-offs limited to lf and type of base, crown, casing, jamb extension, picture rail, chair rail, ect. and door count, type and size? Area of wood ceilings, wall paneling, wainscot, ect?

          If so, I’ve found that Revu by itself can give you those quantities with relatively few clicks if you utilize the custom column and custom tool functions. For example, a single area measurement in a room can give you wood ceiling area (area measurement), base length, crown length and chair rail length (length measurement as defined by the built-in function of the area measurement) and wood wall area (length measurement multiplied by “depth as designated in your particular tool) if that is what you require. A measured door width (polyline) can give you casing length (length + (2 x depth)), and deduct base and chair rail length from the previously measured totals. There are many more techniques that can be used to reduce keystrokes/time and lessen the chance of a mistakes trying to manipulate data outside of Revu.

          At least this works for me and if your take-off work is fairly repetitive, maybe it could work for you.

          • Doug McLean

            Member
            September 25, 2023 at 11:33 am
            Points: 12,674
            Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

            The problem comes when you need to break it down beyond a simple quantity.
            When you need it by floor, phase, building, room, elevation…. quantity link is simply too difficult to set up.
            Building the custom columns for Finish Carpentry is time consuming, but relatively easy. Linking all of that to Excel, that’s where its a PITA.

            You’re right though, you can set it up so that you’re getting lots of data from just one or two markups.

            • Don Walke

              Member
              September 25, 2023 at 12:06 pm
              Points: 4,923
              Rank: UC2 Brainery Orange Belt IIII UC2 Brainery Orange Belt IIII

              Yep, I bailed on QL a couple of years ago. I sort everything by space, mostly by room number and name and am able to use filters to organize the data. Export totals via CSV file where unit prices are applied. I use plan view almost exclusively so I can create a space per room, but sometimes there are items that can’t be measured on plan view so I create a space on an elevation drawing and use the same space name as on plan view.

              Casework is a completely different animal. That’s where I just call my cabinet guy and yell at him to hurry up and get me a price! 😉

  • Doug McLean

    Member
    October 22, 2023 at 10:24 am
    Points: 12,674
    Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

    Checking in on this thread.

    How was the toe dip into PQ?

    Trust me, it’s a rabbit hole you’ll soon find yourself in. And learn to love.

    • Dan Diesing

      Member
      November 8, 2023 at 10:13 am
      Points: 312
      Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

      Thanks for checking in Doug. I’m sorry for not getting back to you sooner. It’s been a deep rabbit hole, I also took on another project while trying to learn this. I have had to brush up on my VBA scripting for the other project.

      I have got my data almost where I need it to be with power query. It is usable but I’m stuck on the transformation (haven’t had any time to dig into it so it might be simple). Top Table is how I currently have my data. My spreadsheet (how the bosses want it) is the bottom table.

      My makeshift workaround is to just pull the data in with this formula:

      =IFERROR(INDEX($E$22:$E$40, MATCH(E$6 & $B8, $C$22:$C$40 & $D$22:$D$40, 0)), “0”)

      It works but if i get bigger sets of data with more buildings or floors I don’t want to have to tediously double check everyone is working right

      • Vince French

        Member
        November 10, 2023 at 12:57 am
        Points: 11,999
        Rank: UC2 Brainery Blue Belt I UC2 Brainery Blue Belt I

        If the details in the header row are fixed, then you could use a couple of formulas to complete the rest of the data.
        The room details on the left could be obtained using a combination of Sort and Unique which will adjust automatically if you’ve put the data from Revu into a table in your spreadsheet.

        The main body of the table could then be completed using a Sumifs formula which would also automatically adjust to the number of rows used.

  • Doug McLean

    Member
    November 8, 2023 at 2:49 pm
    Points: 12,674
    Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

    You need to learn how to pivot and unpivot columns.

    that’s how you’re going to change that data

  • Doug McLean

    Member
    November 8, 2023 at 3:11 pm
    Points: 12,674
    Rank: UC2 Brainery Blue Belt II UC2 Brainery Blue Belt II

    A couple of other things you’re going to want to do is to keep the names of your Subject Column in the first table, the same as your Column headings in the second table.

    If you need them to be in a specific order, build that order in Excel, load it in as a connection only, then you can do a Reorder Table to list function.

    You could also do that report layout using a Pivot Table.

    • Dan Diesing

      Member
      November 8, 2023 at 3:36 pm
      Points: 312
      Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

      Thanks, I forgot to mention that I had already sync’d the names between the 2 tables (just wasnt at my work computer for new screenshot).

      I will look into pivoting the Columns. Lots to learn, trying to fit it into a busy schedule and still get the work done.

      I appreciate the guidance.

Log in to reply.