Formatting an Excel Report

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Formatting an Excel Report

    Good Morning All.
    Running PC-DMIS 2013MR1 SP5 without Datapage
    -------------------------------------------------------------------

    So.
    Excel.

    I haven't had much time to play around with it honestly. Management would prefer I write PC-DMIS code only so I haven't had much time to pretty up the reports.
    Essentially I output to PDF and Excel.
    The PDF report looks fine, but the Excel is sorely lacking.

    I've got it actually outputting to Excel automatically, consistently, what have you.
    But I don't know how to go about actually formatting it to go into a form or something.

    We have, for example, customer PPAP forms that I could use for a template in Excel, or maybe I'll take the time to make my own.

    I'm just not sure how I would go about formatting the report. Would I make a macro?
    I'm familiar with Excel in terms of I can go through a spreadsheet, enter/retrieve data, aaaaand that's about it... Lol!
    ------
    As a training excersize (maybe for production, I haven't decided) I would like a customers data to be displayed on one of their forms, which I have a template for.
    I just don't understand how to put the data on the report!
    --
    P.S.
    I know there are about a billion topics on Outputting to Excel, but I've already outputted the data. I've gotten past step 1. What's step 2!?

  • #2
    I guess everyone has their own way of skinning this cat. Of course I started off just like you a few years back. I made it my priority to learn VBA, it took a few months but I got the hang of it. To answer your question best we'd probable need more info... I think. If the data is out in the Excel world what else are you trying to do? Are you inputting single values in cells or ranges? What forms are you using, AS9102, in-house inspection report?

    Excel is fun.
    PcDmis 2015.1 SP10 CAD++
    Global 7-10-7 DC800S

    Comment


    • #3
      We have our own in-house reports I'd like to format to. Ideally the data would be single values [Nominal, +Tol, -Tol, Actual, OutTol] and get sent to individual cells.
      Each dimension would be on 1 row.

      The thing is, when I output to Excel it has a default format that is ugly. I would like to send the data to a spreadsheet I already have, the problem I've got is actually getting the data there.

      EDIT:
      I'm under the impression I can't do this natively in PC-DMIS. I would assume I need to create a Macro for each part program that will format the Excel spreadsheet into the form. But I don't know, I'd like to know what you guys think; I've never done this before.

      Comment


      • #4
        You can do it directly from VB but (it's quick, but the vb editor in pc-dmis isn't the most user friendly thing) it's not straight forward.

        You could do it in VBA (excel macro) but it's you'd have to learn how

        Or, you could export in the format you have, then simply link the cells on your formatted report to the exported sheet (quick and easy)

        Automettech - Automated Metrology Technology

        Comment


        • #5
          I'm guessing there's no way to create a "one size fits all" script for this?
          E.g. one program, script, macro, or what have you for all of the PC-DMIS programs?

          I've used VBS extensively in the past, and I've used Excel, I've never programmed it.
          ---
          I'll have to play around with it. If I come up with something eventually I'll get around to posting about it

          Comment


          • NinjaBadger
            NinjaBadger commented
            Editing a comment
            That's more than doable.

            Do you use xactmeasure?

          • InspectorJester
            InspectorJester commented
            Editing a comment
            No idea!
            Probably not...?
            I'd have to look at the difference between the two.
            I use the Dimension Location and Dimension Distance for the program in question.

        • #6
          Originally posted by InspectorJester View Post
          I'm guessing there's no way to create a "one size fits all" script for this?
          E.g. one program, script, macro, or what have you for all of the PC-DMIS programs?

          I've used VBS extensively in the past, and I've used Excel, I've never programmed it.
          ---
          I'll have to play around with it. If I come up with something eventually I'll get around to posting about it
          If you have access to internet on the PC you'll be working on, it'll take you a few days/weeks. Sounds like you are willing. Document the whole the whole thing and we can help you along the way. Few more questions...

          A huge one... what do you use: Xcrap or legacy?

          When done this SHOULD fit ALL your programs! It's worth investing time into it, you won't regret it, you won't stop there either. Your same line of thought was my gateway to this bug.
          PcDmis 2015.1 SP10 CAD++
          Global 7-10-7 DC800S

          Comment


          • InspectorJester
            InspectorJester commented
            Editing a comment
            See comment above. I'm thinking Legacy; I don't mess with FCF

          • Kp61dude!
            Kp61dude! commented
            Editing a comment
            Ah ok. Then this just got a little less complicated.

        • #7
          Right now we don't use Excel reporting at all. I know for a fact we'd like to move into doing that.
          The CMM is sort of a paperweight right now until I can beef it up to be used in production, and send snazzy reports to humans other than me.
          Ideally I'd like to have something set up before that happens.
          I have the report I'd like to send it to. I've just gotta send it. When I get the time to play around with it (I'll be getting a laptop to work on within a few days that I'll use as my test rig) I will, but I'd like to have a general idea of how I might go about this in mind before I start.

          The way it gets outputted, I like. It's all text & data in cells. It looks pretty straightforward to adjust the data.

          Comment


          • #8
            There is a nice Excel Spreadsheet I'd love to give credit to whoever built it I just can't remember where I snagged it from. Basically it'll get you going on this asap. With a little bit of tweaking of course.

            I even used it in loops!

            Sorry due to the TIGHT security here I can't attach anything to 3rd party like dropbox and what not.
            Last edited by Kp61dude!; 05-01-2018, 10:18 AM.
            PcDmis 2015.1 SP10 CAD++
            Global 7-10-7 DC800S

            Comment


            • InspectorJester
              InspectorJester commented
              Editing a comment
              Awesome!
              ...But it doesn't help if I can't see it! Lol

          • #9
            Found it! https://www.pcdmisforum.com/forum/pc...an-excel-macro


            Again, not my work as far Excel is involved.
            PcDmis 2015.1 SP10 CAD++
            Global 7-10-7 DC800S

            Comment


          • #10
            I have had this .bas for a while not sure from where possibly this forum. Credit goes to someone else.
            Excel Scripting.zip

            Comment


            • NinjaBadger
              NinjaBadger commented
              Editing a comment
              I think that might have been mine from waaaaay back!

              Or maybe not - I don't remember adding any Xact dimension processing

            • bjgalbreath
              bjgalbreath commented
              Editing a comment
              This looks like the script we used at my last job (2018 R1), but when I try it at my new place (2018 R2) it hangs up on the execution until I force close PCD or it crashes.

          • #11
            If you have your data in one Excel sheet, and your template in another, you can record a macro when you do all the transfers and formatting manually. This macro can then be used to replicate exactly these steps on another exported file. One-time work for each program.

            The next step is to look att the generated VB code of the macro, break out common code sections into small subroutines, try to generalize it (variable number of dimensions for example), fiddle around with it until it does what you want. One time work for all programs...
            AndersI
            SW support - Hexagon Metrology Nordic AB

            Comment


            • #12
              Originally posted by AndersI View Post
              If you have your data in one Excel sheet, and your template in another, you can record a macro when you do all the transfers and formatting manually. This macro can then be used to replicate exactly these steps on another exported file. One-time work for each program.

              The next step is to look att the generated VB code of the macro, break out common code sections into small subroutines, try to generalize it (variable number of dimensions for example), fiddle around with it until it does what you want. One time work for all programs...
              I think this idea will be the most efficient, moving forward.
              I've never generated a macro before, but I understand the general process
              (Hit Record, do your formatting, save it, run it, tweak & repeat)

              Adjusting the code shouldn't be too difficult either.
              As far as I understand, I'll be doing the same formatting for every part, it just depends on how many dimensions there are.
              I'll have to look at it some more. I can handle that...

              Thanks for the help guys. This information gives me an excellent start, and is everything I need to get going at least.

              As I try things out, I may report back with more questions, but hopefully my success story

              Comment


              • Kp61dude!
                Kp61dude! commented
                Editing a comment
                Nothing else is readily available with that capability... I believe that is the fastest way to learn code, when you don't know where to start, hit record and it'll lay code down for you. After looking at and studying it for a little while you'll eventually stop using the record button, instead, you'll type it out. Same goes with exporting a PCD program to BASIC, you can almost copy paste the code over to Excel VBA just requires minor tweaking sometimes. Great tools to get you going and best part of it is that you probably already have access to them.

              • InspectorJester
                InspectorJester commented
                Editing a comment
                Going this route; it just confused me more.
                I've instead resorted to what I know; VBS!
                I've got a script that adjusts cells as necessary; I've just gotta work out the transferring of data between workbooks, and incrementing for number of dimensions. It's not very difficult, actually. Just tedious

            • #13
              So the VBS solution I'm drawing out right now; it's got some quirks.

              It works and works fine enough for me to be able to implement into all of my programs, and use it automatically. (Once I get the naming convention down; I just have to work on it).
              It did, however, bring me questions.

              How come when I output to Excel, it does not have the report comments as well? (This is how I differentiate between features. The naming convention helps, but its more in depth in the comments)

              I'm guessing Excel only sends STATS data?

              Comment


              • InspectorJester
                InspectorJester commented
                Editing a comment
                I'll obviously release this to everyone; just when I;m actually done with it.
                I'm trying to find the best way to differentiate between features.
                This location being a notch, that location being a hole, etc.

                I might need to rethink how I name those features...

                EDIT: I probably just could rename the dimensions. LAND_LOC, NOTCH_LOC, etc.

            • #14
              Originally posted by InspectorJester View Post
              So the VBS solution I'm drawing out right now; it's got some quirks.

              It works and works fine enough for me to be able to implement into all of my programs, and use it automatically. (Once I get the naming convention down; I just have to work on it).
              It did, however, bring me questions.

              How come when I output to Excel, it does not have the report comments as well? (This is how I differentiate between features. The naming convention helps, but its more in depth in the comments)

              I'm guessing Excel only sends STATS data?
              Which Excel 'program' did you go with?
              PcDmis 2015.1 SP10 CAD++
              Global 7-10-7 DC800S

              Comment


              • InspectorJester
                InspectorJester commented
                Editing a comment
                What do you mean? What version of Excel, or how'd I output the dimensions?
                If it's how I outputted the dimensions;

                I use the print command and ticked "Excel", extension ".xlsx"

                If it's the version; 2016.

              • Kp61dude!
                Kp61dude! commented
                Editing a comment
                There were suggestions mentioned on post #9 and 10. Did you try any of those? or what route did you take? looks like you went with PCD commands...

            • #15
              Kp61dude!
              I received both files, but got distracted making a script for it. No PCD commands, the script only copies data from whatever is outputted by default into a formatted sheet I've created.

              [Edit]
              The script interacts exclusively with Excel, and is to be used as an external command with the Wait option.
              First, PC-DMIS runs into the Print command, and outputs 3 Excel files to a seperate folder, two of which contain no data but the header (why I have no idea).
              To compensate, I run an external command immediately after, that searches the seperate save folder for the largest Excel file inside. It then copies this file to a different location and clears the seperate save folder. Then PC-DMIS copies the file and renames it per the programs naming convention, to a different save folder, and deletes the old one.
              Then the script runs a different external command; this script. It searches the different folder for any Excel file (there will be only one at a time, ideally...) and uses that.
              [/Edit]

              I've got it capturing all of the dimensions regardless of how many dimensions there are (so I can use it in every program).

              My problem is I have to manually enter in what the features are, because my naming convention changes ever so slightly between programs (and it doesn't identify feature type, just dimension type, e.g. location, depth, diameter)

              But it helps. Makes it easier to format Excel sheets now as all of the data is sent automatically. I just have to go back and forth between the PDF that gets saved with the Excel file to retrieve the rest of the data, and go back through my programs and re-name the dimensions (so I can differentiate between a notch location and a hole location, for example). Then I'll be able to add that section into the VB Script and viola. Music.

              I can post it as a .txt file on here. You can all at least see my thought process and potentially improve it
              Attached Files
              Last edited by InspectorJester; 05-03-2018, 07:44 AM. Reason: See attachment

              Comment


              • InspectorJester
                InspectorJester commented
                Editing a comment
                See edits above

              • InspectorJester
                InspectorJester commented
                Editing a comment
                It should be noted this is a script. It is in its Alpha stages, and cannot run without being edited, if at all.
                I think I changed the right names...
                If you save it, save it as .vbs instead of .txt.
                Be sure to Right Click and hit Edit.. to open it in Notepad before you run it.
                Not only to take a look and make sure it's not actually a virus, but to see what it does. Plus you have to edit it anyways

              • InspectorJester
                InspectorJester commented
                Editing a comment
                I'll tweak it summore as I go. I haven't gotten my test rig yet, but when I do I'll have plenty of time to knock out some cool extra-demon programs to help tame it.
                Maybe one to adjust the size of the window when I start it so it formats to two of my monitors at the click of a button...

            Related Topics

            Collapse

            Working...
            X