469,915 Members | 2,652 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,915 developers. It's quick & easy.

Data Exporting

I am looking to export certain variables from ms access to ms word, Does
anyone have any idea how this is done. So far my function collects data from
two separate tables, and can open the word document that I need, but I am
unable to figure out how to make it write the collected data to the new word
document. Any help would be great.

Public Sub ShipmentRequest_Click()

Dim inv As Double
Dim getINV As String
Dim invNum As Double
Dim Exists As Boolean
Exists = False
inv = Me.Invoice

Me.OrderBy = "Invoice #"
Me.OrderByOn = True
DoCmd.GoToRecord acDataForm, "Sales Order Log", acLast
getINV = InputBox("Enter Invoice Number", "Shipment Request", getINV)
Loop While (Not IsNumeric(getINV)) And getINV <> ""
If getINV <> "" Then invNum = getINV
DoCmd.FindRecord invNum, acEntire, , acSearchAll, True, acCurrent
If Me.Invoice = getINV Then
Exists = True
MsgBox "Invoice Number Not Found"
End If

If Exists = True Then

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("customerdbnew")
Dim dbtotal As Integer
dbtotal = rst.Fields("ID") - 11
Dim CusNum As String
CusNum = 0
If Me.Customer__ <> "" Then
CusNum = Me.Customer__
End If
MsgBox CusNum
Dim i As Integer
i = 0
Dim PONum As String
PONum = 0
If Me.Customer_PO__ <> "" Then
PONum = Me.Customer_PO__
End If
MsgBox PONum
If CusNum = rst.Fields("Customer Number") Then
Dim BillTo As String
BillTo = " "
If rst.Fields("Bill To") <> "" Then
BillTo = rst.Fields("Bill To")
End If

Dim ShipTo As String
ShipTo = " "
If rst.Fields("Ship To") <> "" Then
ShipTo = rst.Fields("Ship To")
End If

Dim VAT As String
VAT = " "
If rst.Fields("VAT #") <> "" Then
VAT = rst.Fields("VAT #")
End If

Exit Do
i = i + 1
End If
Loop While i < dbtotal
End If

MsgBox BillTo
MsgBox ShipTo
MsgBox VAT
Dim LWordDoc As String
Dim oApp As Object
'Path to the word document
LWordDoc = "\\Backup\misos bu\Released\SALE\Sale-4004_REV_9.doc"
'Create an instance of MS Word
Set oApp = CreateObject(Class:="Word.Application")
oApp.Visible = True

'Open the Document
oApp.Documents.Open filename:=LWordDoc

End Sub
Message posted via AccessMonster.com
Nov 13 '05 #1
2 1590
It looks like you're trying to create an invoice in Word using a
template. (Boy, ain't I a sleuth?!)

This is covered really well in Access Developer's Handbook, Desktop
Edition. There's sample code you can use/incorporate into your own
application. Essentially, there's an "Invoice" recordset and an
"InvoiceDetails" recordset, and if memory serves, they're sent
individually. The Invoice stuff populates the "top" of the invoice -
customer, date, blah blah. then the second recordset is converted to a
table in Word. if you have the basics of Access down and understand at
least basic VBA, then you should have no problem with it. Well worth
the $60 or whatever they're asking. But don't take my word for it - go
to Amazon and read the reviews yourself.

Nov 13 '05 #2
If you have a list of data, then you use the number of records, and create a
table to accomodate them. If you want to position the other field freely on
the page/pages, then a template with bookmarks, will do it. Do your totals
in access, and add them in the same way. I learned most of this through
making macros using the macro maker, then adapting them into access.

To make a table,
Add this function to your modules, then modify it to suit.and call it.
Theres examples of some formatting also.
If you need some example of populating a bookmark, let me know, and i'll try
to dig it out.
Public Function OpenDoc() As Boolean 'file type is the constant value from
the document type

Dim myWord As Word.Application
Dim rst As DAO.Recordset
Dim sWordTemplate As String
Dim i As Integer
Dim mTable As Word.Table
Dim myRange As Word.Range
Dim test As Range
Set myWord = New Word.Application
myWord.Visible = True
myWord.Documents.Add "g:\Access\Development\Normal.dot"
Set myRange = myWord.ActiveDocument.Bookmarks.Item("a1").Range
myWord.ActiveDocument.Tables.Add myRange, 10, 2
With myWord.ActiveDocument.Tables(1)
.Borders.InsideLineStyle = wdLineStyleNone
.Borders.OutsideLineStyle = wdLineStyleNone
.Columns(1).Borders.OutsideLineStyle = wdLineStyleSingle
For i = 1 To 10
.Cell(i, 1).Range.Text = "Column 1, row " & i
.Cell(i, 2).Range.Text = "Column 2, row " & i
Next i

End With

If MsgBox("Save ? ", vbYesNo) = vbYes Then
myWord.ActiveDocument.SaveAs "c:\My Documents\Your.doc"
End If
Set myWord = Nothing

End Function

Gerry Abbott
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Andrew Arace | last post: by
2 posts views Thread by Andy Davis | last post: by
2 posts views Thread by Mux | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.