Merge data from excel into word

excelledsoftware · #1 09-15-2013, 05:32 PM

Quote:

Originally Posted by jonpackbosoxfan

No it doesnt have to be in word, but what i see as the true challenge is that if that particular vendor was not there in a particular week, i dont want them to show up on the accounting form. I just want the vendors that were there that particular work

Ok I think I got it. So I'm looking at week 7-24 for Wednesday. The only names I see that should come are Byard, Jake, English, Jonathan, Flores, Barbara
and their amounts. Is this correct? if so it can be done a couple ways. The first way is with an auto filter applied and select "Non Blanks" on the date specified column then copy and paste those results on your new worksheet or even your word template. The other would be to do it with VBA. Once we confirm that the 7-24 example above is correct I can start on some VBA code for you to do this.

jonpackbosoxfan · #2 09-15-2013, 06:05 PM

Yes that date is correct

I dont know a lot about VBA code, so i may need lots of help putting this together. But thank you for any help that you can provide me.

Thank you,

excelledsoftware · #3 09-15-2013, 11:07 PM

Quote:

Originally Posted by jonpackbosoxfan

Yes that date is correct

I dont know a lot about VBA code, so i may need lots of help putting this together. But thank you for any help that you can provide me.

Thank you,

Cool Here is a start. The workbook attached is a 2003 but you should be able to reconvert it no problem. I added a worksheet that will give your results. It gives you an inputbox to put in the date for either Wednesday or Saturday. It will then grab each amount and the name from column B into the new worksheet. This code does not contain any error handling so try it out first and let me know if it works and I can fine tune it if needed.

Here is the code for the workbook

Code:

Sub WedData()

' Macro recorded 9/15/2013 by JRErickson
'
Dim WedDate As Date
Dim WedColTot As Integer
Dim CurCol As String
Dim CurRange As String
Range("'Print Out'!y1:z500").ClearContents
Range("'Print Out'!b4").ClearContents
Range("'Print Out'!d3").ClearContents
Range("a1").Select
WedDate = InputBox("Please enter Wednesday date in proper format.", "Enter Date")
    Cells.Find(What:=WedDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        CurCell = ActiveCell.Column
        y = 8
        For X = 1 To 104
        If ActiveCell.Value <> "" Then
        Range("'Print Out'!Z" & y) = ActiveCell.Value
        Range("'Print Out'!Y" & y) = Cells(y, 2)
        y = y + 1
        ActiveCell.Offset(1, 0).Select
        Else
        ActiveCell.Offset(1, 0).Select
        End If
        Next X
       
      Range("'Print Out'!b4").Value = WedDate
      Range("'Print Out'!d3").Value = "Wednesday"
      Range("a3").Select
      
        
        
      
        
        
        
   
        


        
End Sub



Sub SatData()



' Macro recorded 9/15/2013 by JRErickson
'
Dim SatDate As Date
Dim SatColTot As Integer
Dim CurCol As String
Dim CurRange As String
Range("'Print Out'!y1:z500").ClearContents
Range("'Print Out'!b4").ClearContents
Range("'Print Out'!d3").ClearContents
Range("a1").Select
SatDate = InputBox("Please enter Saturday date in proper format.", "Enter Date")
    Cells.Find(What:=SatDate, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Select
        CurCell = ActiveCell.Column
        y = 8
        For X = 1 To 179
        If ActiveCell.Value <> "" Then
        Range("'Print Out'!Z" & y) = ActiveCell.Value
        Range("'Print Out'!Y" & y) = Cells(y, 2)
        y = y + 1
        ActiveCell.Offset(1, 0).Select
        Else
        ActiveCell.Offset(1, 0).Select
        End If
        Next X
       
      Range("'Print Out'!b4").Value = SatDate
      Range("'Print Out'!d3").Value = "Saturday"
      Range("a3").Select

End Sub

This was fun.

Let me know what you think or if you have any questions.

Take care.

jonpackbosoxfan · #4 09-16-2013, 07:41 AM

This is exactly what I am wanting

Question for a newbie trying to learn more about Office, how do i access the underlying code in Excel. I had a project that I worked on with a friend on a access database, that i had to get into the code there, but I don't know how to get into it on Excel

If you are feeling more adventurous I would love to be able to add a little more into this project

I have a access database that keeps all the contact data for these vendors. I would love to be able to transfer all their attendances and the totals collected from them into my database. I have to submit a report to the Department of Revenue at the end of the year with all the vendors that attended that year.

I have been keeping separate databases for each year, but would love to be able to start keeping one continuous database. Plus I will not always be in this position and I would like to be able to leave a much easier system in place for the next person.

Thank you for what you have done so far. I really appreciate it.

Thank you

jonpackbosoxfan · #5 09-16-2013, 10:35 AM

okay i ran this on a saturday, and i noticed that the correct data is not necessaily pulling.

i have enclosed the actual spreadsheet that i created. With names fully on it.

when i ran it with all the name back in it, the dollar amounts were not correct for vendors. Case in point...Dick Blaske it showed a dollar amount of 6.00 when it should be 18.00

Thank you,

2013 Membership Attendance.xlsx

BobBridges · #6 09-16-2013, 01:17 PM

Quote:

I ran this on a Saturday, and i noticed that the correct data is not necessarily pulling....When I ran it with all the names back in it, the dollar amounts were not correct for vendors. Case in point: Dick Blaske it showed a dollar amount of $6 when it should be $18.

The attached workbook is .xlsx, not .xlsm, so I can't tell what, if anything, might be wrong with the VBA code that created it. But for what it's worth, when I open this I see a bunch of 18s next to Dick Blaske's name; 18, not 6. Dunno whether I'm simply looking at the wrong row, or our Excels are acting differently, or what.

jonpackbosoxfan · #7 09-16-2013, 04:45 PM

Okay now I have enclosed the one with the macros in it plus all the data. If you compate the data in the Saturday/Wednesday (which is correct) and the data that goes into the printout workbook, you will see that the correct dollar amounts is not being associated with the right people. Please help. Thank you.

2013 Membership Attendance With Macro.xls

BobBridges · #8 09-16-2013, 01:12 PM

Quote:

....how do i access the underlying code in Excel? I had a project that I worked on with a friend on as Access database, that i had to get into the code there, but I don't know how to get into it on Excel.

In Excel, yeah, it's different. What version of Excel are you using?

If you're using one of the later ones with the "ribbon" interface, you can start by clicking on the Developer tab in the ribbon. The problem is that Developer isn't one of the tabs that shows up by default straight from the factory; you have to enable after you get Excel installed.

So if you haven't done that yet, here's how: 1) File, Options; 2) select "Customize Ribbon"; 3) in the list in the right half of the resulting window, look for the unchecked box next to "Developer", and check it. 4) Hit OK to return to the workbook. Now at the right of the menu bar you should see Developer as one of the ribbon options.

When you select Developer, the option Visual Basic appears at the far left; click that to enter the Visual Basic editor. From there I think you'll know your way around, because it looks pretty much the same in Access and Excel. The only difference is that the objects in the Explorer window are organized a little differently, but you should be able to figure that part out alright. Or if not, of course, you can ask.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
data from Excel into Word table	ragesz	Word Tables	1	09-29-2013 06:14 PM
converting a word document to a data file for mail merge	drsuis	Mail Merge	4	02-21-2013 03:34 PM
How can I revise Excel data source in merge?	navysalad	Mail Merge	6	01-07-2012 06:50 PM
Wine List, Data Merge - Excel to Publisher - Or other suggestions?	daym	Publisher	2	05-01-2011 03:45 AM
How do I set up the fields in mail merge word 07 from my data base	mbcrabber	Mail Merge	4	06-06-2010 01:25 PM