Automatically merge data from userform into a Word doc

D

Dynamo Nath

Board Regular
Joined
Aug 5, 2009
Messages
142
Am new to using VBA but with some excellent tips and help from this and the net I have created an Excel spreadsheet that collects info from a userform; we are using this as a issue log.

However, at the same time, upon completion of the log, the engineer then needs to fill out the essentially the same information in a Word form for sending off to the customer. I was thinking that perhaps this could be done by using a mail merge but from past experience this generally isn't very user friendly or quick.

Is it possible to automate this somehow using VBA such that when Enter is clicked on the form, not only does it fill in the next available line in the log spreadsheet but it also opens Word and populates the required fields with this same info?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Update:
I've found some code which I think its pretty much there. However, it tries to merge every row of data into the form. Is there a way of just passing the data from the most recent line/record to the form?

From looking at the code you can declare a start and finish from the source data, I just don't know how to set this up as a variable.
 
Upvote 0
Well could we look at the code?:)

When you say you can set the start and finish is that using criteria on the data source or just specifying the range to use.
 
Upvote 0
This code sort of works although it does throw up some oddities. Mainly that it opens the merge.doc file but then also creates a file called "Form Letters 1". Using the datasource.firstrecord/lastrecord I can hardwire the chosen record into the code but what I am looking to do is create someway this can select the last entry in the spreadsheet. The 'fqr_num line below was my attempt at a variable but it hasn't worked.

Code: 
Private Sub cbMerge_Click()
    Dim wrd As Word.Application
    Dim mydoc As Word.Document
    Dim fqr_num As String
    'fqr_num = Target.Row - 4
    Set wrd = CreateObject("Word.Application")
    wrd.Visible = True
    Set mydoc = wrd.Documents.Open("C:\Documents and Settings\nathan.waterman\Desktop\merge.doc")
    
    With wrd.ActiveDocument.MailMerge
        .OpenDataSource Name:= _
        "C:\Documents and Settings\nathan.waterman\Desktop\ECR Log test v2.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\nathan.waterman\Desktop\ECR Log test v2.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _
        , SQLStatement:="SELECT * FROM `database1`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
        .DataSource.FirstRecord = 2
        .DataSource.LastRecord = 2
        .Execute
        
    End With
    
End Sub
 
Upvote 0
Why not just use something like this to get the last row of data?
Code: 
LastRow =Workbooks("ECR Log test v2.xls").Worksheet("database1").Range("A" & Rows.Count).End(xlUp).Row
Then perhaps this.
Code: 
.DataSource.FirstRecord = LastRow
.DataSource.SecondRecord = LastRow
Note this assumes 'database1' is a worksheet name.

If it's a named range then there could be another way to get the last record.
Code: 
LastRow = Range("database1").Rows.Count
 
Upvote 0
I had a feeling it would be something fairly straight forward. database1 is a named range as that seemed the best way of passing the headers through to Word for the mailmerge.

I'll try this and let you know how i get on.

Thx
 
Upvote 0
Well that wasn't quite as simple as it seemed! Both methods return errors but I suspect that is because I'm not declaring lastrow properly. I have been declaring it as a string, is this correct?
 
Upvote 0
No it should be Long, but it shouldn't error - I think anyway - if it's declared as string.

What was the error message?

Was it when you tried to set LastRow or was it later in the code?

If it's later in the code what value is LastRow actually getting, if any - string or otherwise.

By the way I see part of the code contains an SQL string.

As it is with the * that is pulling all the records, you might be able to alter that string to use criteria to only pull the record you want.

Whether that is possible or not depends on how you can determine what record that is.

If the only way is that it's the last row in the data it might be not be easily done.
 
Upvote 0
I can use .datasource.firstrecord = activerecord but this selects the last line defined within the named range which is fine except that this line is empty as the range will gradually fill with data over time.

The SQL statement in the code came from the Word macro recorder; and I know even less about SQL than I do about VBA :)

I'll have another go at this later but it's getting v frustrating especially as everytime I go to close Word after the macro has run it tells me the global.dot template has been changed and needs saving.

All of this could be alleviated of course if we were allowed to use Access at work.
 
Upvote 0
If the last line is empty because you envisage the data increasing you really should look into using another method.

Perhaps you could try using a dynamic named range.

You can do that manually on the worksheet, or you could write code to create one.

I could help with the latter, but not with the formula - formulas, which is what you would need, aren't my thing.

As for the SQL, like I said, it could be possible, and if it is it would be pretty straightforward.

But it really depends on your data, mainly how it's structure, what's in it, is there some other criteria to use to get the record you want etc

PS How would Access makes things easier?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Same Doc - Different Spreadsheets
Replies
8
Views
604
loract
L
S
  • Question
Open and print a section of word document as pdf
Replies
6
Views
177
StephenBart
S
G
  • Question
Bug with passing variables between user forms--VBA
Replies
4
Views
74
Domenic
D
F
  • Question
VBA create bulk Word docs from Excel
Replies
2
Views
422
Fluff
Fluff
K
  • Question
Change code from inside sheet to userform directly
Replies
0
Views
152
KalilMe
K

Forum statistics

Threads
1,214,657
Messages
6,120,771
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top