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