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

                      Working...
                      X