Merge data from excel into word

jonpackbosoxfan · #16 09-16-2013, 08:07 PM

Dude it works absolutely perfect. Thank you so much

Jonathan

excelledsoftware · #17 09-16-2013, 08:10 PM

Quote:

Originally Posted by BobBridges

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)

Or you could combine it this way:

Code:

Range("'Print Out'!Z" & y) = ActiveCell.Value
Range("'Print Out'!Y" & y) = Cells(ActiveCell.Row, 2)

I haven't tested that, but unless I made a typo it should work.

...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.

I appreciate the feedback. I have only been vba coding for about 2 months now and have a lot to learn still. I posted a corrected workbook in an earlier post. I am aware of debugging and how to do it. I like your solution on the inputbox variation. I liked the idea of the input box and yes I have not put in an error handler for incorrect dates as I wanted to see if the code worked first.

The FOR statement is definitely not the best it could be how would you go about doing a DO Until when a lot of the cells are already blank?

Again thank you for the feedback.

excelledsoftware · #18 09-16-2013, 08:12 PM

Quote:

Originally Posted by jonpackbosoxfan

Dude it works absolutely perfect. Thank you so much

Jonathan

You are very welcome I appreciate the opportunity to work on it as it really helps me learn as well.

BobBridges · #19 09-16-2013, 09:23 PM

Quote:

I am aware of debugging and how to do it.

Yeah, that part was for Jonathan. It's his program, after all, in the final analysis—he's the one who has to make it work. I hope he'll try it, because (as you already know) the debugging features in the VBA editor are a lot better than writing a lot of of MsgBox calls to figure out what's going on.

Quote:

I have only been vba coding for about 2 months now and have a lot to learn still.

Believe me, I was 'way behind you when I first started on VBA. I'd been doing traditional coding for a long time, and I knew Basic well enough; I just didn't understand how object-oriented programming was different, and it caused me no end of frustration at first. Writing my first simple object cleared up a lot of my confusion; it wasn't necessary for the task I was trying to write, strictly speaking, but it helped me understand what I hadn't before about the difference between a function and a "method".

Quote:

The FOR statement is definitely not the best it could be how would you go about doing a DO Until when a lot of the cells are already blank?

I don't have anything against For statements—it's probably what I'd go to first, in this case—but rather than hard-code the last row ("For X = 1 To 104"), I'd be willing to take a lot of trouble to get the program to figure out for itself what the last row is. That's because the program is sure to be run many times between now and Whenever, and you don't want to have to change it repeatedly—and, likely as not, have to change it when you're already busy and can't spare the time, or (even worse) not realize it has to be changed, and then wonder why your data isn't right. So I'd tell the program to go looking for the last line, and use that in the loop.

As for the loop itself, I probably would do it pretty much the same way you did. Oh, I have a preference for changing the output row before the write instead of after, and I never name variables X any more (because of a mishap in another language entirely; but those are more programming style than The One Right Way to do something; any programmer will tell you there are lots and lots of ways to skin every cat you ever saw.

jonpackbosoxfan · #20 09-17-2013, 09:06 AM

Okay I have one little request on this. I have been playing around with it all morning long, and I am unsure of how to accomplish this

In the report sheet, you set a total of 50 spaces. I could and have had more than 50 vendors in a given week.

If you could adjust it so that there are between 70 and 80 spaces. We are a growing market and what I am trying to accomplish here is establishing better record keeping for the future.

Thank you,

Jonathan

BobBridges · #21 09-17-2013, 09:59 AM

Jonathan, I agree with your goal—to expand the size of the report to allow for growth, I mean. But rather than adjust it for you, I think we ought to fix it so you can understand for yourself how. Excelledsoftware may be softer-hearted, but I'm all about teaching the man to fish ... even at the risk of his going hungry a little, at first.

Let's see what he says. If he agrees with me, you'll have to do it, but one or both of us will explain how. I'd start by asking questions. For example: You say you played with it all morning. What, exactly did you try? It could be you were on the right track but just couldn't figure out how to make it work.

jonpackbosoxfan · #22 09-17-2013, 12:10 PM

Bob

Thank you. Learning would be my prefered method of getting this done.

The things that I tried to do were just first insert new rows into the sheet and then fill the formulas into this. The second thing I tried to do was to insert cells in those 4 rows and again fill the formulas into this

None of this seemed to work, because I dont understand the relation between the vba code that has been written and the y & z columns that are in the reports sheet and how all of that pulls into the actual report.

I took a visual basic class many years ago in college, but that was in 2000, and a lot has happened in the years to push all that information out of my head. Like I said about 8 years ago, I had the opportunity to work on a access database that someone else had put a ton of work into it and had a ton of underlying vba code, but this excel code seems vastly different to me, so I have a difficult time understanding it.

Thank you,

Jonathan English

BobBridges · #23 09-17-2013, 04:09 PM

I would love for you to understand the relation between the VBA code and what comes out on the Print out worksheet; and if you want, we can talk about that as a side question. If you've taken a VBA class before, then you should be able to get it all to come back. Let me know if you want to do that. But if I remember excelledsoftware's solution, you should be able to expand the worksheet without having to change the macro he wrote for you. All his program did was write out a list of contact persons and dollar figures in Y:Z starting at row 8. Getting it into a double-column listing is all the work of formulae in A8:D32. I believe you can expand on that without having to know anything more about the VBA program than that it writes its output to Y8:Zn, where n = "as far down as necessary".

Given that, take a look at the formulae in A8:D32. In particular, note that the formulae in A8:A32 identical to B8:B32, but that A8:B32 are different from C8:D32. How are they different? What (exactly) do they do? I think if you look at the difference, and figure out what the two different formulae mean, you'll have a broad hint about how to proceed from there.

I'm not abandoning you; but start there, and if you don't get it yet, come back and ask more questions.

BobBridges · #24 09-17-2013, 04:51 PM

Oh, I should have added this: The difference between the VBA stuff you did for Access and what you see here isn't a difference in the VBA language itself; it's in the "objects" that Access and Excel use. It makes sense, really; Access thinks in terms of records, forms and filters, while Excel has cells, rows, columns and worksheet functions. There are many overlapping concepts, but the properties and methods you use for one are different from the other because the applications themselves are different.

If you're not sure what I'm talking about—if you're not sure what properties and methods are—well, neither was I, only a few years ago, and it caused me lots of confusion when I tried to write Excel macros. Once I got it clear in my head, it was fine—better than fine, actually, because object-oriented programming, though different from plain Basic or PL/1 or REXX or what-have you, enables you to do some things much more easily. But knowing the VBA language is only half the battle; to use VBA on Excel you have to get to know the Excel "object model", and when you then have to use it on Access you have to learn the Access object model as a separate task. Knowing Excel doesn't enable you to use Access, and knowing VBA/Excel doesn't enable you to use VBA/Access either—or not fluently, at least.

jonpackbosoxfan · #25 09-17-2013, 05:48 PM

Do you have any recommendations for web sites or books that would be good for step by step learning of VBA/Excel?

I did a google search but that came up with lots of stuff.

Jonathan.

jonpackbosoxfan · #26 09-17-2013, 06:49 PM

Okay so I generally understand some of the concepts here. The VBA code that is written searches the particular date column and if it comes across any data in that row it records it in the printout sheet starting in Z1 and working on down, pulling the associated name in that row.

I guess where I am struggling is that if it finds nothing in the first row it records noting in Z1 of the printout sheet. So in the case of the date that has been shown it ends up skipping the first 7 lines of the Z column and finally recording the name of Derrick Bishop in Z8.

So in the actual printout what confuses me is how does the formula that is there know to skip the first 7 rows that no data exists and move to row 8. I would think that it would record no data in the first 8 rows. I guess I am really confused by how the formula knows to skip no data.

So then if i added more cells to create additional spaces, and use the fill handle its just going to produce the next number.

Still confused. Thank you for helping me learn this though because it has been interesting. I understand the worksheet a little better, but i admit that while i generally understand the concepts of the VBA code the specifics of the lingo that is being used is way over my head.

Jonathan

BobBridges · #27 09-17-2013, 07:21 PM

I don't use tutorials and texts much. I read documentation, mostly references and once in a while, when I'm desperate, user's guides. But such as I have give I thee:

Microsoft's Developer Network (MSDN) has their official documentation for all the Office apps, all versions, plus a lot more. Start there and drill down to "Office and SharePoint development" and "Office client development"; that'll show you a list of all the currently supported versions of Office. Pick the one you have. Say it's "Office 2010": There you'll see a list of Office apps: Access, Excel, Outlook etc. Choose "Excel 2010", then "Excel 2010 Developer Reference", then "Reference"; that gets you the documentation on each object used in Excel 2010, with a list of each object's properties and methods. Some of the articles are missing important elements, and this isn't nearly as convenient (to my way of thinking) as the CHMs they used to provide, but it's Official and it's a heck of a lot better than nothing.

I don't know whether it'll be helpful, but you may get some hints by looking at the Access development section; since you already know something about that, seeing the information there may tell you more about how the Excel section is organized. But maybe not.

For the VBA language itself, go back to "Office 2010" and then select "Office 2010" (yes, again). This has a list of various features of Office itself, rather than of any particular application, including "Visual Basic for Applications Language Reference for Office 2010"; select that, then "Visual Basic Language Reference" to see a general reference on VBA.

Another one I use a lot—dunno whether it'll interest you—starts at "MSDN Library" and then goes down through "Web Development" and "Scripting". Here you'll find a reference on VBScript, which is a flavor of VB that's cut down some from VBA but has the advantage of being usable in Windows, ie not from any Office application itself; you can write a VBS program in Notepad, then double-click on the icon to get it to run. Also under Scripting are a couple of objects that I find useful in VBA even though they're not documented in Office: The FileSystemObject, for example, which lets me read and write plain-text files, look up documents in directories and such. There's also an object for handling regular expressions; a handy thing, that, though it took me a while to learn what they're good for. If you start using VBScript much, there are some other handy sites too.

Other than that, when the MS documentation fails me (and it does; I try to write user add-ons, but for some reason it always experiences an error so I'm unable to fill in the gaps), I just google my question and discover that it's usually been asked before, and answered. That's how I found this forum, and there are a few others too. But no doubt if you use a search engine you'll find your own favorites without any pushing from me.

BobBridges · #28 09-17-2013, 07:34 PM

Quote:

The VBA code that is written searches the particular date column and if it comes across any data in that row it records it in the printout sheet starting in Z1 and working on down, pulling the associated name in that row.

Not Z1; it starts in row 8. Notice the line in the program that says "y = 8"? It uses y as the row number for the output; it starts it at 8 and then increments it from there. I don't know why it should use 8, but it doesn't really matter.

And that's why the formula skips over the first seven rows: because the author (I presume that, too, was excelledsoftware) knew that's where the data started. So you're not imagining things; the formula had to point to the right place. It's just that the formula was told that the right place to start was row 8—that is, it was constructed using Y8 as the starting place.

Does that help you see what you have to do? If you add more lines to the bottom of the report (after row 32, I mean), how does that affect what has to happen in the formulae in cols C and D?

Quote:

....while i generally understand the concepts of the VBA code the specifics of the lingo that is being used is way over my head.

Whenever that happens, stop me and ask! I used to think there must be something wrong with me because I didn't understand the terminology that other people considered basic, and what I learned is not that there's nothing wrong with me but that it doesn't matter: The only way to make up the deficit is to start asking, even at the risk of making a pest of myself.

excelledsoftware · #29 09-17-2013, 08:19 PM

Quote:

Originally Posted by BobBridges

I would love for you to understand the relation between the VBA code and what comes out on the Print out worksheet; and if you want, we can talk about that as a side question. If you've taken a VBA class before, then you should be able to get it all to come back. Let me know if you want to do that. But if I remember excelledsoftware's solution, you should be able to expand the worksheet without having to change the macro he wrote for you. All his program did was write out a list of contact persons and dollar figures in Y:Z starting at row 8. Getting it into a double-column listing is all the work of formulae in A8

32. I believe you can expand on that without having to know anything more about the VBA program than that it writes its output to Y8:Zn, where n = "as far down as necessary".

Given that, take a look at the formulae in A8

32. In particular, note that the formulae in A8:A32 identical to B8:B32, but that A8:B32 are different from C8

32. How are they different? What (exactly) do they do? I think if you look at the difference, and figure out what the two different formulae mean, you'll have a broad hint about how to proceed from there.

I'm not abandoning you; but start there, and if you don't get it yet, come back and ask more questions.

Absolutely correct Bob. That was the main reason I wrote it that way.

excelledsoftware · #30 09-17-2013, 08:31 PM

Quote:

Originally Posted by jonpackbosoxfan

Okay so I generally understand some of the concepts here. The VBA code that is written searches the particular date column and if it comes across any data in that row it records it in the printout sheet starting in Z1 and working on down, pulling the associated name in that row.

I guess where I am struggling is that if it finds nothing in the first row it records noting in Z1 of the printout sheet. So in the case of the date that has been shown it ends up skipping the first 7 lines of the Z column and finally recording the name of Derrick Bishop in Z8.

So in the actual printout what confuses me is how does the formula that is there know to skip the first 7 rows that no data exists and move to row 8. I would think that it would record no data in the first 8 rows. I guess I am really confused by how the formula knows to skip no data.

So then if i added more cells to create additional spaces, and use the fill handle its just going to produce the next number.

Still confused. Thank you for helping me learn this though because it has been interesting. I understand the worksheet a little better, but i admit that while i generally understand the concepts of the VBA code the specifics of the lingo that is being used is way over my head.

Jonathan

Here is my recommendation when it comes to learning VBA or any language for that matter.

All computer languages have functions, methods, variables blah blah blah. What I have found is that you need to program your mind first before programming starts to make sense. Here is my suggestion on what you should learn for VBA and in what order.

1. Macro recording (Start using the macro recorder to do simple operations then look at the code after. Trust me a lot of it wont make sense but some will.
2. Write your first msgbox macro Something simple like
sub boxxy ()
msgbox "Hi there everybody"
end sub

You will notice on the msgbox when typing that it will show you alot more things you can enter. Here is a hint if those things are in [square brackets] they are optional.
3. write your first inputbox macro (Very similar to msgbox)
4. Start understanding variables and datatypes. There is a ton of info out there on these. Use simple ones like strings and integers to begin with.
Example
dim boxxy as string
boxxy = "Here is a message boxy from boxxy"
msgbox boxxy
end sub

5. once you have variables down a little bit start writing if statements. Try it with an input box and a message box.
Start with
sub boxxy ()
dim boxxyPrmpt as integer
boxxyStr = "message 1"
boxxyprmpt = inputbox("Enter a number from 1 to 100")

if boxxyPrmpt > 50 then
msgbox "Higher than 50 entered"
else
msgbox "Less than 50 entered"
end if
end sub

You can even declare 2 more string variables and assign them to the 2 msgbox's

6. Loops learn the For (stands for "For this instance") and Do You will also need to understand the range function and maybe cells
research these and let us know what you run into.

It goes slow at first but gets a lot easier as you go.

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