Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Excel Merge (Excel 2003)

    Hello all,

    I have an excel form which managers complete in order to submit an employee change to be entered into my system. However, at times we can have many of the same type of change for example 130 salary changes. Instead of asking the manager to complete a form for each salary change, I would like to ask him/her to provide me with an excel list instead. What I would like to do is merge the list with the excel form.

    Can anyone tell me how I can accomplish this? I don't want to recreate the form in WORD. So this is not an option. I would rather try a macro or some other means in excel.

    I attached a copy of the excel form I would like to merge to my excel list.

    Thank you!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Merge (Excel 2003)

    I concur with Hans.

    It seems the form is made to detail the changes that need to be done. If the manager provides you an excel list with all the changes, it seems that this should "bypass" the need for the form: the excel list acts as the request/detail of the changes.

    The machanism from the form to the changes that would normally be done, now becomes a mechanism from the "excel list" to the changes.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Merge (Excel 2003)

    I'm not sure I understand what you want. I assume that your employee data are in some kind of table, be it in Excel or in a database. So if the manager submits a list, I would merge it with that table instead of trying to get the info into the form. But perhaps I misunderstood your description.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Excel Merge (Excel 2003)

    I apologize I wasn't clear. I should of mentioned that is is necessary for me to print each change onto the form. The form is used to file away into the employee files. Therefore, I must print out a change form for each person that is on my list, which is the reason I need to do a merge from my excel list to the attached form.

    Is there a way to do this? I attached a copy of my sample list that I would like to merge, one form for each of the records.
    Let me know if you have any other questions.

    Thank you.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Merge (Excel 2003)

    Does the following macro do what you want?
    <code>
    Sub MergeIt()
    Dim wbkData As Workbook
    Dim wbkForm As Workbook
    Dim wshData As Worksheet
    Dim wshForm As Worksheet
    Dim r As Long
    Dim n As Long

    On Error GoTo ErrHandler

    Set wbkData = Workbooks("testmerge.xls")
    Set wshData = wbkData.Worksheets(1)
    Set wbkForm = Workbooks("Change_of_Status_Formv2.xls")
    Set wshForm = wbkForm.Worksheets(1)

    ' Fixed info
    wshForm.Shapes("Check Box 73").ControlFormat.Value = 1
    wshForm.Shapes("Check Box 111").ControlFormat.Value = 1
    wshForm.Range("K5") = Date
    ' Add other fixed entries here

    n = wshData.Range("A65536").End(xlUp).Row
    ' Variable data
    For r = 2 To n
    wshForm.Range("B5") = wshData.Range("B" & r)
    wshForm.Range("C29") = wshData.Range("C" & r)
    wshForm.Range("K29") = wshData.Range("D" & r)
    wshForm.Range("C20") = wshData.Range("E" & r)
    wshForm.Range("K20") = wshData.Range("F" & r)
    wshForm.PrintOut
    Next r

    ExitHandler:
    wbkForm.Close SaveChanges:=False
    Set wshForm = Nothing
    Set wshData = Nothing
    Set wbkForm = Nothing
    Set wbkData = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>

  6. #6
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Excel Merge (Excel 2003)

    Thank you HansV. It's exactly what I was looking for.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •