Excel Templates

[StrangerMike]

Hello, this is my first post to this forum.

I am brand new to Excel. I have experience with VB, .net, Access but
never used excel (other than importing excel docs into an Acces
database).

But now I am being asked questions about formatting excel document
prior to import.

Please read this scenario and tell me if what I am thinking is possibl
or correct.

A user receives an excel file from a 3rd party;
When she opens the file in excel there are a couple of columns tha
need to be reformatted before sending them to me for import.

She is not real knowledgable about excel either, but what I hoped
could do for her is: create a template with the couple of column
formatted and someone use this template when ever she opens the file.

So I created a template, but how can she open a file using thi
template? Or do you open the template first and some how open th
original excel file within the template?

Can anyone help?
Thanks
Mik

 

[AlfD]

Hi!

Welcome!

If you really mean reformatting a couple of columns, I would sugges
you do it using a macro.

Easiest thing at this stage woud be to record your actions i
"cleaning" the worksheet.

Open the worksheet.
Tools>Macro>Record new macro.
Choose a "hotkey"

Now go through the process you want to happen. If This week you hav
400 data entres and next week it might be 200 or 3000, you can choos
either to estimate a suitable maximum or format the whole colum
(select the column header letter).

Do all the formatting. Then exit the recorder (either through th
pop-up menu or Tools>Macro>Stop recording.

You can see what you created via Alt+F8 >Edit.

Usual process of trying and testing and you should end up with
one-key routine for your colleague to format the sheet before passin
it on.

Come back if this doesn't make sense or doesn't work for you!

Al

 

[StrangerMike]

Alf thanks for your input.
I followed your instructions and it seems the macro was created.

Now here is where my 'inexperienced' questions begin...

Is that Macro only good on that worksheet? Or can it be used on an
excel sheet?

For instance, I created it as you said, using the ctl+Shft+Q key,
when I open another excel spread sheet (for a different month) and whe
I hit the hot key combination nothing happens.
What am I missing or what do I not understand?
Does this macro always need to be created for her every month when sh
receives her new file?

Thanks Mik

 

[StrangerMike]

Alf,

Perhaps I just don't understand the process enough.

Maybe what they would do is use this generic worksheet and
reuse it for the different months. Because I noticed that when I did
copy paste into the worksheet that had the macro, the formattin
changed automatically. So at the end of the month she could make
copy of the spreadsheet and delete all the records in the generic on
to begin again for the new month?

Is that how you invisioned it?

I think that will work.

Also is there a difference between a spreadsheet and a worksheet?

Thanks again for you help

 

[Dave Peterson]

I don't think I'd use a template for this.

I'd either use a plain old .xls workbook or an addin (.xla).

You'll want to write your code so that everything gets done to the activesheet.
Then just tell the user to make sure they activate the sheet before running the
macro.

Record a macro that does the formatting that you want inside a brand new
workbook.

I got this when I selected any old column F and formatted it as a date

Option Explicit
Sub Macro1()
Columns("F:F").Select
Selection.NumberFormat = "mm/dd/yy;@"
End Sub

I changed it slightly:

Option Explicit
Sub FormatFandG()
with activesheet
.columns("F:F").NumberFormat = "mm/dd/yy;@"
.columns("G:G").numberformat = "00.0%"
end with
End Sub

Now assign the macro a nice shortcut button.

Save this workbook to a nice location with a nice name:
ReformatColumnsFandGBeforeImport.xls

Have the user open this workbook whenever they need to run the macro. (Kind of
an "open me first" at xmas.)

The open any other workbook.
Now, no matter what worksheet is active, when that use hits the shortcutkey (or
tools|macro|macros and selects that macro and clicks run), the macro will run.

Just tell them to remember to open the macro workbook first.

========
If you're interested, you could save that workbook as an addin. You could
either build a toolbar or add options to existing toolbars to run your macros.

If you only have one macro, I think I'd stay with tools|macro|macros (or even
just alt-f8) to run them.

But if you get lots, John Walkenbach has a nice procedure at:
http://j-walk.com/ss/excel/tips/tip53.htm

It's called menumaker. It's really simple to use and looks very
nice/professional.

====
For some of my addins (less than 10 macros to run), I sometimes build a toolbar.

If you're still reading <vbg>, you can see my generic toolbar builder here:
http://groups.google.com/[email protected]

 

[StrangerMike]

Thanks Dave,

I may have some more questions as I get into this today.

But for now, when you say...

"Have the user open this workbook whenever they need to run the macro
(Kind of
an "open me first" at xmas.)

The open any other workbook.
Now, no matter what worksheet is active, when that use hits th
shortcutkey (or
tools|macro|macros and selects that macro and clicks run), the macr
will run.

Just tell them to remember to open the macro workbook first."

...... do you mean they will have two worksheets open at the same time

And as long as the one with the macro is opened first the macro wil
run on both sheets?

Mik

 

[Dave Peterson]

I mean two workbooks--the macro workbook and the "data" workbook.

If you write the macro against the activesheet, then it'll work against the
sheet that's active (slightly redundant!).

But it's important to have the workbook with the macro open--it doesn't really
have to be opened first--it just has to be opened before the user can get to run
the macro.

("Open me first" might be a way of reminding the user that macro workbook must
be opened.)

 

[Dana DeLouis]

So at the end of the month she could make a

copy of the spreadsheet and delete all the records in the generic one
to begin again for the new month?

Hello. I had a hard time finding this one. Click on Managing Files, then
"Templates"


Assistance > Excel 2003 > Startup and Settings > Managing Files

 

[Dana DeLouis]

So at the end of the month she could make a

copy of the spreadsheet and delete all the records in the generic one
to begin again for the new month?

Hello. I had a hard time finding this. Here's some stuff on Excel
Templates

http://office.microsoft.com/assista...ID=CH062527921033&CTT=4&Origin=CH010036311033

In your above statement, if one opened a regular "Workbook", deleted
everything, and hit "Save", all you work would be lost. This is one
advantage of Templates. Normally, you "Open" a Template, but when you hit
"Save", it is "Saved" as a "Workbook." Therefore, you will not Overwrite
the basic template. Did I say that right? In case you were thinking of it,
an Excel Template does not act like a Style sheet as in some other programs.
When you open a Template, all the formatting does not get applied to a
current workbook. (Similar in concept to a Style sheet in FrontPage, or a
Style sheet in Mathematica).
Make sure you check out the article "Settings you can save in a Template."
From your statement above, you would want to have a template set up as best
you can "without" the data, then saved as a Template. Therefore, you will
never have to go thru the process of deleting data. When one opens the
"template", they just fill in the data. When they hit save, it is saved as
a workbook and the original template is still there to be used again.

HTH. It is a little confusing. :>)
Dana DeLouis