Word Merge (1 Viewer)

mmchaley · Oct 3, 2019

Hello all,

I have a bit of code that runs a query to generate table with a single record from the form then opens up my word template which has the merge fields already set.

The problem is I then have to run through the mail merge settings in Word.

Code:

Private Sub GSIITB_Click()
    DoCmd.OpenQuery "qry_ITBData", acViewNormal, acEdit
    
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then 'Word isn't already running
    Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wdDoc = wdApp.Documents.Open("R:\Estimating Process\03 - New Task Order\ITB - GSI.dotx")

    wdApp.Visible = True
    DoCmd.RunCommand acCmdAppMinimize

End Sub

If I use the Word Merge button in Access, I have to go through windows explorer to get to the word template I need.

I am looking for a bit of code that when I click my command button, it runs the make table query, word merge, and opens the word file

Thanks,
Mark

theDBguy · Oct 3, 2019

Hi Mark. Have you seen this?

mmchaley · Oct 3, 2019

I have, I am looking for a single button solution as I am never sure exactly who will be utilizing the database front end. I don't want to run into a scenario where they don't have the word add-on installed.

Thank you though.
Mark

theDBguy · Oct 3, 2019

mmchaley said:
I have, I am looking for a single button solution as I am never sure exactly who will be utilizing the database front end. I don't want to run into a scenario where they don't have the word add-on installed.

Thank you though.
Mark

Hmm, "add on?" Which add on?

isladogs · Oct 4, 2019

I've used a modified version of Albert Kallal's super easy mail merge for over 10 years. Just import the entire module to your app and run it using one line of code. There is no 'add-on' and all the work is done in Access

mmchaley · Oct 4, 2019

I was using add-on when i should have used module.

I will delve more into this tomorrow. I will look at your Modified version. The version off his site is not set up for a 64 bit environment. I do not know enough to know what changes to make.

Thanks,
Mark

theDBguy · Oct 4, 2019

mmchaley said:
I was using add-on when i should have used module.

Hi. If you want a button to do all the work for you, then you'll need some VBA, whether it's in a module or not. Either way, you will have to make sure it's available to all your users if you want them to use the same button as well. So, I don't see any problems using Albert's sample.

mmchaley · Oct 4, 2019

I realize I need some VBA - I have been trying to find the VBA that gets me to the same thing as clicking the Word Merge button gets me.

theDBguy · Oct 4, 2019

mmchaley said:
I realize I need some VBA - I have been trying to find the VBA that gets me to the same thing as clicking the Word Merge button gets me.

Hi. Doesn't the VBA from Albert's sample do that?

isladogs · Oct 4, 2019

mmchaley said:
I realize I need some VBA - I have been trying to find the VBA that gets me to the same thing as clicking the Word Merge button gets me.

As I wrote in post #5 referring to Albert Kallal's merge code

isladogs said:
Just import the entire module to your app and run it using one line of code.

What else do you need?

mmchaley · Oct 4, 2019

I tried using Albert's code, I get an error that it does not work in a 64 bit system

isladogs · Oct 4, 2019

Hmm - I see nothing in his code that requires changing for 64-bit as it has no API declarations

Attached is the modWordcode module that I've used for many years and none of my clients have reported issues with it in 64-bit Access.
I can't recall having to make changes for 64-bit compatibility when my clients converted back in 2012 or so

Before you use it, first check your references & fix any marked MISSING.
Also compile your app from the VBE and fix any errors.

Then unzip and import the entire module to the VBE using Import File in place of the module you have now. You will still need Albert's forms etc

If this also fails, please take a screenshot of the error(s) and check with line(s) it fails on.

mmchaley · Oct 4, 2019

A bit more coffee this morning and google - I am getting this sorted out

To get it to function in 64 bit - I had to add PtrSafe between Declare and Function

Thanks all

isladogs · Oct 4, 2019

mmchaley said:
To get it to function in 64 bit - I had to add PtrSafe between Declare and Function

Well that comment completely threw me as there are no API declarations in albert's code in the version I use

So I downloaded the code from the link and sure enough he has included one API in the newer(?) code.
If any of your users still run A2007 or earlier you will need conditional compilation

Code:

#If VBA7 Then
   'code for A2010 or later (32/64-bit)
  [B] Declare PtrSafe Function GetTempPath Lib "kernel32" Alias "GetTempPathA" 
    (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long[/B]
#Else
'for A2007 or earlier
     Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" 
    (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
#End If

If all users have more recent versions, you only need the part in bold above

Note that many API declarations will need other changes to be 64-bit compatible including changing all handles/pointers such as hWnd from Long to LongPtr

Also, in future it would help if you provided the code causing problems and the solution when fixed

mmchaley · Oct 10, 2019

I am 95% there - the merge is occurring. What is happening is when i am merging a hyperlink it is appending a # at the beginning of the hyperlink

My code starts as
{ HYPERLINK "{MERGEFIELD LinktoDocuments}" }

After the merge it becomes
{ HYPERLINK "#HTTPS:\rest of the url" }

For some reason I can't determine it is adding a # to the start of the hyperlink.

Thoughts?

Thanks much
Mark

theDBguy · Oct 10, 2019

mmchaley said:
I am 95% there - the merge is occurring. What is happening is when i am merging a hyperlink it is appending a # at the beginning of the hyperlink

My code starts as
{ HYPERLINK "{MERGEFIELD LinktoDocuments}" }

After the merge it becomes
{ HYPERLINK "#HTTPS:\rest of the url" }

For some reason I can't determine it is adding a # to the start of the hyperlink.

Thoughts?

Thanks much
Mark

Hi Mark. I think that could happen if the field is a Hyperlink field in the table. Is it coming out as a working link in the Word doc? If not, did you want it to? Or, were you okay with it not a link but remove the extra character?

mmchaley · Oct 11, 2019

No luck - I made it short text in both the table and on the form made sure it did not read as Hyperlink. It still added the # to the beginning of the URL.

It does not need to be a link in the data table, but does on the word merge.

Thanks for your input.

EDIT: I had to remove the merge fields and re-establish them. Once I did that, the # did not get added to the hyperlink.

NOTE: I used the Insert Hyperlink field from word and then embedded the merge field in the hyperlink code with a "Click Here" text. Printing the word document to .pdf removed the embedded hyperlink.

Word Merge (1 Viewer)

mmchaley

Registered User.

theDBguy

I’m here to help

mmchaley

Registered User.

theDBguy

I’m here to help

isladogs

MVP / VIP

mmchaley

Registered User.

theDBguy

I’m here to help

mmchaley

Registered User.

theDBguy

I’m here to help

isladogs

MVP / VIP

mmchaley

Registered User.

isladogs

MVP / VIP

Attachments

mmchaley

Registered User.

isladogs

MVP / VIP

mmchaley

Registered User.

theDBguy

I’m here to help

mmchaley

Registered User.

Similar threads

Users who are viewing this thread