MS Excel Question

[Ar-Pharazon]

I have a good basic knowledge of Excel, but have never used macros. Never really needed to until now.

What I have is a spreadsheet of material stored in a building, with about a dozen columns of info per line. There's probably a couple hundred lines.

What I'd like to do is, using info from 4 or 5 of those columns, create a placard for each item automatically, with that info being sent to empty cells on a blank sheet, then probably saving each one as a new sheet and/or new workbook.

I'm not even sure this involves macros, or if it would be another Excel feature altogether.

I'm scheduled to take a couple classes later in the year, but would prefer not to wait that long to do this project.

 

[RobertVA]

I'm not sure if you're wanting to automate the creation of one placard at a time or a new table with data from those 4 or 5 columns in some arrangement other than on a singe line of spreadsheet cells.

You might want to look at the write up in Excel's help for the VLOOKUP() function. It appears to return a value on the same line as a desired value in the first column. Let's imagine you have US two letter state abbreviations in the first column and the respective capitals in the second column. Apparently the VLOOKUP() function could be used to return the appropriate capital when the abbreviation is provided as the first argument, the range containing the data is provided as the second argument (a named range might be desirable) and the column for the return value as the third argument. For an exact match in an unsorted table you would want FALSE as the fourth argument.

Of course you would want to enter the abbreviation you're searching for into a cell outside your placard and use a reference to that cell in the "report" cell in the placard itself.

Note that I'm not prone to pay Microsoft periodic tributes for their fancy UI changes, so I found this information in the help system for Excel '97.

Edited to add: If you're planning to print all the placards in one session it might be possible to use Office's mail merge capabilities in a manner similar to printing out mailing labels.

 

[Snaploud]

http://office.microsoft.com/en-us/word-help/mail-merge-using-an-excel-spreadsheet-HA102809678.aspx

I can't guarantee it will fulfill your needs, but it's a useful Microsoft Office feature for creating Word labels/placards out of excel data.

 

[Ar-Pharazon]

RobertVA I would like to do the creation of as many placards as there are lines (items).

The 4 or 5 pieces of information, from the columns, would be in as many cells, basically 5 rows & 1 column, as wide & tall as possible.

The 5 cells would each have their own font size. The more pertinent info would be line 1, 150pt, and on down the line.

OK, I've gotten into the mail merge thing a little, but I can't see a way to choose what columns I want to include. I can select/deselect columns (recipients).

 

[RobertVA]

We're reaching the limit of how detailed my help can be. You appear to be using a latter version of Microsoft's Office suite, but I don't even know which version. I can't tell what features and/or procedures Microsoft changed. However I'll look in some of my reference material (written for the older versions of Office) to see if they show any flexibility in things like field selection and mail merge output formatting.

Reports like this is one of the things Access made simple, but many people decided the database capabilities of Excel were adequate and bought the less expensive Office bundles that lack the Access program.

Edited to add:

As far as I can tell the image you posted is a record selection pop-up. Once you uncheck unwanted limes like the headings and blanks at the top you would probably return to the word document by clicking the "OK" button at the lower right. The reference books I have for earlier versions of Office show a mail merge tool bar near the top of the merged Word document window. The user can move the insertion point to the desired location and select the desired field from the a drop down on the mail merge toolbar. In the merged Word document the field name appears in angle brackets (<<Part Number>>) and should be subject to the usual formatting functions (justification, font, size etc.). The user can then move the insertion point to where the next database field should appear. The toolbar in the earlier versions featured buttons that allowed the user to preview actual data in the document and move from copy to copy (typical first, previous, next and last icons similar to those you would expect in a media player). Of course the buttons are probably prettier in the more recent versions.

I suspect the mail merge acquires field names from the first line of the table. I don't know to what extent the presence of things like a title and blank lines affects that process. The process might be facilitated if the spreadsheet uses "Header/Footer" on the "Page Setup" pop-up to produce those elements instead of using cells in the spreadsheet itself. Row heights and cell formats like bold face, a different font or borders can be used to visually separate the field headers from the rows containing data.

 

[Ar-Pharazon]

I really just started tinkering with the process. The above image is from Office 2003. I already have 2010 on my other work computer and the machine I was working on should be wanting to upgrade any day now.

I did find a page referencing 2003 for the process after I realized the above link was referencing 2013.

Though, the next step is "writing your letter" and I'm not sure what I need to do there to get it to show me the hundreds of "labels".

I think I really am going to have to do this wholly within Excel somehow, so I can have each cell formatted to the correct font size and only have the columns I need.

 

[RobertVA]

In Word '97 there are different wizards for "LABEL" and "Mailing label" on the "Letters & Faxes" tab of the "New..." pop-up (look for "New..." on the "File" menu) so the degree of automation might vary between them and between them and the "Merge Documents..." function on the "Tools" menu. I suspect the "Mailing Label" option has fields already set up for address labels and offers a selection of label sizes and quantity per sheet (probably by label manufacturer product numbers). The regular "LABEL" wizard (Office '97 uses all caps) might have more flexibility while allowing multiple records per sheet. See my earlier post about moving the insertion point around the Word document and clicking the drop down on the merge toolbar to insert a field where the insertion point is.

The illustrations in one of the books I was looking at had a button on the merge toolbar for switching between a view for editing the document (including the field names which show as <<City>>, <<State>> etc.) and a view showing actual data from the spreadsheet (Los Angeles, CA etc.). The way the title bars looked in that book might reflect their being captured from a computer running a version of Windows prior to XP.

I can't do much in the way of experimentation without setting up a database to merge with a Word document. Even then, there are so many Office versions between mine and yours we won't know how many differences there would be in the details, including which multiple label sheets are still available in retailers.

 

[Ar-Pharazon]

Excel 97 had an Internet Assistant Wizard that would allow you to paste a spreadsheet, or a section thereof, into a webpage template (if the template had a specific tag in it) and then save that as a new webpage.

The more they change things, the less useful they become.

 

[RobertVA]

Apache OpenOffice has a "New" function on the "File" menu that offers creation of a new HTML document (AKA web page). There's also "HTML Document" listed among the many file types in the "Save As..." pop-up. I don't know if you would need a separate FTP program to upload the resulting pages to a server. OpenOffice Writer has similar abilities as Word to insert links to pictures into documents. I would be surprised if Microsoft has dropped this capability from Word, although I wouldn't expect the full capabilities of a program developed for the use of a serious web developer.

I never tried that spreadsheet insertion function. I'm suspecting it inserted a table which added the results shown on the spreadsheet instead of all the formula and function capabilities (at least as far as someone could view in a web browser).

The Apache OpenOffice help function indicates that it too has a label merge capability. It describes clicking an arrow button to move fields into the label and using carriage returns to move the insertion point to new lines on the label.

Similar OpenOffice suites may be available from other sources.

 

[Ar-Pharazon]

I did the webpage thing years ago, for a store website I worked on. I updated the online catalog that way. Not long after 1997 actually.

At work I use MS Expressions Web currently. Editor & FTP in one.