Run a VB Macro with Schedule Script !

[jabrane]

 Hello forum;



I work with FM 11 and FM pro advanced server. My abjectif is to export word file,after running a macro script from excel file. I explain the steps:

1 / with FM server I can export excel files from web browser and send them by mail to users;I use a schedule script running with filemaker server for this task.

2 / User open the excel file received, Run the Excel Macro (macro is saved in an excel file by default (PERSO.XLS) can be used in every excel document in the user's PC).

3 / USING this macro, using MS Word bookmarks, we will have Word files whose data is copied from Excel files sended from the server.

??  I need to have a script in FileMaker Server which includes all these steps, and instead receive an email from FM server containing Word files and NOT Excel files. Please, a track or a solution for this huge work.

 

[Wim Decorte]

Seems like there should be an easier way of doing what you want: you have data in a FM file and ultimately it needs to end up as Word documents.  Seems like the step through Excel is no needed if you can just create the Word documents directly.  Eliminating Excel will improve the stability of this whole setup.

 

As to executing a VBA macro:  Both Word and Excel have command-line switches that you can specify to open a document and run a macro.  However since this needs to run on a server you don't really want to execute things that need to draw a UI and live in user space.

 

One solution would be to take your VBA code and see if you can change it to VBscript syntax.  FMS can execute VBscript so it can use the Word ActiveX interface to do pretty much what you can do from inside Word from VBA.  Here too though the code would have to be written in such a way that you never draw a UI and that you check fro errors and trap them so that you can exit gracefully without leaving things stuck in memory - after all you do not want to bring down your server to clear that up.

 

I would look into the Word API and see if you can transform the data like you want it without going through Excel.

The sequence would then look like this in an FMS Script Sequence schedule:

- FMS exports data into the FMS documents folder

- FMS triggers a VBScript that reads that data and creates Word documents from it

[jabrane]

This Is my   VBA macro , hope you can help me :

 

 

Sub MacroExcelWord()

Dim WordApp As Word.Application

Dim WordDoc As Word.Document

Dim oWdApp As Object 

Dim i As Byte

Dim sChemin As String

Dim nom As String

 

On Error Resume Next

Dim j As Integer

j = ActiveSheet.UsedRange.Rows.Count

 

Dim n As Byte

n = Cells(1, Columns.Count).End(xlToLeft).Column

 

If ActiveWorkbook.Name Like "Class*.xls" Then

For j = 2 To j   'start the loop the opeation until the next will be for each line used in the file

 

Set WordApp = CreateObject("word.application") 'ouvre session word

nom = Sheets(1).Cells(j, 2)

 

Set WordDoc = WordApp.Documents.Open("C:Documents and SettingsUserMy DocumentsClassModel.doc") 

Set oWdApp = CreateObject("Word.Application")

Set WordDoc = oWdApp.Documents.Open("C:Documents and SettingsUserMy DocumentsClassModel.doc")

 

For i = 1 To n

 

WordDoc.Bookmarks("Sig" & i).Range.Text = Cells(j, i)

Next i

WordDoc.Bookmarks("Signet").Range.Text = Cells(j, 2)

 

WordDoc.SaveAs Filename:=nom & ".doc"

WordApp.Visible = False

 

oWdApp.Quit

ActiveDocument.Close True

WordDoc.Quit 

WordApp.Quit

 

Next j

Application.Quit

End If

End Sub

[jp98]

I need to do a similar thing - execute a macro in excel then import the data in a single script - any suggestions?

[Wim Decorte]

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.

[jp98]

what is the "equivalent VBscript code" to a macro?

[Josh Ormond]

This may give you a headstart: http://help.mjtnet.com/article/19-converting-office-vba-to-vbscript

[jp98]

4 minutes ago, Josh Ormond said:

This may give you a headstart: http://help.mjtnet.com/article/19-converting-office-vba-to-vbscript

thanks very much.

[jp98]

5 hours ago, Wim Decorte said:

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.

this is what i want to do with the vbscript

I want to be able to click an import button in filemaker which runs the VBscript to apply the macro to a specified excel sheet in a certain location (this will always be the same name but it will contain different data each time), then imports the data from the excel sheet into filemaker matching up the columns and then at the end of the script deletes the excel file from the folder we got it from. would this work?

 

would the vbscript open the file, run the equivalent macro (process the data), close the file and delete the file and then all filemaker needs to do after calling it up is import the data - is that correct or would filemaker do some of this?

 

and how does filemaker open vbscripts? would this whole process be able to happen blindly?

[Wim Decorte]

7 hours ago, jp98 said:

would the vbscript open the file, run the equivalent macro (process the data), close the file and delete the file and then all filemaker needs to do after calling it up is import the data - is that correct or would filemaker do some of this?

I think that is correct, but it describes a workflow that only you knows for sure... 

 

7 hours ago, jp98 said:

and how does filemaker open vbscripts? would this whole process be able to happen blindly?

Can be done in any number of ways, with and without plugins.

Without plugins:

You can write the VBscript so that it accepts parameters, store the VBscript in a container field and use "export field contents" when you need it then use the "send event" script step to call it and pass the parameters.

You can also store the VBscript syntax (it's just text) in a text field so that you can substitute in whatever needs to come from your FM data, then again use the "export field contents" + "send event" to execute it.