excel macro in DB server

Hi;

I work with FM 11 and FM pro advanced server. My abjectif is to export word file from a database server ,after running a macro script from default excel file.
I cretae the macro that save data sheet excel on a template word . I shoud make it running on a database server ( FileMaker server 11). Is it possible to run an excel macro and save documents (.doc) on the DataBase server??

My macro is:

Dim TimeToRun
Sub Auto_Open()
Call scheduleJb
End Sub
Sub scheduleJb()
TimeToRun = Now + TimeValue("00:00:01")
Application.OnTime TimeToRun, "MacroAutoJB"
End Sub

Sub MacroAutoJB()

Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim oWdApp As Object
Dim i As Byte
Dim sChemin As String

On Error Resume Next
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

Set WordDoc = WordApp.Documents.Open("C:\Documents and Settings\User1\My Documents\Class_fich.doc") 'ouvre document Word
Set oWdApp = CreateObject("Word.Application")
Set WordDoc = oWdApp.Documents.Open("C:\Documents and Settings\User1\My Documents\Class_fich.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"
colonne du fichier excel
WordApp.Visible = False
End If
Next j
...

Thnk yopu for your reply ;

Thnk yopu for your reply ; This is my finaly macro created after your last one sended to me . But, I still have a error msgbox : " member of the collection does not exist required"..!!!?? I can't understand what's the problem!!

Sub CreateWordDoc()
'---------------------------------------------------------------------------
' Procedure : CreateWordDoc
' DateTime : 23/11/2012
' Author : Argyronet
' Purpose : Create Word doc according to data cell content
'...........................................................................
' Parameters : None

' Return Codes : None
'...........................................................................
' Notice :
'---------------------------------------------------------------------------
Const WORD_DOC As String = "C:\Documents and Settings\ADMIN\My Documents\ClassJb.doc"
Dim oWordApp As Word.Application
Dim oWordDoc As Word.Document

Dim oXLWkb As Excel.Workbook
Dim oXLSht As Excel.Worksheet
Dim oXLRng As Excel.Range
Dim oXLCell As Excel.Range

Dim R As Long
Dim strData As String
Dim strBookmarkName As String

Dim strUserName As String
Dim strWordDocumentName As String

On Error GoTo L_ErrCreateWordDoc
'Get user name
strUserName = Environ("USERNAME")
strWordDocumentName = Replace(WORD_DOC, "USERNAME", strUserName)
'Create a new instance of Word
Set oWordApp = New Word.Application
'Set the variable to the target workbook
Set oXLWkb = ActiveWorkbook
'Set the variable to the target sheet
Set oXLSht = oXLWkb.Worksheets(1)
'Set the used range (A2:A5)
Set oXLRng = oXLSht.Range(Cells(2, 1), Cells(Cells(1, 1).End(xlDown).Row, 1))
'Create a new document
Set oWordDoc = oWordApp.Documents.Open(strWordDocumentName, False, False)
For Each oXLCell In oXLRng
'Counter
R = R + 1
'Get data and date
strData = "Data = " & oXLCell.Value & " created on " & oXLCell.Offset(0, 2).Value
'Build documen name
strBookmarkName = oXLCell.Offset(0, 1).Value
'Put data into the bookmark of the document
oWordDoc.Bookmarks(strBookmarkName).Range.Text = strData
Next
'Save and close document
With oWordDoc
.Save
.Close
End With
'Quit Word
oWordApp.Quit

On Error GoTo 0
L_ExCreateWordDoc:
'Reset used memory
Set oXLRng = Nothing
Set oXLSht = Nothing
Set oXLWkb = Nothing
Set oWordDoc = Nothing
Set oWordApp = Nothing
Exit Sub

L_ErrCreateWordDoc:
MsgBox Err.Description, 48, Err.Source
Resume L_ExCreateWordDoc

End Sub

Hi, What do you mean by

Hi,

What do you mean by :
"save documents (.doc) on the DataBase server"
?
Do you want to save the document as an embedded field into the database ?
If so, it is strongly not recommanded.
Can you expand your wishes ?

Hi; First i want to thank

Hi;
First i want to thank you for your reply.
I mean by " save documents (.doc) on the database" ; that I want to save a "WORD" documents, for exemple , The file that will be saved has aname like (File_Saved.doc)..

I will add some thing to my

I will add some thing to my question please;after running this excel macro , I will have many Word document, for each line in my excel file , The macro create a new document! Can I have just ONE new document for One excel file and for each line , I want to make a new page in the word docuemnt . for exemple , I have 4 line in my Excel sheet or file , I want to found 4 page or 4 window in the same Word document after running the macro. Is iyt possible please ??

Well, ok... #1/ Like I told

Well, ok...
#1/ Like I told you yesterday, it is not recommanded to store OLE objects within a database, because it will increase the DB file size enormously and this can be impact to database performances...
It is better to store the path of each document where the documents are stored on a NAS server (Network Attached Storage).
#2/ With and from VBA, you can do about all what you want to get with created, managed ou updated documents either they are Excel, Word or PowerPoint documents. This is the subroutine which is wrote to exceute properly the instruction you wish ; for example you suppose this range of data inserted into the range A1:

Data Filename CreationDate
1234 D:\GJB\Document1.docx 22/10/2012
1235 D:\GJB\Document2.docx 23/09/2012
1236 D:\GJB\Document3.docx 13/11/2012
1237 D:\GJB\Document4.docx 12/10/2012

Then you can use this subroutine to create in this case 4 documents taht will contain the data and the date...

Option Explicit

Private Sub CreateWordDoc()
'---------------------------------------------------------------------------
' Procedure : CreateWordDoc
' DateTime : 22/11/2012
' Author : Argyronet
' Purpose : Create Word doc according to data cell content
'...........................................................................
' Parameters : None

' Return Codes : None
'...........................................................................
' Notice :
'---------------------------------------------------------------------------
Dim oWordApp As Word.Application
Dim oWordDoc As Word.Document

Dim oXLWkb As Excel.Workbook
Dim oXLSht As Excel.Worksheet
Dim oXLRng As Excel.Range
Dim oXLCell As Excel.Range

Dim R As Long
Dim strData As String
Dim strDocumentname As String

On Error GoTo L_ErrCreateWordDoc
'Create a new instance of Word
Set oWordApp = New Word.Application
'Set the variable to the target workbook
Set oXLWkb = ActiveWorkbook
'Set the variable to the target sheet
Set oXLSht = oXLWkb.Worksheets(1)
'Set the used range (A2:A5)
Set oXLRng = oXLSht.Range(Cells(2, 1), Cells(Cells(1, 1).End(xlDown).Row, 1))
For Each oXLCell In oXLRng
'Get data and date
strData = "Data = " & oXLCell.Value & " created on " & oXLCell.Offset(0, 2).Value
'Build documen name
strDocumentname = oXLCell.Offset(0, 1).Value
'Create a new document
Set oWordDoc = oWordApp.Documents.Add
'Put data into teh document
oWordApp.Selection.TypeText strData
'Save and close document
With oWordDoc
.SaveAs2 strDocumentname
.Close
End With
Next
'Quit Word
oWordApp.Quit

On Error GoTo 0
L_ExCreateWordDoc:
'Reset used memory
Set oXLRng = Nothing
Set oXLSht = Nothing
Set oXLWkb = Nothing
Set oWordDoc = Nothing
Set oWordApp = Nothing
Exit Sub

L_ErrCreateWordDoc:
MsgBox Err.Description, 48, Err.Source
Resume L_ExCreateWordDoc

End Sub

Is it what you are looking for ?

Hi; Thnk you for your Time

Hi;
Thnk you for your Time and your reply ; I can't integrate some part of your Macro to that one I have..may be I can't inderstand perfectly your Macro , Im beginer in Excel Macro and when I try to run yours , I don't have a result or changes .

Into your VBE windows, from

Into your VBE windows, from the VBA project of your workbook, you must check reference to Microsoft Word Object Library.
You also have to create a directory such as the path specified into my data sampple or change cells B2:B5 according to your own target path.

What kind of problem or error you met ?

f ActiveWorkbook.Name Like

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

user = Environ("username")
sName = ActiveWorkbook.Name
sPath = "C:\Documents and Settings\" & user & "\My Documents\"
sName = Replace(sName, ".xls", "_Word")
MkDir sName
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")
nom = Sheets(1).Cells(j, 2)

Set WordDoc = WordApp.Documents.Open("C:\Documents and Settings\" & user & "\ClassJb.doc")
Set oWdApp = CreateObject("Word.Application")
Set WordDoc = oWdApp.Documents.Open("C:\Documents and Settings\" & user & "\ClassJb.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:=sPath & sName & "\" & nom & ".doc"
WordApp.Visible = False

oWdApp.Quit
ActiveDocument.Close True
WordDoc.Quit
WordApp.Quit
Next j
Application.Quit
End If

That macro run perfectly ; but , every time , he create a new folder , save it as thecuurent Zxcel file is name , then create into that folder, the word documents , each line in a new word document !! waht I suppose doing is: create a new word document and for each line of the excel file , the macro create a new page in the same word document ..

Well, you mustn't create the

Well, you mustn't create the word instance within the loop... Imagine you've got 1000 rows you will going to create 1000 Word instances !!!
Read the sample I sent you first...
See how it is implemented.

I see the "n" variable : it is not seem to be initialized.

Well, be more precis with the code and try to understand what you expect by writing the algorythm an a paper... ;o)

Here is a new sample :
Private Sub CreateWordDoc()
'---------------------------------------------------------------------------
' Procedure : CreateWordDoc
' DateTime : 23/11/2012
' Author : Argyronet
' Purpose : Create Word doc according to data cell content
'...........................................................................
' Parameters : None

' Return Codes : None
'...........................................................................
' Notice :
'---------------------------------------------------------------------------
Const WORD_DOC As String = "C:\User\#USERNAME#\My Documents\ClassJB.doc"
Dim oWordApp As Word.Application
Dim oWordDoc As Word.Document

Dim oXLWkb As Excel.Workbook
Dim oXLSht As Excel.Worksheet
Dim oXLRng As Excel.Range
Dim oXLCell As Excel.Range

Dim R As Long
Dim strData As String
Dim strBookmarkName As String

Dim strUserName As String
Dim strWordDocumentName As String

On Error GoTo L_ErrCreateWordDoc
'Get user name
strUserName = Environ("USERNAME")
strWordDocumentName = Replace(WORD_DOC, "#USERNAME#", strUserName)
'Create a new instance of Word
Set oWordApp = New Word.Application
'Set the variable to the target workbook
Set oXLWkb = ActiveWorkbook
'Set the variable to the target sheet
Set oXLSht = oXLWkb.Worksheets(1)
'Set the used range (A2:A5)
Set oXLRng = oXLSht.Range(Cells(2, 1), Cells(Cells(1, 1).End(xlDown).Row, 1))
'Create a new document
Set oWordDoc = oWordApp.Documents.Open(strWordDocumentName, False, False)
For Each oXLCell In oXLRng
'Counter
R = R + 1
'Get data and date
strData = "Data = " & oXLCell.Value & " created on " & oXLCell.Offset(0, 2).Value
'Build documen name
strBookmarkName = oXLCell.Offset(0, 1).Value
'Put data into the bookmark of the document
oWordDoc.Bookmarks(strBookmarkName).Range.text = strData
Next
'Save and close document
With oWordDoc
.Save
.Close
End With
'Quit Word
oWordApp.Quit

On Error GoTo 0
L_ExCreateWordDoc:
'Reset used memory
Set oXLRng = Nothing
Set oXLSht = Nothing
Set oXLWkb = Nothing
Set oWordDoc = Nothing
Set oWordApp = Nothing
Exit Sub

L_ErrCreateWordDoc:
MsgBox Err.Description, 48, Err.Source
Resume L_ExCreateWordDoc

End Sub

Thank you Argyronet for your

Thank you Argyronet for your help and your time.
This is the macro which I have , but I still have a problem ion a msg box : " member of the collection does not exist required"!!!

Private Sub CreateWordDoc()
'---------------------------------------------------------------------------
' Procedure : CreateWordDoc
' DateTime : 23/11/2012
' Author : Argyronet
' Purpose : Create Word doc according to data cell content
'...........................................................................
' Parameters : None

' Return Codes : None
'...........................................................................
' Notice :
'---------------------------------------------------------------------------
Const WORD_DOC As String = "C:\Documents and Settings\#USERNAME#\My Documents\ClassJb.doc"
Dim oWordApp As Word.Application
Dim oWordDoc As Word.Document

Dim oXLWkb As Excel.Workbook
Dim oXLSht As Excel.Worksheet
Dim oXLRng As Excel.Range
Dim oXLCell As Excel.Range

Dim R As Long
Dim strData As String
Dim strBookmarkName As String

Dim strUserName As String
Dim strWordDocumentName As String

On Error GoTo L_ErrCreateWordDoc
'Get user name
strUserName = Environ("USERNAME")
strWordDocumentName = Replace(WORD_DOC, "#USERNAME#", strUserName)
'Create a new instance of Word
Set oWordApp = New Word.Application
'Set the variable to the target workbook
Set oXLWkb = ActiveWorkbook
'Set the variable to the target sheet
Set oXLSht = oXLWkb.Worksheets(1)
'Set the used range (A2:A5)
Set oXLRng = oXLSht.Range(Cells(2, 1), Cells(Cells(1, 1).End(xlDown).Row, 1))
'Create a new document
Set oWordDoc = oWordApp.Documents.Open(strWordDocumentName, False, False)
For Each oXLCell In oXLRng
'Counter
R = R + 1
'Get data and date
strData = "Data = " & oXLCell.Value & " created on " & oXLCell.Offset(0, 2).Value
'Build documen name
strBookmarkName = oXLCell.Offset(0, 1).Value
'Put data into the bookmark of the document
oWordDoc.Bookmarks(strBookmarkName).Range.Text = strData
Next
'Save and close document
With oWordDoc
.Save
.Close
End With
'Quit Word
oWordApp.Quit

On Error GoTo 0
L_ExCreateWordDoc:
'Reset used memory
Set oXLRng = Nothing
Set oXLSht = Nothing
Set oXLWkb = Nothing
Set oWordDoc = Nothing
Set oWordApp = Nothing
Exit Sub

L_ErrCreateWordDoc:
MsgBox Err.Description, 48, Err.Source
Resume L_ExCreateWordDoc

End Sub