hexagon logo
Register Sign in
  • Replies 23 replies
  • Subscribers 31 subscribers
  • Views 376 views
  • Users 0 members are here
Options
Related

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

Nano Vujkovic
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
  • 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.
  • Thanks NB, but unfortunately i need more than that.
  • 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. 


    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
  • 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. 


    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.
  • 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?


  • 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.
  • 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


  • 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



  • 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.
  • 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.

© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement