I would probably not use Excel for data  of this quantity, however, others would.

I wouldn't say that databases are much more flexible at things like column
insertion - provided you've set your Excel code up properly in the first place.
You should be using named ranges or column headings or lookups to locate your
data columns, rather than simple column letters.

Excel is good at statistics and graphs, so that's an advantage.

Excel isn't good at lots of different views of the same data - databases are
better.

However, it's up to you. Both technologies can be made to do the work.

The problem is that you've jumped right in the deep-end, with a technology that
you are inexperienced with. You will need to have the patience to rework as you
learn.

I have not set my code up properly in the first place--I've been using
relative references off of an activecell in a "key" column--just doing
it intuitively. And what a tangled web I have woven! I will try to
find some stuff to read on named ranges and column headings. Also
Pascal showed me some code which makes navigation a bit easier.

Since you would use something other than excel, what would you
use?--In other words, what should I consider to be the next step up,
if I want to do a little research? I'm mainly concerned about putting
a bunch of work  into a vehicle which will turn out to be unable to
handle the load down the line.

And what about those WebQueries that can't handle what I can do
manually--does that make sense to you???

I would use Access because I know it. Also because it is easy to create
queries and to produce different summary reports.

Access would have no problems with additional columns  being inserted because
each column would have a unique name and would be referred to by name.

The issue with webqueries is that web pages are not structured consistently.
When you look at a webpage you can say "That stuff in the centre at the bottom
is what I want, copy  it and paste it. However to the computer it is just part
of a stream of bytes.

The webquery tools work  well _if_ the data  is in a separate table on the
webpage and there are not too many tables, nested tables, etc. This depends
on the whim of the web designer and the tools he is using. A web page
designed in MS Word looks (to the computer) quite different from one handmade
in html or one prepared using DreamWeaver. These differences are not visible
to you and therefore it is easy to pick stuff off the surface of the page
without worrying about the structure underneath.

It makes no difference what application you use to extract the data. This
problem will always exist as it is at a different level from the application
itself. Information which is presented in an obscure fashion behind the
scenes will not flow into access any better than it will into excel. Actually
it could be more difficult because with excel  you can import and then tidy,
rearrange columns, etc. To import data into Access you have to import into
the correct columns in the first place and the data has to be of the right
type. A string (such as "no play") in a numeric field will cause access to
spit the whole row out.

However your WebQuery answer, while entirely accurate, does not
address the specific situation that I have (mainly because I didn't
explain it in detail). I just download the whole page, with no table
selection necessary, since WebQuery has the problems you described.

So here it is--a very simple situation: These are the steps that I do
physically and that I would like to accomplish with a macro:

1. Go to
www.donbest.com/.../all-games.shtml?20070408
(note that this page can be easily activated by hyperlink)
(note that the last 8 digits are the date, which can be easily
incremented by macro)

2. Click Edit
3. Click Select All
4. Click Copy

5. Key Control z on the keyboard. This activates a macro that I have
already created. This macro simply goes to the active cell on my
excel sheet, does a "paste special" with the HTML selection  (default),
then selects "match destination formatting", then chops off all the
extra garbage that I don't want, leaving only the table of data  that I
do want in the proper place on the worksheet.

So all I'm asking a new macro to do is steps 2,3, and 4. It blows my
mind that people are telling me it can't be done.

Use Sendkeys to send Ctrl-A Ctrl-C to Internet explorer (after you activate
the hyperlink)


Something like

Activate the hyperlink
SendKeys "^(AC)", true

Application.activate ' to bring you back to excel

Name of macro called by ctrl-z

I think this will need some experimenting and tidying but it should be enough
to get you started.

SendKeys--something else to look into--
Is there an easy-to-understand book that has all this stuff in one
place?--QueryTables, SendKeys, and I'm sure there are many more--"VBA
for Dummies" just doesn't pack this kind of horsepower--Any
recommendations?-

in regards to the subject, I was once read (I'm a newbie tho :)) that we
could use an SQL statement in Excel Macro. Can someone point me to an
example ?.

SQL can be used to import external data  from a database. If you open import
external data and do not use the wizard there is a button in MS query to do
that.

You can also use SQL strings programatically. It requires an understanding of
???DAO or ???ADO (a bit outside my area of expertise, tho I have used it in
Access.).

What are you looking to do - export external data, or just use SQL on a
worksheet?

Actually, i was just thinking if there is a way to use SQL on a worksheet.

You can record a web query to download a whole page. Then you need to
find something on the page which IS consistent for each date, such as
the table heading eg. NBA BASKETBALL, then look for one of its
subheadings, which you'll know is going to be somewhere below and to
the right within a limited range, then you'll be able to grab only the
data you want and place it elsewhere.

I tend to steer away from keeping html formatting. I recorded the
following which leaves you with a sheet having something you can work
with:
Sub Macro4()
'
' Macro4 Macro

'

'
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;www.donbest.com/.../all-games.shtml?20070408",
_
Destination:=Range("A1"))
.Name = "all-games.shtml?20070408"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Delete the data  when you've finished farming it and start again with
the next date.

I did this some time ago to get historical results from UK horseracing.

It works!!! And on the first try, too. I need to play around with it
a bit to figure it all out, but I cannot thank you enough!!

You can develop your application with Excel as long as you plan for the column
expansions in the VBA code.
You can use the form feature in Excel to be able to enter data  into your
database.

I am a MS Access developer so I would tend to use Access rather than Excel.
That being said I have development some fairly complex Excel applications using
VBA and forms.

My thoughts are you can try the Excel ... develop your database  and using VBA to
do your analysis. If you can not get it to work  a lot of your efforts will not
be lost because they can be copied to Access and you move forward from there.

You put my mind at ease with the statement that if I need to move up
in the future my excel  stuff is largely transferrable to Access.

I'm not really sure what you mean by "planning for the column
expansions in the VBA code". David Smart has suggested named ranges,
column headings, and lookups--all of which I am investigating. Do you
have anything else in mind, or should those 3 do the trick?

I enter all of my data  into the database  by copy/paste off web sites.
When you say that I "can use the form feature in Excel to be able to
enter data into the database" I'm not sure what you mean. Are you
referring to manual data entry? Please elaborate briefly.

Sorry, I'd use a database  - probably MS Access because it has pretty good
development tools for small-scale projects.

Re WebQueries. Don't know, sorry. I've never really used them.

Two votes for Access--Looks like it's time to get an "Access for
Dummies" book to peruse in my leisure moments .

Don't set a huge store by what we'd do. I can't remember who the other "vote"
was from, but I am a software professional, with many years of experience with
all types of systems, including big database  systems. Use what you think you'll
be more comfortable with.

But ... get that Access book and have a look. You might take to it right away.

Just do a Google search. I'm sure there is lots of information there.
However, don't use the term "macro" in your search, use "VBA". Macros are
VBA subroutines.