#1
|
|||
|
|||
Creating a Word Document in vba from an Access Function
I am trying to create a fairly simple Word Document using VBA. Also using Late binding as I don't know what version of Word my users have.
The Document has to have 3 lines of text and a table which contains data from an Access table. I have tried everything I can think of to get this to work without success. I have built this by combining a lot of 'stuff' from a variety of sources. Clearly I am doing something wrong as the code I have errors every time at the first .Selection line with error 438 Object doesn't support this property or Method. Code below, any help gratefully received. Code:
Public Function ExportToWord(sQuery, sFileName, sStationName As String, iPNumber As Integer, vPDate As Variant, Optional bOpenDocument As Boolean = False) Dim oWord As Object Dim oWordDoc As Object Dim oWordTbl As Object Dim bWordOpened As Boolean Dim db As DAO.Database Dim rs As DAO.Recordset Dim iCols As Integer Dim iRecCount As Integer Dim iFldCount As Integer Dim i As Integer Dim j As Integer Const wdPrintView = 3 Const wdWord9TableBehavior = 1 Const wdAutoFitFixed = 0 ' Const wdOrientPortrait = 0 Const wdOrientLandscape = 1 'Start Word On Error Resume Next Set oWord = GetObject("Word.Application") 'Bind to existing instance of Word If Err.Number <> 0 Then 'Could not get instance of Word, so create a new one Err.Clear On Error GoTo Error_Handler Set oWord = CreateObject("Word.application") bWordOpened = False Else 'Word was already running bWordOpened = True End If On Error GoTo Error_Handler Set oWordDoc = oWord.Documents.Add 'Create a new document With oWordDoc .PageSetup.Orientation = wdOrientLandscape .Selection.TypeText Text:="Station Name" ' - to be substituted with past value sStationName .Selection.TypeParagraph .Selection.TypeText Text:="Programme Number" '- to be substituted with past value iPNumber .Selection.TypeParagraph .Selection.TypeText Text:="Programme Date" '- to be substituted with past value vPDate .Selection.TypeParagraph End With 'Open our SQL Statement, Table, Query Set db = CurrentDb Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot) With rs If .recordCount <> 0 Then .MoveLast 'Ensure proper count iRecCount = .recordCount 'Number of records returned by the table/query .MoveFirst iFldCount = .Fields.Count 'Number of fields/columns returned by the table/query oWord.ActiveDocument.Tables.Add Range:=oWordDoc.Selection.Range, NumRows:=iRecCount + 1, NumColumns:=iFldCount, _ DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _ wdAutoFitFixed Set oWordTbl = oWordDoc.Tables(1) With oWordTbl .Style = "Table Grid" .ApplyStyleHeadingRows = True .ApplyStyleLastRow = False .ApplyStyleFirstColumn = True .ApplyStyleLastColumn = False .ApplyStyleRowBands = True .ApplyStyleColumnBands = False .Borders.Enable = True 'Build our Header Row For i = 0 To iFldCount - 1 .Cell(1, i + 1) = rs.Fields(i).Name Next i 'Build our data rows For i = 1 To iRecCount For j = 0 To iFldCount - 1 .Cell(i + 1, j + 1) = Nz(rs.Fields(j).Value, "") Next j rs.MoveNext Next i End With Else MsgBox "There are no records returned by the specified queries/SQL statement.", _ vbCritical + vbOKOnly, "No data to generate an Word spreadsheet with" GoTo Error_Handler_Exit End If End With oWordDoc.SaveAs (sFileName) 'Save and close oWord.Visible = True If bOpenDocument = False Then oWordDoc.Close 'Close Word if is wasn't originally running If bWordOpened = False Then oWord.Quit End If End If Error_Handler_Exit: On Error Resume Next rs.Close Set rs = Nothing Set db = Nothing oWord.Visible = True 'Make Word visible to the user Set oWordTbl = Nothing Set oWordDoc = Nothing Set oWord = Nothing Exit Function Error_Handler: If Err.Number = 5148 Then MsgBox "Your Table/Query contains a total of " & iFldCount & " fields/columns, but Word tables can only support a maximum of 63. " & _ "Please change your Table/Query to only supply a maximum of 63 fields/columns and try again.", _ vbCritical Or vbOKOnly, "Operation Aborted" Else MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: ExportToWord" & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occurred!" End If Resume Error_Handler_Exit End Function |
#2
|
||||
|
||||
Don't use a selection object for this. Assuming there is no initial content in the new doc you could remove those pesky .Selection lines and replace it with
.Range.Text = "Station Name" & vbCr & "Programme Number" & vbCr & "Programme Date" & vbCr
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Creating a Word Document in vba from an Access Function
Thank you for your reply. I have tried this route before however when the Document is produced the text moves to the bottom of the page. I guess I need to do 'something' to position the table on the page but I have no idea what?
I've attached an image of the current result having replaced the .selection coding with:- Code:
With oWordDoc .PageSetup.Orientation = wdOrientLandscape .Range.Text = "Station Name" & vbCr & "Programme Number" & vbCr & "Programme Date" & vbCr End With |
#4
|
||||
|
||||
Which simply means you need to specify where the table gets added instead of using the Selection object again
Code:
oWordDoc.Tables.Add Range:=oWordDoc.Paragraphs.Last.Range, NumRows:=iRecCount + 1, NumColumns:=iFldCount, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
Wonderful, thank you. I really MUST spend some time learning about how to use the Word Object Model.
Best wishes |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to Open a Word Document from ACCESS VBA | PosseJohn | Word VBA | 3 | 12-06-2013 04:33 PM |
Creating a graph for Future Value function (FV function) | bmoody | Excel | 2 | 11-06-2013 10:52 AM |
importing Access data to a Word document | WayneCusack | Word VBA | 2 | 12-24-2012 12:26 AM |
Access to Word, Creating a list from multiple records | daymaker | Mail Merge | 9 | 03-14-2012 06:37 AM |
Protecting Word Document by restricting access permissions! | user | Word | 0 | 11-20-2008 01:21 PM |