With the help of this form, I have written a script that enables me to send data from Multiple Computers to a single Excel Document. However I have run into another problem. I want the program to automatically open the Excel Document. It does do that, however when another user goes to send data again from another computer the following window in Excel pops up:
xxxx.xls is already open, reopening will cause you to lose changes you made. Do you want to reopen. I click No and all is good.
How can I automate this response?
How do I check to see if the doc is already open, and if it is don’t try and open it again?
Thanks
tell application "Finder" of machine BaseMac
using terms from application "Finder"
if "Microsoft Excel" is not in (get name of processes) then open application file id "com.microsoft.Excel"
repeat until "Microsoft Excel" is in (get name of every process)
delay 1
end repeat
end using terms from
end tell
tell application "Finder" of machine BaseMac
using terms from application "Finder"
activate
open document file "xxxx.xls" of folder "Desktop" of folder "dcpm04x" of folder "Users" of startup disk
end using terms from
end tell
try
tell application "Excel" of machine BaseMac
using terms from application "Microsoft Excel"
Activate
tell Worksheet 1
can’t you check if Excel has your document open to prevent a reopen instead of trying to handle the popping alert?
i would try sth like this:
...
tell application "Finder" of machine BaseMac
using terms from application "Finder"
activate
set myDoc to (document file "xxxx.xls" of folder "Desktop" of folder "dcpm04x" of folder "Users" of startup disk)
if (my checkExcelDoc( ( myDoc as alias ) as string ) is false) then
open myDoc
end if
end using terms from
end tell
...
on checkExcelDoc(thePath)
tell application "Microsoft Excel"
set docs to documents
repeat with doc in docs
if full name of doc is thePath then
return true
end if
end repeat
return false
end tell
end checkExcelDoc
Note: I couldn’t try this with a network machine - so I hope it works …
That is what I was trying to figure out how to do, Thank You very much for the help, as you have pointed me in the correct direction of what I need to do.
I did run into some problems when adjusting my original code, to what you suggested. The first time the build failed on the:
on checkExcelDoc(thePath)
tell application "Microsoft Excel"
set docs to documents
repeat with doc in docs
if full name of doc is thePath then
return true
end if
end repeat
return false
end tell
end checkExcelDoc
So I didn’t put that in, and modified the other script you adjusted for me to:
tell application "Finder" of machine BaseMac
using terms from application "Finder"
activate
set myDoc to (document file "MASTER Page SiteSyncT.xls" of folder "Desktop" of folder "dcpm04x" of folder "Users" of startup disk)
if (((myDoc as alias) as string) is false) then
open myDoc
end if
end using terms from
end tell
Now, it won’t open the document at all, my program says it can’t find the Excel sheet. If I revert back to the original code, the document at least opens. But, I still can’t
use my app to send data from multiple machines. I know this is the way to go, I just need to figure out why it isn’t working.
By the way I am using Excel X (version 10.1.9),not sure if that makes a difference or not.
Sorry - I am not sure about the scripting dictionary of Excel 10 - i have tried it with a Version 11 here.
You could try this script in Script Editor …
tell application "Microsoft Excel"
get properties of document 1
end tell
… to check if your version’s documents have a ‘full name’ property. If not - maybe they have a path and a name property? Then this could eventually work:
...
if (path of doc & "/" & name of doc) is thePath then
...
The dictionary between the 2 Excel versions is different.
When I try an run the script:
tell application "Microsoft Excel"
get properties of document 1
end tell
I get the error, "Microsoft Excel got an error: Can’t get every property of Document 1
So I changed the code to:
tell application "Microsoft Excel"
GetOpenFilename of document 1
end tell
That tells me where the document is located: DCPM04X:users:dcpm04x:desktop:TelegrapgData.xls
Which to me indicates Excel sees the location differently than the way the finder does.
Also, I don’t think my version of Excel uses the ‘full name’ property, becase I can change it to name & it compiles.
I am going to try a different approach. Instead of having the finder open the file, I am going to have Excel open the file and adjust the code to check to see if it is open.