UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Make Macro From Excel To Run Mail Merge-and Let Us Chose The Data In Excel To Be Merged, Office 2007    
 
   
Frank Situmorang
post Jun 16 2017, 09:34 PM
Post#1



Posts: 1,185
Joined: 10-June 10



Hello,

Sorry for being absent almost 4 years, the reason is I already moved to CA from Jakarta Indonesia, and I just got a survival job right now, so I do not have time to do office job. But since I was just elected as our Church Treasurer, so I want to automatize my issuing receipts to members.

Can anybody help me how to make a macro from Excel running word merging?

The following VBA of Word Macro:

ActiveDocument.MailMerge.MainDocumentType = wdCatalog
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False


I need to inform that the following is the range which is the dynamic range.
Attached File  People_need_receipt.jpg ( 407.67K )Number of downloads: 3

How can we make the dynamic range can be read from word when we running the Macro from my word template attached Attached File  receipt_sample.jpg ( 151.18K )Number of downloads: 4
. Macro should let us to ck which one should be printed in the range (see this picture) Attached File  Wordmacro.jpg ( 102.18K )Number of downloads: 1
and give us a chance to see the checking point in Cell J1 should be OK

Thank you for any help

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
cheekybuddha
post Jun 17 2017, 01:56 PM
Post#2


UtterAccess VIP
Posts: 9,177
Joined: 6-December 03
From: Telegraph Hill


Hi Frank,

I'm not sure I can be of much help, but maybe this link can offer some ideas?

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Frank Situmorang
post Jun 19 2017, 07:07 PM
Post#3



Posts: 1,185
Joined: 10-June 10



Thank you David, I will give it a try

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
Frank Situmorang
post Jun 19 2017, 07:07 PM
Post#4



Posts: 1,185
Joined: 10-June 10



Thank you David, I will give it a try

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
Frank Situmorang
post Jun 23 2017, 01:45 PM
Post#5



Posts: 1,185
Joined: 10-June 10



Hi David,

I tried to write macro like this, but it does not work.

Sub Frank_1()
'
' Frank_1 Macro
'
'
ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\Mentone_Treasurer\Mentone SDA Church Receipt of Tithe n Offerings_for Print.xlsm", _
Connection:="", _
SQLStatement:="SELECT * FROM `Receipt`", WHERE `Nama` <> '0' ORDER BY `Nama` ASC "
& ""
SubType:=wdMergeSubTypeAccess
End Sub


Could you help?

Thank you

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
cheekybuddha
post Jun 23 2017, 01:57 PM
Post#6


UtterAccess VIP
Posts: 9,177
Joined: 6-December 03
From: Telegraph Hill


Frank, I'll try and have a look - but I doubt I will any spare time before next week.

Other members may be able to assist in the meanwhile, I hope.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 23 2017, 01:58 PM
Post#7


UtterAccess VIP
Posts: 9,177
Joined: 6-December 03
From: Telegraph Hill


The backticks [ ` ] in your SQL statement might be causing a problem.

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 23 2017, 01:59 PM
Post#8


UtterAccess VIP
Posts: 9,177
Joined: 6-December 03
From: Telegraph Hill


What's the native language/regional setting of your copy of Office?

--------------------


Regards,

David Marten
Go to the top of the page
 
Frank Situmorang
post Jun 23 2017, 08:02 PM
Post#9



Posts: 1,185
Joined: 10-June 10



David,

English I think, but anyway can you teach where to look the regional language of my office. I do not think Indonesian can work for the Office.

Thank you for your further help.


Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
MadPiet
post Jun 23 2017, 08:18 PM
Post#10



Posts: 2,184
Joined: 27-February 09



I would say use Albert Kallal's "Super Easy Word Merge" but it runs from Access... Not an option if you're using a Mac, (but who does that?!)
Go to the top of the page
 
Frank Situmorang
post Jun 23 2017, 08:52 PM
Post#11



Posts: 1,185
Joined: 10-June 10



Mad Piet,

Is there anything that run from Excel Macro?

Thanks

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
Frank Situmorang
post Jun 23 2017, 08:52 PM
Post#12



Posts: 1,185
Joined: 10-June 10



Mad Piet,

Is there anything that run from Excel Macro?

Thanks

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
Frank Situmorang
post Jun 25 2017, 02:25 PM
Post#13



Posts: 1,185
Joined: 10-June 10



David,

I have tried this, but it still wont work.Hopefully you have time this week to help me.

Sub Frank_1()
'
' Frank_1 Macro
'
'
ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\Mentone_Treasurer\Mentone SDA Church Receipt of Tithe n Offerings_for Print.xlsm""," _
Connection:="",_
SQLStatement:= "SELECT * FROM 'Receipt' WHERE 'Amount' > 0 "
SubType:=wdMergeSubTypeAccess
'ActiveDocument.MailMerge.DataSource.QueryString = "SELECT * FROM 'Receipt'"
'WHERE (Nama IS NOT NULL And Nama)ORDER BY Nama ASC"
'&""
End Sub

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
Frank Situmorang
post Jun 30 2017, 04:26 PM
Post#14



Posts: 1,185
Joined: 10-June 10



Hi David,

Good Afternoon!, I know that you have no time for this week, but maybe sometime next week you can help me, or somebody can help me.

When I open my Receipt template document where I put my macro for merging, below is the statement of SQL in the pop up window.
Attached File  PopUp_in_Word.jpg ( 42.26K )Number of downloads: 0


This following macro for merging data from my excel to word's receipt template is working now.
Sub Frank()
'
' Frank Macro
'
'
ActiveDocument.MailMerge.MainDocumentType = wdCatalog
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

My question now, what is the VBA of Macro if we want it to make in Excel, without opening the "Receipt" Word template.
Both excel and word file, I save them in C:\.


Thank you for any helps from our community.

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
MadPiet
post Jul 10 2017, 12:46 AM
Post#15



Posts: 2,184
Joined: 27-February 09



ActiveDocument.MailMerge.MainDocumentType = wdCatalog
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

My question now, what is the VBA of Macro if we want it to make in Excel, without opening the "Receipt" Word template.
Both excel and word file, I save them in C:\.

The problem is that if you're calling everything from Excel, the compiler "thinks" you're talking about Excel when you're not. You need to specify the Word application in your code

CODE
'****** Code Start ********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
[b]Private mobjWordApp As Word.Application[/b]

Private Sub Command1_Click()
Const conTEMPLATE_NAME = "\Templates\merge.dot"

   Set mobjWordApp = New Word.Application
   With mobjWordApp
      .Visible = True
      .WindowState = wdWindowStateMaximize
      .Documents.Add Template:=(.Options.DefaultFilePath( _
               wdUserTemplatesPath) _
               & conTEMPLATE_NAME)
   End With
End Sub
'****** Code End ********


So your code...

CODE
Private mobjWordApp As Word.Application
Set mobjWordApp = New Word.Application

With mobjWordApp

       ActiveDocument.MailMerge.MainDocumentType = wdCatalog
       With ActiveDocument.MailMerge
              .Destination = wdSendToNewDocument
              .SuppressBlankLines = True
              With .DataSource
                     .FirstRecord = wdDefaultFirstRecord
                     .LastRecord = wdDefaultLastRecord
              End With
              .Execute Pause:=False
       End With
End With
--- but now you have to destroy the reference to mobjWordApp
     set mobjWordApp = Nothing
End Sub


Dig around on AccessWeb for IsAppRunning() -- if Word is already running, you don't want to create a new instance of it, you just want to use the running instance.
Go to the top of the page
 
Frank Situmorang
post Jul 10 2017, 11:56 AM
Post#16



Posts: 1,185
Joined: 10-June 10



Thank you Piet, I will try it.

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
Frank Situmorang
post Jul 10 2017, 02:57 PM
Post#17



Posts: 1,185
Joined: 10-June 10



Piet,

This is now my macro in Word, that works to select only certain rows from data source:

CODE
Sub creatingreceipt()
'
' creatingreceipt Macro
'
'
    ActiveDocument.MailMerge.MainDocumentType = wdCatalog
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Mentone_Treasurer\Myworkbook_for Print.xlsm" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Mentone_Treasurer\Myworkbook_for Print.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLE" _
        , SQLStatement:="SELECT * FROM `Receipt$` WHERE `Amount` <> 0 ", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub


How can I adapt your suggestion.

Thank you.

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
MadPiet
post Jul 10 2017, 04:42 PM
Post#18



Posts: 2,184
Joined: 27-February 09



Not sure. Maybe go find a copy of Albert Kallal's SuperEasy Word Merge and figure out how he does it? He outputs a selected set of data to a CSV and then uses that as a merge source in Word, then deletes the CSV using KILL() or something similar. Maybe if you poke Albert and he's not crazy busy, he can help.
Go to the top of the page
 
Frank Situmorang
post Jul 10 2017, 04:55 PM
Post#19



Posts: 1,185
Joined: 10-June 10



My Macro in Word is already working to copy data from " Donor detail" to Interface sheet. But since my imported excel data from Quickbooks is varying to sometimes fill in Cell I3, some times empty some times not, so I want the macro to copy to always include column I3 of Donor Detail Sheet.

Thanks
Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 
Frank Situmorang
post Jul 23 2017, 05:22 PM
Post#20



Posts: 1,185
Joined: 10-June 10



Piet and all others,

Here is my VBA in Excel to do selecting data that we need from excel:

CODE
Sub Frank()
''
' Frank Macro
'

'
    Sheets("summary").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Sheets("Donor").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("summary").Select
    Range("A3").Select
    ActiveSheet.Paste
    Sheets("Interface").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
    Dim wsDonorDetail As Worksheet
    Dim wsInterface As Worksheet
    
    Set wsDonorDetail = Sheets("DonorDetail")
    Set wsInterface = Sheets("Interface")
    wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy Destination:=wsInterface.[a2]
    Sheets("Receipt").Select
    Application.CutCopyMode = False
End Sub


While this is the code in Word to do printing in the form of receipt for data taken from excel:
CODE
ub creatingreceipt()
'
' creatingreceipt Macro
'
'
    ActiveDocument.MailMerge.MainDocumentType = wdCatalog
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Myfolder\MyWorkbook.xlsmt.xlsm" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Myfolder\MyWorkbook.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLE" _
        , SQLStatement:="SELECT * FROM `Receipt$` WHERE `Amount` <> 0 ", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub


My question now , how can we combine thes 2 VBA, to put in excel. In other word, we do not have to open the word receipt template, but we just create a button in excel to do it once for all.

Thank you for any help.

Frank

--------------------
Frank S
Redlands, CA
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2017 - 01:26 AM