Select Case And Exporting Data To Word Template

[ChrisCione]

How do I combine this code (which opens a specific Word fillable form based on a combo box selection):

Select Case Me.ComboWord
Case Is = "ERS"
Application.FollowHyperlink "L:\Bper Spec\Cione\Database Files\Announcement (ERS NonRep BroadBand).doc"
Case Is = "Open"
Application.FollowHyperlink "L:\Bper Spec\Cione\Database Files\Announcement (OPEN NonRep BroadBand).doc"
Case Else
' do nothing
End Select


with this code (which exports data into corresponding form fields in a Word template)?

Private Sub CommandERSNRBB_Click()
'Download to ERS NonRep Broadband.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn’t open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn’t open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set doc = appWord.Documents.Open("L:\Bper Spec\Cione\Database Files\Announcement (ERS NonRep BroadBand).doc", , True)
With doc
.FormFields("Specialist").Result = Me!Specialist
.FormFields("Classification").Result = Me!Classification
.FormFields("ClassCode").Result = Me!ClassCode
.FormFields("JobAnnoID").Result = Me!JobAnnoID
.FormFields("JobAnnoCode").Result = Me!JobAnnoCode
.FormFields("Cert").Result = Me!CertNumber
.FormFields("PositionNumber").Result = Me!PositionNumber .Visible = True
.Activate
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub <!--IBF.ATTACHMENT_2062125-->

 

[boblarson]

So you have the same fields but are wanting to open the appropriate document based on the combo selection and then fill in the fields?

If so, it would be something like this:

Function ExportToDoc(frm As Form)
'Download to Document
    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim strDocName As String
    'Set appWord object variable to new instance of Word.
    Set appWord = CreateObject(, "Word.Application")
    appWord.Visible = True
 
    ' check to see what has been selected.  I would suggest storing the file paths
    ' in a table and using a DLookup instead to get the appropriate path
    ' that way you don't need to modify code if anything changes, just make
    ' changes in the table.
    Select Case frm.ComboWord
    Case "ERS"
        strDocName = "L:\Bper Spec\Cione\Database Files\Announcement (ERS NonRep BroadBand).doc"
    Case "Open"
        strDocName = "L:\Bper Spec\Cione\Database Files\Announcement (OPEN NonRep BroadBand).doc"
    Case Else
        ' message box just in case a selection happens that hasn't been coded for
        MsgBox "Selection has not been set up.  Please contact your administrator for assistance.", vbExclamation, "Selection Error"
    End Select
 
    Set doc = appWord.Documents.Open(strDocName)
    With doc
        .FormFields("Specialist").Result = Me!Specialist
        .FormFields("Classification").Result = Me!Classification
        .FormFields("ClassCode").Result = Me!ClassCode
        .FormFields("JobAnnoID").Result = Me!JobAnnoID
        .FormFields("JobAnnoCode").Result = Me!JobAnnoCode
        .FormFields("Cert").Result = Me!CertNumber
        .FormFields("PositionNumber").Result = (frm.PositionNumber.Visible = True)
        .Activate
    End With
    
    appWord.UserControl = True
 
    Set doc = Nothing
    Set appWord = Nothing
Exit_ExportToDoc:
    Exit Function
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_ExportToDoc
    Resume
 
End Function

By the way I modified your Select Case as you just need to use

Case "Something"

instead of

Case Is = "Something"

when using strings

And same with numbers:

Case 9, 10, 32, 32

Case 2 To 4, 7, 9, 12 To 15, 22

And I also suggested using a table for the file paths instead because it would make maintenance easier and no code changes required.

Also, I just create a new instance of Word instead of worrying about getting an existing instance. By doing so I avoid all of the extra error handling and also don't mess up something else that someone is doing because I am using a separate instance.

I also turned this into a function you can put into a Standard Module and then you can just call it from your form and pass the form to it like:

Call ExportToDoc(Me)

Also, I used

appWord.UserControl = True

So it doesn't kill what you've just done. If you kill the word app and doc before saving the form fields aren't going to be saved.
Hope that this helps.

 

[ChrisCione]

Thanks. I'll plug it in and see what happens.

So you have the same fields but are wanting to open the appropriate document based on the combo selection and then fill in the fields?

If so, it would be something like this:

Function ExportToDoc(frm As Form)
'Download to Document
    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim strDocName As String
    'Set appWord object variable to new instance of Word.
    Set appWord = CreateObject(, "Word.Application")
    appWord.Visible = True
 
    ' check to see what has been selected.  I would suggest storing the file paths
    ' in a table and using a DLookup instead to get the appropriate path
    ' that way you don't need to modify code if anything changes, just make
    ' changes in the table.
    Select Case frm.ComboWord
    Case "ERS"
        strDocName = "L:\Bper Spec\Cione\Database Files\Announcement (ERS NonRep BroadBand).doc"
    Case "Open"
        strDocName = "L:\Bper Spec\Cione\Database Files\Announcement (OPEN NonRep BroadBand).doc"
    Case Else
        ' message box just in case a selection happens that hasn't been coded for
        MsgBox "Selection has not been set up.  Please contact your administrator for assistance.", vbExclamation, "Selection Error"
    End Select
 
    Set doc = appWord.Documents.Open(strDocName)
    With doc
        .FormFields("Specialist").Result = Me!Specialist
        .FormFields("Classification").Result = Me!Classification
        .FormFields("ClassCode").Result = Me!ClassCode
        .FormFields("JobAnnoID").Result = Me!JobAnnoID
        .FormFields("JobAnnoCode").Result = Me!JobAnnoCode
        .FormFields("Cert").Result = Me!CertNumber
        .FormFields("PositionNumber").Result = (frm.PositionNumber.Visible = True)
        .Activate
    End With
 
   appWord.UserControl = True
 
    Set doc = Nothing
    Set appWord = Nothing
Exit_ExportToDoc:
    Exit Function
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_ExportToDoc
    Resume
 
End Function

By the way I modified your Select Case as you just need to use

Case "Something"

instead of

Case Is = "Something"

when using strings

And same with numbers:

Case 9, 10, 32, 32

Case 2 To 4, 7, 9, 12 To 15, 22

And I also suggested using a table for the file paths instead because it would make maintenance easier and no code changes required.

Also, I just create a new instance of Word instead of worrying about getting an existing instance. By doing so I avoid all of the extra error handling and also don't mess up something else that someone is doing because I am using a separate instance.

I also turned this into a function you can put into a Standard Module and then you can just call it from your form and pass the form to it like:

Call ExportToDoc(Me)

Also, I used

appWord.UserControl = True

So it doesn't kill what you've just done. If you kill the word app and doc before saving the form fields aren't going to be saved.
Hope that this helps.

 

[ChrisCione]

Thanks for your help.