How do i open an excel spreadsheet, run a macro inside that sheet, save and close it and then import into my database all from a script in filemaker that is triggered by a button?
The easiest and most effective way is to *NOT* store the macro in excel but just use the equivalent VBscript code and trigger that from FM. That will be easier to maintain the code and not have a lot of security warnings issues.
so will the vbscript run on any excel document that doesn't even have macros in it - is that correct?
and so you just have to specify the file that the vbscript will run in and then import it - correct again?
how do you make a vbscript from the macros?
could a macro be run in an excel document via send dde execute?
Sure it can... google "excel command line switches"
embedded macros are going to trigger all sort of security warnings just because they are embedded. The only safe way around it is to get a "code signing certificate" to sign the macro code. That's going to set you back a few hundred dollars. And you still have a deployment issue. What if you come up with some slight changes to the macro code. Then you need to make sure that everyone is working off the same version of the excel files. Whereas if you keep the code inside FM then you do not need to distribute or worry about code that is outside of your solution.
good point. thanks again for your help!
i just tried using the following vbscript and it works perfectly with no security warnings or anything. however there are still the other points you mentioned so i will only be using this as a last resort.
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\FileName.xls", 0, True)
Set xlBook = Nothing
Set xlApp = Nothing
edit: the only problem with using send event from filemaker is the macro opens a popup and even if it is set to bring target application to foreground it doesn't show up!
Retrieving data ...