Controlling Excel

[Guest]

Good morning,
I've made some progress on controlling Excel using Access, including opening
a workbook and saving it as a different name (made from entries in my
frmNewProject object).

What I would like to do is this... only after the Excel workbook (and
application) is closed, I would like to sendobject to the Department Manager
that his form is complete and on the network drive for evaluation. Will
Access wait until the application (Excel) is closed before performing the
sendobject, or will that take additional coding?

Thank you in advance!
Derek

 

[Guest]

Shelling is asynchronous, so (assuming you are Shelling out to Excel from
Access), code execution will not wait until Excel is closed. However, since
you are saving the Excel file under a new name, you could add the following
code after you save the new file but before the SendObject:

Dim NewFilePath
NewFilePath = "C:\Test\ExcelFileName.xls" 'replace with full path\name of
new file.
Do While Len(Dir(NewFilePath)) = 0 'check to see if file exists
Loop

There may be a way to check if Excel is still open also, but the above code
may be more accurate in that it looks for the new file, not just whether
Excel is open or closed.