Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-16-2018, 08:32 PM   #1
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Coding Help please

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

MITSupport is offline   Reply With Quote
Old 02-16-2018, 10:51 PM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-16-2018, 11:34 PM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,712
Thanks: 55
Thanked 2,133 Times in 2,044 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Coding Help please

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 02-18-2018, 01:43 PM   #4
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

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
MITSupport is offline   Reply With Quote
Old 02-18-2018, 01:45 PM   #5
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

I hope this works properly. Here is the database
Attached Files
File Type: zip CoreQA - Copy - Copy (2).zip (840.5 KB, 16 views)
MITSupport is offline   Reply With Quote
Old 02-18-2018, 02:21 PM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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:
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
Is that the kind of thing you are looking for?
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-18-2018, 02:33 PM   #7
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

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 by MITSupport; 02-18-2018 at 02:39 PM.
MITSupport is offline   Reply With Quote
Old 02-18-2018, 02:45 PM   #8
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-18-2018, 02:58 PM   #9
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

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
MITSupport is offline   Reply With Quote
Old 02-18-2018, 03:37 PM   #10
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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 'this is the name of the module AND of the routine you want to call
End Sub
Code:
Public Sub btnPrint_Click()
    ExportToWord.ExportToWord 'It solves it to qualify the call with ModuleName.RoutineName
End Sub
A common practice here too is to prefix the module name with an "m"
hth
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-18-2018, 04:01 PM   #11
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

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
MITSupport is offline   Reply With Quote
Old 02-18-2018, 04:18 PM   #12
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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???" 'now you know if this routine even runs on click
   CallASubRoutine me.txtReportType
end sub
But->Rule of Thumb: If you post about an error in your code, POST THE CODE!
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-18-2018, 08:35 PM   #13
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

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
MITSupport is offline   Reply With Quote
Old 02-18-2018, 08:47 PM   #14
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
This sub takes a parameter. You need to pass it the report type...
Code:
Public Sub ExportToWord(ReportType As String)
Where does that data come from?
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 02-18-2018, 09:22 PM   #15
MITSupport
Newly Registered User
 
Join Date: Aug 2016
Posts: 46
Thanks: 2
Thanked 0 Times in 0 Posts
MITSupport is on a distinguished road
Re: Coding Help please

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

MITSupport is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Coding Prayder Modules & VBA 4 03-28-2014 03:23 PM
Need some help with coding Marinus Forms 10 03-22-2011 04:47 AM
Coding help please... WSC Modules & VBA 2 11-20-2006 10:53 AM
Which is a better way of coding? yhgtbfk General 6 10-18-2004 05:21 AM
VBA Coding StefanSch Forms 7 04-14-2003 08:52 AM




All times are GMT -8. The time now is 03:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World