RSS iconTwitter iconFacebook icon

The Trek BBS title image

The Trek BBS statistics

Threads: 140,847
Posts: 5,474,200
Members: 25,040
Currently online: 499
Newest member: Space Tennis

TrekToday headlines

Retro Review: Covenant
By: Michelle on Nov 22

Two Official Starships Collection Previews
By: T'Bonz on Nov 21

Saldana: Women Issues In Hollywood
By: T'Bonz on Nov 21

Shatner Book Kickstarter
By: T'Bonz on Nov 20

Trek Original Series Slippers
By: T'Bonz on Nov 19

Hemsworth Is Sexiest Man Alive
By: T'Bonz on Nov 19

Trek Business Card Cases
By: T'Bonz on Nov 17

February IDW Publishing Trek Comics
By: T'Bonz on Nov 17

Retro Review: The Siege of AR-558
By: Michelle on Nov 15

Trevco Full Bleed Uniform T-Shirts
By: T'Bonz on Nov 14


Welcome! The Trek BBS is the number one place to chat about Star Trek with like-minded fans. Please login to see our full range of forums as well as the ability to send and receive private messages, track your favourite topics and of course join in the discussions.

If you are a new visitor, join us for free. If you are an existing member please login below. Note: for members who joined under our old messageboard system, please login with your display name not your login name.


Go Back   The Trek BBS > Entertainment & Interests > Science and Technology

Science and Technology "Somewhere, something incredible is waiting to be known." - Carl Sagan.

Reply
 
Thread Tools
Old April 29 2014, 07:13 PM   #1
Ar-Pharazon
Rear Admiral
 
Ar-Pharazon's Avatar
 
MS Excel Question

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.
__________________
Rimmer, on what period of history to live in-
“Well, It’d be the 19th century for me, one of Napoleon’s marshals.
The chance to march across Europe with the greatest general of all time and kill Belgians” - (White Hole).
Ar-Pharazon is offline   Reply With Quote
Old April 30 2014, 12:35 AM   #2
RobertVA
Fleet Captain
 
RobertVA's Avatar
 
Location: Virginia USA
Re: MS Excel Question

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.

Last edited by RobertVA; April 30 2014 at 12:59 AM.
RobertVA is offline   Reply With Quote
Old April 30 2014, 12:40 AM   #3
Snaploud
Admiral
 
Snaploud's Avatar
 
Location: Massachusetts, USA
Re: MS Excel Question

http://office.microsoft.com/en-us/wo...102809678.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.
Snaploud is offline   Reply With Quote
Old April 30 2014, 03:04 PM   #4
Ar-Pharazon
Rear Admiral
 
Ar-Pharazon's Avatar
 
Re: MS Excel Question

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

__________________
Rimmer, on what period of history to live in-
“Well, It’d be the 19th century for me, one of Napoleon’s marshals.
The chance to march across Europe with the greatest general of all time and kill Belgians” - (White Hole).

Last edited by Ar-Pharazon; April 30 2014 at 03:28 PM.
Ar-Pharazon is offline   Reply With Quote
Old April 30 2014, 04:48 PM   #5
RobertVA
Fleet Captain
 
RobertVA's Avatar
 
Location: Virginia USA
Re: MS Excel Question

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.

Last edited by RobertVA; April 30 2014 at 06:22 PM.
RobertVA is offline   Reply With Quote
Old April 30 2014, 11:03 PM   #6
Ar-Pharazon
Rear Admiral
 
Ar-Pharazon's Avatar
 
Re: MS Excel Question

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.
__________________
Rimmer, on what period of history to live in-
“Well, It’d be the 19th century for me, one of Napoleon’s marshals.
The chance to march across Europe with the greatest general of all time and kill Belgians” - (White Hole).
Ar-Pharazon is offline   Reply With Quote
Old May 1 2014, 05:42 AM   #7
RobertVA
Fleet Captain
 
RobertVA's Avatar
 
Location: Virginia USA
Re: MS Excel Question

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.
RobertVA is offline   Reply With Quote
Old May 1 2014, 12:21 PM   #8
Ar-Pharazon
Rear Admiral
 
Ar-Pharazon's Avatar
 
Re: MS Excel Question

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.
__________________
Rimmer, on what period of history to live in-
“Well, It’d be the 19th century for me, one of Napoleon’s marshals.
The chance to march across Europe with the greatest general of all time and kill Belgians” - (White Hole).
Ar-Pharazon is offline   Reply With Quote
Old May 1 2014, 01:21 PM   #9
RobertVA
Fleet Captain
 
RobertVA's Avatar
 
Location: Virginia USA
Re: MS Excel Question

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.
RobertVA is offline   Reply With Quote
Old May 1 2014, 04:12 PM   #10
Ar-Pharazon
Rear Admiral
 
Ar-Pharazon's Avatar
 
Re: MS Excel Question

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.
__________________
Rimmer, on what period of history to live in-
“Well, It’d be the 19th century for me, one of Napoleon’s marshals.
The chance to march across Europe with the greatest general of all time and kill Belgians” - (White Hole).
Ar-Pharazon is offline   Reply With Quote
Reply

Bookmarks

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump



All times are GMT +1. The time now is 05:21 AM.

Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
FireFox 2+ or Internet Explorer 7+ highly recommended.