Excel Templates

Hello, this is my first post to this forum.

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

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

Please read this scenario and tell me if what I am thinking is possible
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 that
need to be reformatted before sending them to me for import.

She is not real knowledgable about excel either, but what I hoped I
could do for her is: create a template with the couple of columns
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 this
template? Or do you open the template first and some how open the
original excel file within the template?

Can anyone help?
Thanks
Mike


---
Message posted from http://www.ExcelForum.com/

Hi!

Welcome!

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

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

Open the worksheet.
ToolsMacroRecord new macro.
Choose a "hotkey"

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

Do all the formatting. Then exit the recorder (either through the
pop-up menu or ToolsMacroStop recording.

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

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

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

Alf


---
Message posted from http://www.ExcelForum.com/

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 any
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 when
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 she
receives her new file?

Thanks Mike


---
Message posted from http://www.ExcelForum.com/

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 a
copy paste into the worksheet that had the macro, the formatting
changed automatically. 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?

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.


---
Message posted from http://www.ExcelForum.com/

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 he
http://groups.google.com/groups?thre...5B41%40msn.com



"StrangerMike " wrote:

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 any
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 when
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 she
receives her new file?

Thanks Mike

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

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

...... 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 will
run on both sheets?

Mike


---
Message posted from http://www.ExcelForum.com/

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

"StrangerMike " wrote:

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

..... 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 will
run on both sheets?

Mike

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

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





"StrangerMike " wrote in
message ...
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 a
copy paste into the worksheet that had the macro, the formatting
changed automatically. 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?

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.


---
Message posted from http://www.ExcelForum.com/

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



"StrangerMike " wrote in
message ...
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 a
copy paste into the worksheet that had the macro, the formatting
changed automatically. 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?

Is that how you envisioned it?

I think that will work.

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

Thanks again for you help.