#1
|
|||
|
|||
Merge data from excel into word
Got what I see as a complicated project. I do all the bookkeeping for a farmers market. I have a excel spreadsheet set up with all the vendors for the year. On any particular week that they are present I will enter that data into my spreadsheet. I would like to see about how to merge it into a word document for a weekly accounting form that I use. In the past I have been handwriting the accounting form, but if I can automate this it would be greatly helpful. |
#2
|
|||
|
|||
Quote:
|
#3
|
|||
|
|||
Excel
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
|
#4
|
|||
|
|||
Quote:
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. |
#5
|
|||
|
|||
mail merge
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, |
#6
|
|||
|
|||
Quote:
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. |
#7
|
|||
|
|||
Cool
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 |
#8
|
|||
|
|||
Data Incorrect
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 |
#9
|
||||
|
||||
Quote:
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. |
#10
|
||||
|
||||
Quote:
|
#11
|
|||
|
|||
Problems
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 |
#12
|
|||
|
|||
Quote:
|
#13
|
|||
|
|||
Thank you so much. This really does solve my problem very well.
|
#14
|
|||
|
|||
Quote:
I have some experience with creating macros in access and it sounds like you will need to set up a paste append macro in access and set up a table worksheet in your workbook to do this. Sorry again hope this works now. |
#15
|
||||
|
||||
Ok, I see the problem. I was staring at it suspiciously for quite a while, but I couldn't figure out exactly what it did so I wasn't sure it was wrong. I had to run the thing interactively to pin it down; and that's how you're going to do it, too.
Go to the Excel VBA editor, put the cursor on the first line of the program (the one that says "Sub SatData()"), and hit <F9>. Notice how that line turns sort of burgandy; it means you've put a "break" or "breakpoint" on that line; that means when your program gets to that point in the execution, it'll stop and call for your attention. Now go back to the Saturdays worksheet and start the macro. Immediately you're back in the macro editor again, looking at that line—only now it's highlighted in yellow. That means the program is running, and it's about to run that statement. Now hit <F8> just once, and watch the yellow highlighting advance. It jumps over the Dim statements to the first ClearContents command. That's the statement it's about to execute in the VBA program. <F8> is a command to the editor to execute just one statement in your program, and then to pause again. Hit <F8> again and it'll jump to the next statement. If you pause at this point and go look at your "Print Out" worksheet, you'll notice that the contents of Y1:Z500 are indeed now cleared. Hit <F8> again, several times, one at a time—feel free to watch the results in the worksheet—until you get to the InputBox function. When you hit <F8> on that one, the input box will be displayed on your screen. Go ahead and enter the desired date—2013-06-22, it was, in this case. Now, back in the VBA editor, the Cells.Find statement is highlighted. Before you hit <F8> again, cause your mouse arrow to hover over a few items in your program: Put the cursor over SatDate—any instance of SatDate in your program, it doesn't matter which one. A little box will pop up showing the current value of SatDate (presumably "2013-06-22", just now). Have the cursor hover over the variable a few lines down that you named y; just now it'll say it's "Empty", because you haven't yet set it to anything. Hover over ActiveCell.Value a few lines further down yet; on my machine just now it says "Business Name", because the active cell is currently A1. Feel free to explore a little. When you're ready, hit <F8>; the program has now done the search for the date you typed in. If you go back to the worksheet, you'll see that that date is now selected in row 1. But when you go back to the VBA editor and hit <F8> again, the next statement is executed, which in your worksheet moves the selection down one row. We're almost to the problem. Go ahead and step down through "y = 8" (and now y isn't Empty any more), the For loop, the If statement, and so on until you get to "Range("'Print Out'!Y" & y) = Cells(y, 2)". Here we'll stop and discuss a moment. The variable y is the row number for the output on the Print Out worksheet. Your program has already set 'Print Out'!Z8 to the dollar value (you can go and check, if you like). Now the next statement is supposed to set 'Print Out'!Y8 to the name (in column B, ie 2) from the input row on the Saturdays sheet. But wait—that may be what you want it to do, but it isn't what you told it to do. You told it to use the value not from that row but from Cells(y,2). Hover your mouse over "Cells(y, 2)", and you'll see that it displays the wrong name, "Bishop, Derrick", not "Anders, Nelli" as it should. Why? Cells(y, 2) says to pull from row y, column 2; and since y = 8, you pulled it from the Saturdays worksheet, row 8, column 2. Right column, wrong row; y is the variable you're using for your output row. How to tell your program which input row to use? Well, I don't think much of using ActiveCell and moving it around; most people say that slows your program down unnecessarily. If we keep this up together, I'd just as soon show you some better ways. But for now, since you're already using ActiveCell, just to keep it simple you could do it this way: Code:
Range("'Print Out'!Z" & y) = ActiveCell.Value row = ActiveCell.Row Range("'Print Out'!Y" & y) = Cells(row, 2) Code:
Range("'Print Out'!Z" & y) = ActiveCell.Value Range("'Print Out'!Y" & y) = Cells(ActiveCell.Row, 2) ...But there are some other problems with this program that I'd like to talk about, aside from the obvious bug. These are not exactly wrong, but there are ways to make it easier to run: 1) Rather than have the user type in the desired date (and risk it being typed in wrong, or using the wrong format), it seems to me it's easier on the user to point to the correct column just by putting using the arrow keys to put the selection somewhere in that column. That also enables you to dump the concept of having two different programs; just use whichever worksheet the user points to. 2) Rather than telling your program which rows to look at (1 to 104 for Wed, 1 to 179 for Sat), have your program find the right ending row by itself. That way you don't have to keep adjusting it whenever you add enough new shops to the worksheets. There are a few other things but they're relatively minor and they'll probably come to you anyway, after a little more practice. But these two will be significant improvements in ease of use and of programming. |
|
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 |