Spreadsheet, Excel, Frontpage Help

[rinkadink]

Hello -

FYI:
Office 2003
Win XP
using IE.

I manage a softball team website for fun and I consider working on it a
hobby. I am a Frontpage novice and am even worse when it comes to working
with Excel.

The team coach keeps stats, and has given them to me in an Excel file. He
would like me to post them online for the team. I believe I can do this at
least two different ways.

1. Upload the file and make it a clickable link so the visitor can open it
on their own and save it on their computer.

2. I can save/copy the data from the stats and somehow save it as a webpage
or have it be viewed on the page itself, but have NO IDEA how to do this.
The coach really wants me to somehow publish them online, so you don't have
to save it to your own PC, but I can't figure this out. I've been reading,
and posting messages.

Any insight would be appreciated. If you would like, please visit the site
at www.mulliganssoftball.com, and click on "Statistics" on the left to view
the stats, or offer any advice.

Thank you for all of your help.

rinkadink

 

[Andrew Murray]

In Excel, save the worksheet as HTML (from File > Save As (or File > Export
as the case might be) then import that html file into your web, link to it
and publish it.

You can link direct to the xls file, but it relies on the user having Excel
on their machine (or the Excel reader, similar idea as with PDF files
requring Adobe reader to be viewed) but you sound like you want to avoid
doing that, so exporting the excel file to HTML is the best bet.

You might lose the formatting etc, so be aware of this, and it might take
some tweaking in Frontpage to rearrange the columns and rows in to a
readable arrangement.

The transition of excel to HTML may not necessarily go as smoothly as you
would like.

 

[Nicholas Savalas - http://savalas.tv]

Dear rinkadink,

My friend, you are about to enter the beautiful world known as the
"Microsoft Office Suite" - an amazing place where every program loves
every other program, and all data is shared by all programs. Now you
will find out why Microsoft Office is the most popular and best selling
software of all time. An incredibly difficult set of programming and
data conversion tasks will suddenly become easy and routine for you to
do. Let us bow our heads in a moment of reflection, and take three deep
breaths. Ready? Good. Let us begin.

Data is data is data. A good place to store, retrieve, search for, and
sort data is a database. An excellent database program for almost all
small and medium sized tasks, like yours, is MS Access - part of the
Office suite. Open Access, and create a new, blank database on your
hard drive. Call it, say, stats.mdb. Now let us get some data.

To get the Excel files into your new Access database, go to File -> Get
External Data -> Import... and change the FILE TYPE to import (at the
bottom of the import window) to .xls files. Then, browse to the Excel
file you want to import, and format the incoming data as it suits you.
Access will save the data in a TABLE with the name of the original
sheet. Right click the table, and rename it to something more
descriptive of those particular stats (I'm guessing by date here) like
jan_06, feb_06, etc. Import all the Excel spreadsheets, one by one,
like this, remember to rename the new tables with your new, descriptive
naming convention. You'll see why in a second. When you are done, close
Access, and open FrontPage.

Open your website, and go to File -> Import... and browse to your
database on your hard drive. While importing the database, FP will ask
you if you want to create, and name, a new 'Database Connection'. Do
you? Sure! Let's name it, say, stats, and not the default 'Database 1'.
Again, you will see why in a second. Then, FP will ask you if you want
to store your database in the recommended 'FPDB' folder. Again, the
answer is: Sure!

Next, let us display the data from the database. Create a new webpage
in your sites' theme (empty out an existing page, and use File -> Save
As... so we don't lose the existing page) and name it, say,
stats_template.asp, so we can create many different database results
pages from the one new template page. Click the place on the page where
you would like to show the records, and choose Insert -> Database
Results. A groovy feature of FP, the 'Database Results Wizard', will
open. Now we are in the home stretch.

The database connection source will be our newly made 'stats'
connection (if you have many databases, the naming we did earlier makes
life easier than the default name 'Database 1', see?). The data source
(TABLE) will be, say, jan_06 (see why we named the tables more
descriptively in the beginning?), and then choose the fields, formats,
queries, etc. that suit you. When you are finished, save the page as,
say, stats_jan06.asp, and go preview it, live in your browser. Do the
same for all the other tables (that used to be Excel files), and save
them each with descriptive names according to your naming convention.

Finally, change your present page:

http://www.mulliganssoftball.com/statistics.html

to include links to your new data access pages, and on every data
access page, include links back, etc. Now, rinkadink, you are no longer
a website hobbyist, you are a full-fledged database administrator and
webmaster. Women love you, men envy and admire you. Life is sweet.

The FrontPage Database Results Wizard can also make a search (query)
for your page; it can limit the number of results per page; you can
format the text as you wish; link to individual players stats, if they
exist; etc. etc. etc. You will play with it. You will learn to use it.
You will be amazed at yourself.

Every month, when you get the new stats, open Access FROM WITHIN
FrontPage by double-clicking the stats.mdb file in the /fpdb/ folder,
and import your new Excel data into a new table with a descriptive
name. When you close Access, FP will automatically upload the new
version of your database. When that is done, open your original
stats_template.asp page, create the new results, and Save As...
whatever.asp and link to it.

You are sensational human being, rinkadink, and I am going to write a
song about you. Thanks for the lyrics. Good luck.

Nicholas Savalas - http://savalas.tv

 

[Stefan B Rusynko]

Nicholas

While I agree you present a very elegant and detailed correct solution
- it makes several presumptions that may cause the user problems

a) that the Excel spreadsheet is in a valid data table layout
(if not the Access import will fail and you can not use a database)
b) that the users site is on a Windows Hosted Server that supports Access DB and ASP
(if not the whole exercise is futile)

IMHO
The simplest solution (w/o presumptions) is to save the Excel file as html and File Import it into the FP site
(or even copy paste from the Excel sheet to the FP page)

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Dear rinkadink,
|
| My friend, you are about to enter the beautiful world known as the
| "Microsoft Office Suite" - an amazing place where every program loves
| every other program, and all data is shared by all programs. Now you
| will find out why Microsoft Office is the most popular and best selling
| software of all time. An incredibly difficult set of programming and
| data conversion tasks will suddenly become easy and routine for you to
| do. Let us bow our heads in a moment of reflection, and take three deep
| breaths. Ready? Good. Let us begin.
|
| Data is data is data. A good place to store, retrieve, search for, and
| sort data is a database. An excellent database program for almost all
| small and medium sized tasks, like yours, is MS Access - part of the
| Office suite. Open Access, and create a new, blank database on your
| hard drive. Call it, say, stats.mdb. Now let us get some data.
|
| To get the Excel files into your new Access database, go to File -> Get
| External Data -> Import... and change the FILE TYPE to import (at the
| bottom of the import window) to .xls files. Then, browse to the Excel
| file you want to import, and format the incoming data as it suits you.
| Access will save the data in a TABLE with the name of the original
| sheet. Right click the table, and rename it to something more
| descriptive of those particular stats (I'm guessing by date here) like
| jan_06, feb_06, etc. Import all the Excel spreadsheets, one by one,
| like this, remember to rename the new tables with your new, descriptive
| naming convention. You'll see why in a second. When you are done, close
| Access, and open FrontPage.
|
| Open your website, and go to File -> Import... and browse to your
| database on your hard drive. While importing the database, FP will ask
| you if you want to create, and name, a new 'Database Connection'. Do
| you? Sure! Let's name it, say, stats, and not the default 'Database 1'.
| Again, you will see why in a second. Then, FP will ask you if you want
| to store your database in the recommended 'FPDB' folder. Again, the
| answer is: Sure!
|
| Next, let us display the data from the database. Create a new webpage
| in your sites' theme (empty out an existing page, and use File -> Save
| As... so we don't lose the existing page) and name it, say,
| stats_template.asp, so we can create many different database results
| pages from the one new template page. Click the place on the page where
| you would like to show the records, and choose Insert -> Database
| Results. A groovy feature of FP, the 'Database Results Wizard', will
| open. Now we are in the home stretch.
|
| The database connection source will be our newly made 'stats'
| connection (if you have many databases, the naming we did earlier makes
| life easier than the default name 'Database 1', see?). The data source
| (TABLE) will be, say, jan_06 (see why we named the tables more
| descriptively in the beginning?), and then choose the fields, formats,
| queries, etc. that suit you. When you are finished, save the page as,
| say, stats_jan06.asp, and go preview it, live in your browser. Do the
| same for all the other tables (that used to be Excel files), and save
| them each with descriptive names according to your naming convention.
|
| Finally, change your present page:
|
| http://www.mulliganssoftball.com/statistics.html
|
| to include links to your new data access pages, and on every data
| access page, include links back, etc. Now, rinkadink, you are no longer
| a website hobbyist, you are a full-fledged database administrator and
| webmaster. Women love you, men envy and admire you. Life is sweet.
|
| The FrontPage Database Results Wizard can also make a search (query)
| for your page; it can limit the number of results per page; you can
| format the text as you wish; link to individual players stats, if they
| exist; etc. etc. etc. You will play with it. You will learn to use it.
| You will be amazed at yourself.
|
| Every month, when you get the new stats, open Access FROM WITHIN
| FrontPage by double-clicking the stats.mdb file in the /fpdb/ folder,
| and import your new Excel data into a new table with a descriptive
| name. When you close Access, FP will automatically upload the new
| version of your database. When that is done, open your original
| stats_template.asp page, create the new results, and Save As...
| whatever.asp and link to it.
|
| You are sensational human being, rinkadink, and I am going to write a
| song about you. Thanks for the lyrics. Good luck.
|
| Nicholas Savalas - http://savalas.tv
|

 

[Kathleen Anderson [MVP - FrontPage]]

Also, c) if you are using FP2003, you cannot open the database in Access
from within FP, you have to export it to your PC and open it from there.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
FrontPage Support: http://www.frontpagemvps.com/

Nicholas

While I agree you present a very elegant and detailed correct solution
- it makes several presumptions that may cause the user problems

a) that the Excel spreadsheet is in a valid data table layout
(if not the Access import will fail and you can not use a database)
b) that the users site is on a Windows Hosted Server that supports Access
DB and ASP
(if not the whole exercise is futile)

IMHO
The simplest solution (w/o presumptions) is to save the Excel file as html
and File Import it into the FP site
(or even copy paste from the Excel sheet to the FP page)

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Dear rinkadink,
|
| My friend, you are about to enter the beautiful world known as the
| "Microsoft Office Suite" - an amazing place where every program loves
| every other program, and all data is shared by all programs. Now you
| will find out why Microsoft Office is the most popular and best selling
| software of all time. An incredibly difficult set of programming and
| data conversion tasks will suddenly become easy and routine for you to
| do. Let us bow our heads in a moment of reflection, and take three deep
| breaths. Ready? Good. Let us begin.
|
| Data is data is data. A good place to store, retrieve, search for, and
| sort data is a database. An excellent database program for almost all
| small and medium sized tasks, like yours, is MS Access - part of the
| Office suite. Open Access, and create a new, blank database on your
| hard drive. Call it, say, stats.mdb. Now let us get some data.
|
| To get the Excel files into your new Access database, go to File -> Get
| External Data -> Import... and change the FILE TYPE to import (at the
| bottom of the import window) to .xls files. Then, browse to the Excel
| file you want to import, and format the incoming data as it suits you.
| Access will save the data in a TABLE with the name of the original
| sheet. Right click the table, and rename it to something more
| descriptive of those particular stats (I'm guessing by date here) like
| jan_06, feb_06, etc. Import all the Excel spreadsheets, one by one,
| like this, remember to rename the new tables with your new, descriptive
| naming convention. You'll see why in a second. When you are done, close
| Access, and open FrontPage.
|
| Open your website, and go to File -> Import... and browse to your
| database on your hard drive. While importing the database, FP will ask
| you if you want to create, and name, a new 'Database Connection'. Do
| you? Sure! Let's name it, say, stats, and not the default 'Database 1'.
| Again, you will see why in a second. Then, FP will ask you if you want
| to store your database in the recommended 'FPDB' folder. Again, the
| answer is: Sure!
|
| Next, let us display the data from the database. Create a new webpage
| in your sites' theme (empty out an existing page, and use File -> Save
| As... so we don't lose the existing page) and name it, say,
| stats_template.asp, so we can create many different database results
| pages from the one new template page. Click the place on the page where
| you would like to show the records, and choose Insert -> Database
| Results. A groovy feature of FP, the 'Database Results Wizard', will
| open. Now we are in the home stretch.
|
| The database connection source will be our newly made 'stats'
| connection (if you have many databases, the naming we did earlier makes
| life easier than the default name 'Database 1', see?). The data source
| (TABLE) will be, say, jan_06 (see why we named the tables more
| descriptively in the beginning?), and then choose the fields, formats,
| queries, etc. that suit you. When you are finished, save the page as,
| say, stats_jan06.asp, and go preview it, live in your browser. Do the
| same for all the other tables (that used to be Excel files), and save
| them each with descriptive names according to your naming convention.
|
| Finally, change your present page:
|
| http://www.mulliganssoftball.com/statistics.html
|
| to include links to your new data access pages, and on every data
| access page, include links back, etc. Now, rinkadink, you are no longer
| a website hobbyist, you are a full-fledged database administrator and
| webmaster. Women love you, men envy and admire you. Life is sweet.
|
| The FrontPage Database Results Wizard can also make a search (query)
| for your page; it can limit the number of results per page; you can
| format the text as you wish; link to individual players stats, if they
| exist; etc. etc. etc. You will play with it. You will learn to use it.
| You will be amazed at yourself.
|
| Every month, when you get the new stats, open Access FROM WITHIN
| FrontPage by double-clicking the stats.mdb file in the /fpdb/ folder,
| and import your new Excel data into a new table with a descriptive
| name. When you close Access, FP will automatically upload the new
| version of your database. When that is done, open your original
| stats_template.asp page, create the new results, and Save As...
| whatever.asp and link to it.
|
| You are sensational human being, rinkadink, and I am going to write a
| song about you. Thanks for the lyrics. Good luck.
|
| Nicholas Savalas - http://savalas.tv
|

 

[Nicholas Savalas - http://savalas.tv]

Dear Stefan,

Thank you for your flattering judgement of my solution. It is always
nice to read approving words from an MVP; especially since I have some
ongoing issues with a few of your colleagues who share that title.

Your analysis is correct; most people who use Excel use the header
cells and other default formatting that make importing sometimes
difficult to achieve in Access, and gums up the whole process. I
considered that (and other issues, like he may be hosted on a *ix
server, might not have Access at all because it is not part of the
standard Office package, etc.) when writing my response. The
interesting part of NOT being an MVP is that I can present the exact
Microsoft solution without a hint of bias.

I am sure that you are aware that publishing an interactive Excel sheet
presumes that the visitor has Excel (and its attendant web components)
installed; a Save As Web Page... solution leaves that nasty Office XML
code (which, by the way, often reveals details like author name and
original file details - and the Remove Office XML add-in does not do a
complete job), even if you copy and paste right from the spreadsheet.

I offered my solution because I want to remind everybody here that
FrontPage was not meant to be a stand-alone solution, but, rather, a
part of the 'Office Suite'; a sort-of industrial (read: not 100%
professional) level set of tools that make the previously
impossible-to-solve scenario almost routine. In my own humble opinion,
just the ability for a 'hobbyist' level site manager to create a page
where database results were searchable and not offered "en tota" (as
the cut and paste solution dictates) would, in itself, be enough to
make me buy Office 2003+ Pro and switch to a Windows Server host. I
remember when I did, many years ago.

When I say that the Office Suite set of tools is industrial/not 100%
professional, I know you know what I mean - remember that I did not
offer the correct solution, which would involve building (in Visual
Studio.NET) a web-based administration area (on a front-end Windows
Advanced Server with all of it's attendant sub-servers connected to
offline, dedicated SQL Server) allowing secure, direct input access of
data entry for the records-keeper.

See, when you are not an MVP, sometimes you can quietly whisper words
(like 'Visual Studio.NET' and 'Visual Studio WebDeveloper') here at the
FrontPage forum that are shouted out loud at other forums, without any
fear of retribution or retaliation. Freedom means never having to say
you're sorry.

As a final comment, to all MVP's: I have noticed that some of you are
telling visitors with problems getting Excel into FrontPage to go post
in the Excel forums - it is unacceptable to tell visitors that. It is
OK to refer to Microsoft FrontPage as only an integral part of a much
larger Office Suite. Remember 'SharePoint', and 'Office Web
Components'?

But, again, thank you for your response, Stefan. I always considered
this a fun place, and you one of the fun people here.

Nicholas Savalas - http://savalas.tv

 

[Nicholas Savalas - http://savalas.tv]

Dear Kathleen,

I am, without question, your biggest fan. It was your site (especially
recommended:

http://www.spiderwebwoman.com/resources/dbrwtipsandtricks.asp

a Godsend for the FrontPage user, no matter what their experience
level) that I found when I was first trying to customize the Database
Results Wizard output in, like, 1997 or thereabouts. Your site is the
Holy Grail of the DBRW, and you, its most beloved yet under-appreciated
priestess.

Your comment brings up a very sore spot for me, and I went to war with
Microsoft over that one. I still have FP 2000 installed on my computer
because of that impossibly stupid "fix" they threw at us without any
warning.

I remember being at a Microsoft Developers conference, and asking about
that very point. I was told, by the MS speaker, that I should be using
the Database Interface Wizard any time I wanted to use a database in
FrontPage-driven web site. In response, I recall making an brief, but
accurate, analysis of the speaker's relationship to the lower primates,
and the next thing I knew, I was being escorted, in a hammer-lock, to
the door by four security guards, while the speaker yelled from the
stage that "I could forget about getting my official Microsoft
Developers Network T-shirt". Heck, that was the whole purpose of my
attendance at the conference in the first place.

I returned my MCSE cert within the hour.

Then, after some reflection, I conjectured that anyone who would make a
statement as stupid as that one was probably wrong in the first place.
After hours of testing, my theory was proven. I found that the issue of
being unable to open .mdb files directly through FrontPage 2003 was
related to two issues: server configuration (especially its handling of
..ldb files), and whether or not the database had been CREATED by
FrontPage 2003. I advised, therefore, in my solution, to create the
database in Access separately, and import it into FrontPage as a
discrete step.

Shhh - don't tell anyone, but every site I author or manage uses some
sort of database-driven feature, and I never, NEVER, would consider the
using FrontPage's built-in connections or its database creation
utility. My advice to all: don't let FrontPage 2003 handle your
databases. If you already have a database in place and want to edit it
without, as you have correctly pointed out, having to publish the
database to your hard drive for additions, I have found a miraculous,
one page, universal Access database editor at:

http://www.codeproject.com/asp/ute.asp

The mere fact that you found that one insignificant issue from within
my entire explanation justifies my undying admiration for you.

Nicholas Savalas - http://savalas.tv

 

[Joe Rohn]

Hi Nicholas,

Just wanted to say that I enjoy reading your posts..You certainly show an
ample amount of reason in your reasoning! While I may not agree 100%
with all of your conclusions..I will say that you have shown that it *is*
possible to teach an old dog new tricks (at least to this old dog) <g>

--
Joe

Microsoft MVP FrontPage

FrontPage Portal and Users Forums:
http://www.timeforweb.com/frontpage