Coding Help please (1 Viewer)

M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Hi all

I am having issues with some coding and i was wondering if i am doing it right.

Here is a copy of the code that i am doing:

Code: 
Public Sub ExportToWord()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rsReports As DAO.Recordset
            
    Set wApp = New Word.Application
    Set rsReports = CurrentDb.OpenRecordset("qryReports")
    
    If Me.txtReportType = "Power" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Power Cable.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
                wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
            
                rsReports.MoveNext
            Loop
            
    If Me.txtReportType = "Control" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core COntrol Cable.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
                wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
            
                rsReports.MoveNext
            Loop
            
    If Me.txtReportType = "Earth" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Earth Test Sheet.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
               wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
                
                rsReports.MoveNext
            Loop
            
     If Me.txtReportType = "Motor" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Test Motor Test Sheet.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
                wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
            
                rsReports.MoveNext
            Loop
            
     If Me.txtReportType = "Instrument" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Instrument Test Sheet.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
                wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
            
                rsReports.MoveNext
            Loop
            
     If Me.txtReportType = "ITP" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Earth Test Sheet.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
                wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
            
                rsReports.MoveNext
            Loop
            
    If Me.txtReportType = "Single Phase" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Single Phase Power Cable.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
                wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
            
                rsReports.MoveNext
            Loop
            
     If Me.txtReportType = "Three Phase" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Three Phase Power Cable.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
                wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
            
                rsReports.MoveNext
            Loop
            
     If Me.txtReportType = "IS" Then
        Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Intrinsically Safe Cable.dotx")
        
            If Not rsReports.EOF Then rsReports.MoveFirst
            
            Do Until rsReports.EOF
                wDoc.Bookmarks("Project").Range.Text = Nz(rsReports!Project, "")
                wDoc.Bookmarks("Location").Range.Text = Nz(rsReports!Location, "")
                wDoc.Bookmarks("CableNumber").Range.Text = Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, "")
                wDoc.Bookmarks("Origin").Range.Text = Nz(rsReports!OriginZone, "")
                wDoc.Bookmarks("Dest").Range.Text = Nz(rsReports!DestinationZone, "")
                wDoc.Bookmarks("Date").Range.Text = Nz(rsReports!DateChecked, "")
                wDoc.Bookmarks("CheckedBy").Range.Text = Nz(rsReports!CheckedBy, "")
                wDoc.Bookmarks("Comments").Range.Text = Nz(rsReports!Comments, "")
                wDoc.Bookmarks("Tool").Range.Text = Nz(rsReports!Certification, "")
            
                wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(rsReports!Project, ""))
                wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(rsReports!Location, ""))
                wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(rsReports!CableType, rsReports!Prefix, rsReports!Type, ""))
                wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(rsReports!OriginZone, ""))
                wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(rsReports!DestinationZone, ""))
                wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(rsReports!Date, ""))
                wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(rsReports!CheckedBy, ""))
                wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(rsReports!Comments, ""))
                wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(rsReports!Certification, ""))
            
                rsReports.MoveNext
            Loop
            
            wDoc.Close False
            wApp.Quit
            
            Set wDoc = Nothing
            Set wApp = Nothing
            Set rsReports = Nothing

I am wondering if i am doing this right or if there is an easier way to get the information from this particular query to the different word templates.

Please help. TIA

Allan
 
MarkK

MarkK

bit cruncher
Local time
Today, 06:54
Joined
Mar 17, 2004
Messages
8,179
Is there a particular complaint you have with this code? If not, should we go looking for one? What should we be looking for, if there is a problem?
Mark
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:54
Joined
May 7, 2009
Messages
19,231
are you sure you are not saving the file after?
firstly you need to make a working copy of the template, then work and save the copy,
example:


...
...

Dim strDocx As String


strDocx = Environ("UserProfile") & "\Documents\Core Intrinsically Safe Cable.docx"
' delete the docx file if it exists
If Dir(strDocx) <> "" Then Kill (strDocx)
' open the template

Set wDoc = wApp.Documents.Open("C:\QA\Templates\Core Intrinsically Safe Cable.dotx")
' save the template as normal .docx file

Call SaveDotxAsDocx(wDoc, strDocx)
wDoc.Close False
' open the working copy of .docx file

Set wDoc = wApp.Documents.Open(strDocx)
' update the bookmarks here
'
'
' after updating save it
wDoc.Close True
Set wDoc = Nothing



Code: 
Public Sub SaveDotxAsDocx(ByRef dotx As Word.Document, sDocName As String)
    Dim oWord As Word.Application
    Dim oNewDoc As Word.Document
    Set oWord = New Word.Application
    Set oNewDoc = oWord.Documents.Add(dotx.FullName)
    oNewDoc.SaveAs2 FileName:=sDocName, fileformat:=wdFormatXMLDocument
    oNewDoc.Close
    Set oNewDoc = Nothing
    oWord.Quit
    Set oWord = Nothing
End Sub
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Hi MarkK

Is there a better way to do the code because it all revolves around what the report type is. I know it looks clunky and everything .

@arnelgp would i put that code snippet in after each loop or at the end of the code?

Would it be better if i upload a copy of the database that i am working on??

Thanks for the help guys :)
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
I hope this works properly. Here is the database
 

Attachments

  • CoreQA - Copy - Copy (2).zip
    840.5 KB · Views: 286
MarkK

MarkK

bit cruncher
Local time
Today, 06:54
Joined
Mar 17, 2004
Messages
8,179
You don't have to repeat those blocks of assignments to fields in the word doc. They are all identical, right? So you could do...
Code: 
[SIZE="1"]Public Sub ExportToWord(ReportType As String)
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim fn As String
            
    Select Case ReportType
        Case "Power"
            fn = "Core Power Cable.dotx"
        Case "Control"
            fn = "Core COntrol Cable.dotx"
        Case "Earth"
            fn = "Core Earth Test Sheet.dotx"
        Case "Motor"
            fn = "Core Test Motor Test Sheet.dotx"
        Case "Instrument"
            fn = "Core Instrument Test Sheet.dotx"
        Case "ITP"
            fn = "Core Earth Test Sheet.dotx"
        Case "Single Phase"
            fn = "Core Single Phase Power Cable.dotx"
        Case "Three Phase"
            fn = "Core Three Phase Power Cable.dotx"
        Case "IS"
            fn = "Core Intrinsically Safe Cable.dotx"
    End Select
    fn = "C:\QA\Templates\" & fn
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)
    
    With CurrentDb.OpenRecordset("qryReports")
        Do While Not .EOF
            wDoc.Bookmarks("Project").Range.Text = Nz(!Project, "")
            wDoc.Bookmarks("Location").Range.Text = Nz(!Location, "")
            wDoc.Bookmarks("CableNumber").Range.Text = Nz(!CableType, !Prefix, !Type, "")
            wDoc.Bookmarks("Origin").Range.Text = Nz(!OriginZone, "")
            wDoc.Bookmarks("Dest").Range.Text = Nz(!DestinationZone, "")
            wDoc.Bookmarks("Date").Range.Text = Nz(!DateChecked, "")
            wDoc.Bookmarks("CheckedBy").Range.Text = Nz(!CheckedBy, "")
            wDoc.Bookmarks("Comments").Range.Text = Nz(!Comments, "")
            wDoc.Bookmarks("Tool").Range.Text = Nz(!Certification, "")
        
            wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(!Project, ""))
            wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(!Location, ""))
            wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(!CableType, !Prefix, !Type, ""))
            wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(!OriginZone, ""))
            wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(!DestinationZone, ""))
            wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(!Date, ""))
            wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(!CheckedBy, ""))
            wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(!Comments, ""))
            wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(!Certification, ""))
            
            .MoveNext
        Loop
        .Close
    End With
            
    wDoc.Close True
    wApp.Quit
            
End Sub[/SIZE]
Is that the kind of thing you are looking for?
Mark
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Thanks Mark That is the type of thing i am looking for.

Can you do me a favour and have a look through that database and tell me where i can improve it as well please.

Allan

I am willing to pay as well for the help :)
 
Last edited:
MarkK

MarkK

bit cruncher
Local time
Today, 06:54
Joined
Mar 17, 2004
Messages
8,179
Prefer if you describe a particular problem you need to solve rather than me go looking for them. Maybe I find something I would change that you don't care about. Then my time is wasted.
Cheers,
Mark
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Hi again Mark

I have just put the code in place of the original code and it keeps getting an error

"Compile error: Expected variable or procedure, not module"

SHould this code go in behind a command button with reference to an OnClick scenario?

Allan
 
MarkK

MarkK

bit cruncher
Local time
Today, 06:54
Joined
Mar 17, 2004
Messages
8,179
What is the code that causes the error? Sounds like you have a module and routine in that module with the same name. Maybe you can qualify the subroutine's name, like...
Code: 
Public Sub btnPrint_Click()
    ExportToWord [COLOR="Green"]'this is the name of the module AND of the routine you want to call[/COLOR]
End Sub

Code: 
Public Sub btnPrint_Click()
    ExportToWord.ExportToWord [COLOR="Green"]'It solves it to qualify the call with ModuleName.RoutineName[/COLOR]
End Sub

A common practice here too is to prefix the module name with an "m"
hth
Mark
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Thanks Mark.

Now the smallest problem has arisen. It seems that the onclick event is not working? I have tried to test the code structure and it is not showing anything or even in task manager even opening a word document. Have i done something wrong?

Thanks again Mark

Allan
 
MarkK

MarkK

bit cruncher
Local time
Today, 06:54
Joined
Mar 17, 2004
Messages
8,179
Show the code! In the db you posted, the code I modified was in a standard module, and a standard module cannot receive a click event. Also, I modified it to receive a ReportType As String parameter. What click event do you mean? What is the code for that click event? Have you put a MsgBox in that click event to see if it runs at all? Have you modified that click event to pass the parameter the routine is expecting?
Code: 
private sub somebutton_click()
   msgbox "Working???" [COLOR="Green"]'now you know if this routine even runs on click[/COLOR]
   CallASubRoutine me.txtReportType
end sub
But->Rule of Thumb: If you post about an error in your code, POST THE CODE! :)
Mark
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Sorry for the late reply Mark but i have used the code that you have shown.
Code: 
Option Compare Database
Option Explicit

Public Sub btnPrint_Click()
ExportToWord.ExportToWord
End Sub


And

Code: 
Option Compare Database
Option Explicit

Public Sub ExportToWord(ReportType As String)
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim fn As String
            
    Select Case ReportType
        Case "Power"
            fn = "Core Power Cable.dotx"
        Case "Control"
            fn = "Core COntrol Cable.dotx"
        Case "Earth"
            fn = "Core Earth Test Sheet.dotx"
        Case "Motor"
            fn = "Core Test Motor Test Sheet.dotx"
        Case "Instrument"
            fn = "Core Instrument Test Sheet.dotx"
        Case "ITP"
            fn = "Core Earth Test Sheet.dotx"
        Case "Single Phase"
            fn = "Core Single Phase Power Cable.dotx"
        Case "Three Phase"
            fn = "Core Three Phase Power Cable.dotx"
        Case "IS"
            fn = "Core Intrinsically Safe Cable.dotx"
    End Select
    fn = "C:\QA\Templates" & fn
    
    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open(fn)
    
    With CurrentDb.OpenRecordset("qryReports")
        Do While Not .EOF
            wDoc.Bookmarks("Project").Range.Text = Nz(!Project, "")
            wDoc.Bookmarks("Location").Range.Text = Nz(!Location, "")
            wDoc.Bookmarks("CableNumber").Range.Text = Nz(!CableType, !Prefix, !Type, "")
            wDoc.Bookmarks("Origin").Range.Text = Nz(!OriginZone, "")
            wDoc.Bookmarks("Dest").Range.Text = Nz(!DestinationZone, "")
            wDoc.Bookmarks("Date").Range.Text = Nz(!DateChecked, "")
            wDoc.Bookmarks("CheckedBy").Range.Text = Nz(!CheckedBy, "")
            wDoc.Bookmarks("Comments").Range.Text = Nz(!Comments, "")
            wDoc.Bookmarks("Tool").Range.Text = Nz(!Certification, "")
        
            wDoc.Bookmarks("Project").Range.Delete wdCharacter, Len(Nz(!Project, ""))
            wDoc.Bookmarks("Location").Range.Delete wdCharacter, Len(Nz(!Location, ""))
            wDoc.Bookmarks("CableNumber").Range.Delete wdCharacter, Len(Nz(!CableType, !Prefix, !Type, ""))
            wDoc.Bookmarks("Origin").Range.Delete wdCharacter, Len(Nz(!OriginZone, ""))
            wDoc.Bookmarks("Dest").Range.Delete wdCharacter, Len(Nz(!DestinationZone, ""))
            wDoc.Bookmarks("Date").Range.Delete wdCharacter, Len(Nz(!Date, ""))
            wDoc.Bookmarks("CheckedBy").Range.Delete wdCharacter, Len(Nz(!CheckedBy, ""))
            wDoc.Bookmarks("Comments").Range.Delete wdCharacter, Len(Nz(!Comments, ""))
            wDoc.Bookmarks("Tool").Range.Delete wdCharacter, Len(Nz(!Certification, ""))
            
            .MoveNext
        Loop
        .Close
    End With
    
    wDoc.Close True
    wApp.Quit
            
End Sub

Where did I go wrong?

Thanks for your help Mark I really appreciate it

Allan
 
MarkK

MarkK

bit cruncher
Local time
Today, 06:54
Joined
Mar 17, 2004
Messages
8,179
This sub takes a parameter. You need to pass it the report type...
Code: 
Public Sub ExportToWord([COLOR="DarkRed"]ReportType[/COLOR] As String)
Where does that data come from?
Mark
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
The data for the ReportType comes from the query and that query is linked to 2 tables. (I thought that the SELECT CASE was the information that the routine needed.)

The information for the ReportType Field is populated from a drop down in the tbl_Cable_Schedule which is linked to the tbl_Reports so the end user can add new reports as they create them.

I know that my knowledge is very limited in the coding of VBA which is why i am asking for help :( :)

Thanks Mark
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Also there is a field in the Form that is called me.txtReportType where the name of the report is populated.
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
There is a field in the query that has the report type data in it. Also in the original code there was reference to the me.txtReportType as this is on the form that is used to print the different reports from the data in the recordset. I hope that this explains where the data is coming from
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Hi again Mark

I am at a loss as to where and how to create the Subroutine you mentioned.

I used the code that you had in place with the message box
Code: 
 Public Sub btnPrint_Click()
    MsgBox "are you sure you want to print to MS Word", vbOKOnly
    ExportToWord.ExportToWord
    CallASubRoutine Me.txtReportType
End Sub

When I click on the button the msgbox does not appear and I am not sure if what i did was correct at all. If i put the full name into the code it errors out with an expected = error and that is confusing me.

Thanks for all your help with this Mark. I really do appreciate it. :)

TIA

Allan
 
MarkK

MarkK

bit cruncher
Local time
Today, 06:54
Joined
Mar 17, 2004
Messages
8,179
Who wrote all that code in the database you posted? Not you?
Mark
 
M

MITSupport

Registered User.
Local time
Today, 23:54
Joined
Aug 8, 2016
Messages
46
Yes mark i wrote most of the code and googled other snippets of code. I am at a loss because this has gone deeper than I ever thought it would.
 
You must log in or register to reply here.

Similar threads

Rene vK
Solved Show progress during export to Word
Replies
14
Views
1,480
isladogs
isladogs
D
Exporting from Access to Word
Replies
14
Views
1,455
The_Doc_Man
The_Doc_Man
L
Export records to word bookmarks
Replies
7
Views
1,434
pbaldy
pbaldy
A
Export data from Access to Word
Replies
5
Views
911
Mark_
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom