Checking if the excel is open - here I go again.

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

  • Checking if the excel is open - here I go again.

    Some of our operators do not close the excel before running another part which causes excel to not save data. The data is dent to excel via a script.

    How can i check if the excel is open and if so close it?

    Thanks

  • #2
    It's a long time ago but I believe you need to try GetObject first (which is like create object but gets an open instance), add error trapping so that if that fails you know it's closed and can open it. if it''s open, you can close it then open it.
    Automettech - Automated Metrology Technology

    Comment


    • #3
      Thanks NB, but unfortunately i need more than that.

      Comment


      • #4
        One possible solution with minimal scripting would be:

        1. Save the attached file in example location and change file extension from .txt to .bat.

        2. Then put an external call (see below) in your program and change file path to location of batch file.


        Code:
        EXTERNALCOMMAND/NO_DISPLAY, NO_WAIT ; C:\FOLDER_WHERE_YOU_SAVE_BATCH\KILL_EXCEL.BAT

        Possible positive/negative, no prompts will be displayed such as SaveAs in excel it will just kill it. Can be used for any process.

        Attached Files

        Comment


        • #5
          Originally posted by RichT View Post
          One possible solution with minimal scripting would be:

          1. Save the attached file in example location and change file extension from .txt to .bat.

          2. Then put an external call (see below) in your program and change file path to location of batch file.


          Code:
          EXTERNALCOMMAND/NO_DISPLAY, NO_WAIT ; C:\FOLDER_WHERE_YOU_SAVE_BATCH\KILL_EXCEL.BAT

          Possible positive/negative, no prompts will be displayed such as SaveAs in excel it will just kill it. Can be used for any process.
          Thanks Rich.
          This works, but it kills any excel file open and it's not going to work for me because of that. There are some other excel files open that need to stay open.

          I don't think i was clear in my OP. I don't want all of the excel app killed, just the results workbook if open for the specific program running in PC-DMIS.

          Comment


          • #6
            Originally posted by Nano Vujkovic View Post
            Thanks NB, but unfortunately i need more than that.
            In what respect?

            It's how you accomplish what you're trying to do. How does it not work?
            Automettech - Automated Metrology Technology

            Comment


            • #7
              Originally posted by NinjaBadger View Post

              In what respect?

              It's how you accomplish what you're trying to do. How does it not work?
              I didn't say it doesn't work, just that i don't know what to do with it.
              I understand the principal behind what you're saying, but i'm not a coder and i don't know how to code it. I had a course in C++ many years ago and that is the extant of my coding knowledge.
              What I've done so far was done by copy/paste, looking at examples and trial and error.

              Comment


              • #8
                This is the code i have so far, but it does't work.

                FileName = Part.partname & " " & strVariable & " " & reasonVar & ".xlsm"
                Workbook(FileName).Close 'Close results workbook

                Comment


                • #9
                  Originally posted by Nano Vujkovic View Post

                  I didn't say it doesn't work, just that i don't know what to do with it.
                  I understand the principal behind what you're saying, but i'm not a coder and i don't know how to code it. I had a course in C++ many years ago and that is the extant of my coding knowledge.
                  What I've done so far was done by copy/paste, looking at examples and trial and error.

                  I did one module of Visual Basic at Uni years ago. The rest was self taught / copy pasted / modified etc etc.

                  Debugging will help you figure it out.

                  FileName = Part.partname & " " & strVariable & " " & reasonVar & ".xlsm"
                  MsgBox(FileName)
                  Workbook(FileName).Close 'Close results workbook

                  If the file name is being generated correctly then you may need to loop through the open workbooks checking for a match, before you try close it



                  (This is psudocode - i.e. It shows tha basic structure but the exact syntax etc might not be right.)

                  i.e.

                  for i = 1 to workbooks.count

                  if workbooks(i).name = FileName then
                  workbook(i).close
                  end if
                  next i
                  Automettech - Automated Metrology Technology

                  Comment


                  • #10
                    Originally posted by NinjaBadger View Post


                    I did one module of Visual Basic at Uni years ago. The rest was self taught / copy pasted / modified etc et

                    Debugging will help you figure it out.

                    FileName = Part.partname & " " & strVariable & " " & reasonVar & ".xlsm"
                    MsgBox(FileName)
                    Workbook(FileName).Close 'Close results workbook

                    If the file name is being generated correctly then you may need to loop through the open workbooks checking for a match, before you try close it



                    (This is psudocode - i.e. It shows tha basic structure but the exact syntax etc might not be right.)

                    i.e.

                    for i = 1 to workbooks.count

                    if workbooks(i).name = FileName then
                    workbook(i).close
                    end if
                    next i
                    Thanks NB, I'll try this today.

                    Quick question. What compiler are you using?
                    I installed Visual Studio Code, but it looks very different than what i remember from back in the day.

                    Comment


                    • #11
                      It depends.

                      I use .bas scripts from within pc-dmis itself for simple stuff (.csv file generation, archiving .prg files)

                      I use visual studios (.net) for proper applications (RunDmis operator interface / RepDmis results database etc)

                      I use vba in excel occasionally if it's something excel-centric.

                      Automettech - Automated Metrology Technology

                      Comment


                      • JackMido
                        JackMido commented
                        Editing a comment
                        "(RunDmis operator interface / RepDmis results database etc)"

                        I like the idea of having a results databse!, currently I just use file explorer lol Thanks Ninja you've just found me my next .net project!

                    • #12
                      https://www.pcdmisforum.com/forum/pc...pc-dmis-script

                      It's in my posted code at the bottom.
                      You can even set it to report without showing excel to speed it up.

                      Comment


                      • #13
                        Originally posted by NinjaBadger View Post
                        It depends.

                        I use .bas scripts from within pc-dmis itself for simple stuff (.csv file generation, archiving .prg files)

                        I use visual studios (.net) for proper applications (RunDmis operator interface / RepDmis results database etc)

                        I use vba in excel occasionally if it's something excel-centric.

                        Here's a couple of screen shots JackMido


                        https://www.dropbox.com/s/oflvvin7ecj0s9s/rd1.jpg?dl=0

                        https://www.dropbox.com/s/3vvofah6e7dgt4k/rd2.jpg?dl=0
                        Automettech - Automated Metrology Technology

                        Comment


                        • JackMido
                          JackMido commented
                          Editing a comment
                          **** that looks professional AF! Cheers for the screenshots mate it's definitely something I'm going to work on! Any chance you could post a couple of screenshots of RunDmis? That's another idea I am toying with (for when my programming skills have improved)

                        • vpt.se
                          vpt.se commented
                          Editing a comment
                          Really nice, NinjaBadger !

                      • #14
                        https://www.dropbox.com/s/924hd5rvr016c4c/rund.jpg?dl=0


                        My pride and joy! Looks and works brilliantly. RepDmis is arguably more useful however.
                        Automettech - Automated Metrology Technology

                        Comment


                        • #15
                          It's no substitute for a proper stats package, but we don't do runs big enough for SPC, nor have the £'s to spend on it.

                          Here I can see at a glance what's been run, when, view single reports or history / run chart.

                          It's quick as well. There's no real back end database either, all the data is stored in .xml files which are read in and processed.
                          Automettech - Automated Metrology Technology

                          Comment


                          • JackMido
                            JackMido commented
                            Editing a comment
                            SPC is something that I was really keen on us investing in but I seem to be the only one that can see he benefit of having it so having a similar system to RepDmis seems like a happy compromise

                        Related Topics

                        Collapse

                        Working...
                        X