rouxjean
asked on
Exporting to Word
Hi !
I'd like to know what would be my best bet when trying to export data from a table and/or query to a Word document.
Be aware that the exportation would be like an order, with an order header stored in one table and an order-details with multiple lines (records) stored in another table. Both are shown in an order form/subform. Word bookmarks are only good for the header ! I need help to export both the header and details ! Code needed !
Thanks
I'd like to know what would be my best bet when trying to export data from a table and/or query to a Word document.
Be aware that the exportation would be like an order, with an order header stored in one table and an order-details with multiple lines (records) stored in another table. Both are shown in an order form/subform. Word bookmarks are only good for the header ! I need help to export both the header and details ! Code needed !
Thanks
ASKER
#1 How can I do this when I don't know how many records there will be in the details ??? The details will have to fit in a table.This looks more suitable for the header. I understand this would be the bookmark option.
#2 Too much coding involved....No template set and each time I want to modify the format, i'll have to play in the code. I'd really like a word template in which I can put my data. This way, even if I modify the word template, everything is still ok !
#3 As a last option....
So what would be best for me ? I really like the practical side of a Word template...but it is my understanding that I won't be able to export the details using this method...
What do you suggest ?
#2 Too much coding involved....No template set and each time I want to modify the format, i'll have to play in the code. I'd really like a word template in which I can put my data. This way, even if I modify the word template, everything is still ok !
#3 As a last option....
So what would be best for me ? I really like the practical side of a Word template...but it is my understanding that I won't be able to export the details using this method...
What do you suggest ?
2) Is the best option if you need serious control over how it looks. However...
Have you considered creating an Access report and exporting to RTF?
Have you considered creating an Access report and exporting to RTF?
ASKER
shanesuebsahakarn: yes but I was afraid it wouldn't look exactly as the report..Am I wrong ?
It wouldn't, no, if you use the default Access export. You can, however, use this:
http://www.lebans.com/ReportUtilities.htm
This will preserve the formatting.
http://www.lebans.com/ReportUtilities.htm
This will preserve the formatting.
Can you elaborate a bit more on what the output will look like. I am assuming it is something along the lines of
Header Stuff Goes here
--------------------------
item
--------------------------
item
--------------------------
item
--------------------------
Footer Stuff goes here
Where the number of item rows is variable. If so what sort of limits are we looking at here - could this potentially run for a number of pages?
Option 2 may not turn out to be that maintenance unfriendly compared to some of the other options.
Header Stuff Goes here
--------------------------
item
--------------------------
item
--------------------------
item
--------------------------
Footer Stuff goes here
Where the number of item rows is variable. If so what sort of limits are we looking at here - could this potentially run for a number of pages?
Option 2 may not turn out to be that maintenance unfriendly compared to some of the other options.
ASKER
julianH: exactly the layout I need :-) I really like the practical side of a Word template but if I don't have a choice I'll take option 2..but this is lines and lines of code hehe. It could potentially goes on 2 pages or more
Thanks
Thanks
ASKER
Here is what I use: form fields for header and footer. In the middle I try to write a table by code...everything works fine except the table....it just doesn't write a table, just plain text...can you help What I do is that I have included an empty table in the word template that I copy and paste and then try to fill them up. ? Code involved starts on this line :
Set rs = Me.subDetails.Form.Records
==========================
Private Sub cmdExport_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rsModel As ADODB.Recordset
Dim rsContactDetails As ADODB.Recordset
Dim strSQLModel As String
Dim strSQLContactDetails As String
Dim msgA
Dim strDocName As String
Dim blnContactDetailsID As Boolean
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Const DOC_PATH As String = "C:\access\"
Const DOC_NAME As String = "model.dot"
On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
Set appWord = New Word.Application
Err = 0
End If
Set doc = appWord.Documents(DOC_NAME
If Err = 0 Then
If MsgBox("Do you want to save the current document " _
& "before updating the data?", vbYesNo) = vbYes Then
appWord.Dialogs(wdDialogFi
End If
doc.Close False
End If
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Quote-Header] WHERE ((([Quote-Header].[No])=" & Me.Quote_Number & "))"
Set rs = dbs.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then 'Aucun client trouvé
MsgBox "Customer Not Found"
Else
rs.MoveLast 'Client trouvé
rs.MoveFirst
Set doc = appWord.Documents.Open(DOC
With doc
.FormFields("QuoteNo").Res
.FormFields("Date").Result
'.FormFields("To").Result = Nz(rs![No], 0)
.FormFields("BillToNo").Re
.FormFields("BillToName").
.FormFields("BillToAddress
.FormFields("BillToAddress
.FormFields("BillToPhone")
.FormFields("BillToFax").R
.FormFields("ShipToNo").Re
.FormFields("ShipToName").
.FormFields("ShipToAddress
.FormFields("ShipToAddress
.FormFields("ShipToPhone")
.FormFields("ShipToFax").R
.FormFields("Attention").R
.FormFields("FOB").Result = Trim(rs![FOB])
.FormFields("Terms").Resul
.FormFields("Remarks").Res
End With
rs.Close
Set rs = Nothing
strSQL = "SELECT [Quote-Header].[No], AGENT.AGENT_NAME, AGENT.AGENT_LOGIN, AGENT.AGENT_EXTENSION FROM AGENT RIGHT JOIN [Quote-Header] ON AGENT.AGENT_CODE = [Quote-Header].User WHERE ((([Quote-Header].[No])=" & Me.Quote_Number & "))"
Set rs = dbs.OpenRecordset(strSQL)
With doc
.FormFields("AgentName").R
.FormFields("AgentExtensio
.FormFields("AgentLogin").
End With
rs.Close
Set rs = Nothing
Set rs = Me.subDetails.Form.Records
doc.FormFields("formfield1
appWord.Selection.MoveDown
Do Until rs.EOF
With appWord
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.Tables(1).Selec
.Selection.Copy
.Selection.Paste
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.MoveUp Unit:=wdLine, Count:=2
.Selection.TypeText TEXT:="title"
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.TypeText TEXT:="size"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="upc"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="qty"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="pack.qty"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="weight"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="price"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="min.qty"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="delivery"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="notes"
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.TypeParagraph
End With
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
appWord.Visible = True
appWord.Activate
End If
Set doc = Nothing
Set appWord = Nothing
Exit Sub
==========================
Thanks
Set rs = Me.subDetails.Form.Records
==========================
Private Sub cmdExport_Click()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rsModel As ADODB.Recordset
Dim rsContactDetails As ADODB.Recordset
Dim strSQLModel As String
Dim strSQLContactDetails As String
Dim msgA
Dim strDocName As String
Dim blnContactDetailsID As Boolean
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Const DOC_PATH As String = "C:\access\"
Const DOC_NAME As String = "model.dot"
On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
Set appWord = New Word.Application
Err = 0
End If
Set doc = appWord.Documents(DOC_NAME
If Err = 0 Then
If MsgBox("Do you want to save the current document " _
& "before updating the data?", vbYesNo) = vbYes Then
appWord.Dialogs(wdDialogFi
End If
doc.Close False
End If
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Quote-Header] WHERE ((([Quote-Header].[No])=" & Me.Quote_Number & "))"
Set rs = dbs.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then 'Aucun client trouvé
MsgBox "Customer Not Found"
Else
rs.MoveLast 'Client trouvé
rs.MoveFirst
Set doc = appWord.Documents.Open(DOC
With doc
.FormFields("QuoteNo").Res
.FormFields("Date").Result
'.FormFields("To").Result = Nz(rs![No], 0)
.FormFields("BillToNo").Re
.FormFields("BillToName").
.FormFields("BillToAddress
.FormFields("BillToAddress
.FormFields("BillToPhone")
.FormFields("BillToFax").R
.FormFields("ShipToNo").Re
.FormFields("ShipToName").
.FormFields("ShipToAddress
.FormFields("ShipToAddress
.FormFields("ShipToPhone")
.FormFields("ShipToFax").R
.FormFields("Attention").R
.FormFields("FOB").Result = Trim(rs![FOB])
.FormFields("Terms").Resul
.FormFields("Remarks").Res
End With
rs.Close
Set rs = Nothing
strSQL = "SELECT [Quote-Header].[No], AGENT.AGENT_NAME, AGENT.AGENT_LOGIN, AGENT.AGENT_EXTENSION FROM AGENT RIGHT JOIN [Quote-Header] ON AGENT.AGENT_CODE = [Quote-Header].User WHERE ((([Quote-Header].[No])=" & Me.Quote_Number & "))"
Set rs = dbs.OpenRecordset(strSQL)
With doc
.FormFields("AgentName").R
.FormFields("AgentExtensio
.FormFields("AgentLogin").
End With
rs.Close
Set rs = Nothing
Set rs = Me.subDetails.Form.Records
doc.FormFields("formfield1
appWord.Selection.MoveDown
Do Until rs.EOF
With appWord
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.Tables(1).Selec
.Selection.Copy
.Selection.Paste
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.MoveUp Unit:=wdLine, Count:=2
.Selection.TypeText TEXT:="title"
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.TypeText TEXT:="size"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="upc"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="qty"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="pack.qty"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="weight"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="price"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="min.qty"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="delivery"
.Selection.MoveRight Unit:=wdCharacter, Count:=1
.Selection.TypeText TEXT:="notes"
.Selection.MoveDown Unit:=wdLine, Count:=1
.Selection.TypeParagraph
End With
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
appWord.Visible = True
appWord.Activate
End If
Set doc = Nothing
Set appWord = Nothing
Exit Sub
==========================
Thanks
>> I really like the practical side of a Word template but if I don't have a choice I'll take option 2..but this is lines and lines of code hehe. It could potentially goes on 2 pages or more
Not really - you need code for the header
1 section of code for a line item
and code for the footer.
The line items are contained in a loop so you only need to implement code for
code for Header Stuff Goes here
--------------------------
code for item <----- Create n items using a loop
--------------------------
code for Footer Stuff goes here
You might even be able to do a solution where you create a word template with bookmarks and then use code to cut and paste item rows that link back to the database. Haven't actually done this but it may be an option.
Not really - you need code for the header
1 section of code for a line item
and code for the footer.
The line items are contained in a loop so you only need to implement code for
code for Header Stuff Goes here
--------------------------
code for item <----- Create n items using a loop
--------------------------
code for Footer Stuff goes here
You might even be able to do a solution where you create a word template with bookmarks and then use code to cut and paste item rows that link back to the database. Haven't actually done this but it may be an option.
ASKER
julianH: I know....this is what I tried to do with my last code posted. However I can't insert a table...only plain text is processed so either way aren't working !
Ok - missed your post with the code - you must have posted it after I accessed the page last.
I will look at the code and see if I spot anything useful.
I will look at the code and see if I spot anything useful.
This might help. The code below is a VBScript implementation of a simplified application I wrote a couple years back that is similar to what you want to do.
It will run on the command line with
cscript filename.vbs
or
wscript filenam.vbs
where filename.vbs is the name of the file you copy and paste it to.
Hope you find it useful
--------- CUT HERE -------------
set wrd = CreateObject ( "Word.Application")
wrd.Visible = True
set doc = wrd.Documents.Add ()
doc.SaveAs "test.doc"
' Header stuff goes here
set rng = doc.Range(0, 0 )
' For Demonstration purposes only - modify based on the results of your recordset
StillRowsToPrint = 4
ColsToPrint = 4
RowsToPrint = 0
MaxRowsPerPage = 10
while StillRowsToPrint > 0
' Put logic here to work out if table will fit on a page
' If not then create table with max rows for the page and decrement StillRowsToPrint by the num rows on the page
if StillRowsToPrint > MaxRowsPerPage then
RowsToPrint = MaxRowsPerPage
StillRowsToPrint = StillRowsToPrint - MaxRowsPerPage
else
RowsToPrint = StillRowsToPrint
StillRowsToPrint = 0
end if
doc.tables.add rng, RowsToPrint, ColsToPrint
'Modify to suite your needs ...
for i = 1 to 4
with wrd.Selection
.TypeText "Row " & i & " Coloumn1"
.MoveRight
.TypeText "Row " & i & " Coloumn2"
.MoveRight
.TypeText "Row " & i & " Coloumn3"
.MoveRight
.TypeText "Row " & i & " Coloumn4"
.MoveRight
end with
next
' Create the page break for the next table
wrd.Selection.MoveDown
wrd.Selection.MoveDown
wrd.Selection.TypeParagrap
wrd.Selection.InsertBreak 7
wend
'Footer stuff goes here
doc.Save
--------- CUT HERE -------------
It will run on the command line with
cscript filename.vbs
or
wscript filenam.vbs
where filename.vbs is the name of the file you copy and paste it to.
Hope you find it useful
--------- CUT HERE -------------
set wrd = CreateObject ( "Word.Application")
wrd.Visible = True
set doc = wrd.Documents.Add ()
doc.SaveAs "test.doc"
' Header stuff goes here
set rng = doc.Range(0, 0 )
' For Demonstration purposes only - modify based on the results of your recordset
StillRowsToPrint = 4
ColsToPrint = 4
RowsToPrint = 0
MaxRowsPerPage = 10
while StillRowsToPrint > 0
' Put logic here to work out if table will fit on a page
' If not then create table with max rows for the page and decrement StillRowsToPrint by the num rows on the page
if StillRowsToPrint > MaxRowsPerPage then
RowsToPrint = MaxRowsPerPage
StillRowsToPrint = StillRowsToPrint - MaxRowsPerPage
else
RowsToPrint = StillRowsToPrint
StillRowsToPrint = 0
end if
doc.tables.add rng, RowsToPrint, ColsToPrint
'Modify to suite your needs ...
for i = 1 to 4
with wrd.Selection
.TypeText "Row " & i & " Coloumn1"
.MoveRight
.TypeText "Row " & i & " Coloumn2"
.MoveRight
.TypeText "Row " & i & " Coloumn3"
.MoveRight
.TypeText "Row " & i & " Coloumn4"
.MoveRight
end with
next
' Create the page break for the next table
wrd.Selection.MoveDown
wrd.Selection.MoveDown
wrd.Selection.TypeParagrap
wrd.Selection.InsertBreak 7
wend
'Footer stuff goes here
doc.Save
--------- CUT HERE -------------
ASKER
Hi thanks for your reply.
It crash on this line : .TypeText "Row " & i & " Coloumn1"
It says action impossible on last line !
We're getting closer now :-)
It crash on this line : .TypeText "Row " & i & " Coloumn1"
It says action impossible on last line !
We're getting closer now :-)
ASKER
I also need to set the width of each column and can't really use :
Selection.Tables(1).Column
since I have many tables and tables(1) refers to the wrong one !!!
Selection.Tables(1).Column
since I have many tables and tables(1) refers to the wrong one !!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It throws an error: 5251, It says action impossible on last line !
Any ideas ? I'm using Office 97...
Any ideas ? I'm using Office 97...
ASKER
I think that once it is in column4, it doesn't move back to column1 once the loop change records...so it tries to move right of something inexistant...wouldn't know how to fix though...
Office 97!!
Ok, well that explains it then. Is there a reason it has to be Office 97 instead of Office 2000 or Office 2003?
Ok, well that explains it then. Is there a reason it has to be Office 97 instead of Office 2000 or Office 2003?
ASKER
Unfortunately, yes...the office where I work don't want to upgrade..
gngngngn.
Oh well - unfortunately I don't have access to Office 97 so I can't help you there. You might want to save your self some problems and explain to the powers that be that if they want advanced functionality they must acquire a tool capable of doing the job.
If I think of anything I will post back.
Oh well - unfortunately I don't have access to Office 97 so I can't help you there. You might want to save your self some problems and explain to the powers that be that if they want advanced functionality they must acquire a tool capable of doing the job.
If I think of anything I will post back.
ASKER
OK, well thanks...
Thanks for the points - sorry I could not give you a solution.
Just as a matter of interest - does the output have to be a word document - can you not use HTML to output your reports?
I mention it only because internally we have recently moved from Word docs to HTML docs because it gives us greater options. Actually we are creating MHT files - they include all the graphics inside the same file as the HTML so you can put together pretty neat reports.
I am still looking on this one - let you know if I find anything.
Just as a matter of interest - does the output have to be a word document - can you not use HTML to output your reports?
I mention it only because internally we have recently moved from Word docs to HTML docs because it gives us greater options. Actually we are creating MHT files - they include all the graphics inside the same file as the HTML so you can put together pretty neat reports.
I am still looking on this one - let you know if I find anything.
1. You create a word document that "pulls" the data from access. You do this by setting up a template that is linked to a database. Word automaticaly will pull the data into the document for you. This may not suite your needs
2. You create the document dynamically using the Word application object. Access retains control and dynamically generates the document inserting data from the tables where needed.
3. You could include a Word Macro that pulls the data from access and populates a document - you would use this if option 1 cannot easily solve the problem.
It is impractical to post code for all three options - rather post back which option you think would best suit your requirements and we can work from there.