Excel Question 2

[haeddy]

I'm trying to do total compensation report for our employees. I've created a database in Excel and I created the letter in excel. How can I merge the two files to create the letters? I have Excel 2013.

 

[SkipVought]

Hi,
There are several FAQs on MailMerge, but I'd suggest faq68-4223.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[haeddy]

I'm getting an ambiguous name error on MergePrint. Below is what I put in Visual.

[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1464192687/tips/Doc5_ib0bh8.docx[/url]

 

[SkipVought]

When you Debug, what what statement is indicated?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[haeddy]

Compile error: Ambiguous name detected: Mergeprint

 

[SkipVought]

You posted an IMAGE!

Can't compile an image!

So you have got to find the ambiguity!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[haeddy]

Thanks Skip. I got that one fixed. Now I'm getting:

Compile error: sub or function not defined and it's highlighting RangeName.

 

[SkipVought]

Refer to the FAQ regarding Range Names.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[haeddy]

I already did the range names in my data file. I highlighted the column and clicked on Define Name and typed in a name. I double checked and they're all still there and there are no spaces in them.

Holly

 

[SkipVought]

So you made a Range Name for the CELL in the sheet that contains the letter that needs a NAME from your MailMerge list of names in some other sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[haeddy]

Yes. They're in the data file and the letter.

 

[SkipVought]

Please upload your workbook(s).

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

You failed to copy the RangeName function from the FAQ to your module.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

In addition to not having that function...

1) when you Set the ws data worksheet object, you need the Workbooks object to qualify the Worksheet object...

Set ws data = Workbooks("test_dteztz.xlsx").Worksheets("EE Upload")

2) the first Named Range your code assigns is EMP_ID. Your workbook has no cell named as such! (BTW, the reference from wsdata in that statement needs the .Value property, yielding the value 6133)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[haeddy]

I couldn't find the RangeName function in the FAQ - I just googled it. Can you help me with that?

1) Where do I put that code?

2) I removed the EMP_ID column. The reference from wsdata in that statement needs the .Value property, yielding the value 6133 - I have no idea what you're talking about.

 

[SkipVought]

Code as copied from FAQ68-4223

[b]Function RangeName(sName As String) As String
    RangeName = Application.Substitute(sName, " ", "_")
End Function
[/b]Sub MergePrint()
'set up your merge form by naming the merge fields _
 with the same name as the data fields you are importing.
'if any data field contains spaces, then substitute an _
 UNDERSCORE character for each space in the name.
    Dim wsForm As Worksheet, wsData As Worksheet
    Dim sRngName As String, r As Long, c As Integer
    Set wsForm = Worksheets("My Form") 'change to your sheet name
    Set wsData = Worksheets("My Data") 'change to your sheet name
    With wsData.Cells(1, 1).CurrentRegion
        For r = 2 To .Rows.Count
            If Not wsData.Cells(r, 1).EntireRow.Hidden Then
                For c = 1 To .Columns.Count
                    sRngName = wsData.Cells(1, c).Value
                    Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
                Next
                wsForm.PrintOut
            End If
        Next
    End With
End Sub

1)-> Paste it into the module.

2)->

'
    With wsdata.Cells(1, 1).CurrentRegion
        For r = 2 To .Rows.Count
            If Not wsdata.Cells(r, 1).EntireRow.Hidden Then
                For c = 1 To .Columns.Count
                    srngname = wsdata.Cells(1, c).Value
                    [highlight #FCE94F]Range(RangeName(srngname)).Value = wsdata.Cells(r, c[b]).Value[/b]
[/highlight]                Next
                wsform.PrintOut
            End If
        Next
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Okay, since you eliminated EMP ID, I did this...

'
                For c = [highlight #FCE94F]2[/highlight] To .Columns.Count
                    srngname = wsdata.Cells(1, c).Value
                    Range(RangeName(srngname)).Value = wsdata.Cells(r, c).Value
                Next

...and got this in column A for the first three c values...

[pre]
Aavang,Michele R
1122 Any Street
Woodstock
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

BTW, it looped through to the last column of data in the first row.

I didn't run through all the rows.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[haeddy]

I already had it in the letter but maybe I had something typed wrong. I copied and pasted it again and now it's giving me "run-time error '9': Supscript out of range". UGH! This is what I put in the letter:

Function RangeName(sName As String) As String
RangeName = Application.Substitute(sName, " ", "_")
End Function
Sub MergePrint()
Dim wsForm As Worksheet, wsData As Worksheet
Dim sRngName As String, r As Long, c As Integer
Set wsForm = Worksheets("Total Compensation Summary") This line is where it's giving me the run time error
Set wsData = Worksheets("EE Upload")
With wsData.Cells(1, 1).CurrentRegion
For r = 2 To .Rows.Count
If Not wsData.Cells(r, 1).EntireRow.Hidden Then
For c = 2 To .Columns.Count
sRngName = wsData.Cells(1, c).Value
Range(RangeName(sRngName)).Value = wsData.Cells(r, c).Value
Next
wsForm.PrintOut
End If
Next
End With
End Sub

Can I scream now???

 

[SkipVought]

Look at my 26 May 13:44 post

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]