Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-12-2020, 11:39 AM
jcc285 jcc285 is offline Creating a Word Document in vba from an Access Function Windows 7 64bit Creating a Word Document in vba from an Access Function Office 2010 64bit
Novice
Creating a Word Document in vba from an Access Function
 
Join Date: Aug 2014
Location: UK
Posts: 4
jcc285 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-12-2020, 04:03 PM
Guessed's Avatar
Guessed Guessed is offline Creating a Word Document in vba from an Access Function Windows 10 Creating a Word Document in vba from an Access Function Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 05-13-2020, 01:19 AM
jcc285 jcc285 is offline Creating a Word Document in vba from an Access Function Windows 10 Creating a Word Document in vba from an Access Function Office 2016
Novice
Creating a Word Document in vba from an Access Function
 
Join Date: Aug 2014
Location: UK
Posts: 4
jcc285 is on a distinguished road
Default 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
Attached Images
File Type: jpg Heading at bottom.jpg (115.3 KB, 11 views)
Reply With Quote
  #4  
Old 05-13-2020, 01:43 AM
Guessed's Avatar
Guessed Guessed is offline Creating a Word Document in vba from an Access Function Windows 10 Creating a Word Document in vba from an Access Function Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #5  
Old 05-13-2020, 02:44 AM
jcc285 jcc285 is offline Creating a Word Document in vba from an Access Function Windows 10 Creating a Word Document in vba from an Access Function Office 2016
Novice
Creating a Word Document in vba from an Access Function
 
Join Date: Aug 2014
Location: UK
Posts: 4
jcc285 is on a distinguished road
Default

Wonderful, thank you. I really MUST spend some time learning about how to use the Word Object Model.

Best wishes
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a Word Document in vba from an Access Function 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
Creating a Word Document in vba from an Access Function 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft