Downloading big datasets daily?

[marulo]

Hi, I am new to this forum and have a question that might be too simple or
easy for you guys. I'll try to do my best describing my problem and what I am
doing so far to solve it...

I want to download a daily database from a website. I set up a program that
tells the computer to go everyday and opens an excel file that has a web
query embbeded to it. The query goes to the website and selects the table I
need and downloads it. Then, the program saves it with a csv format and
name=date.
So far so good....The problem is that the website now provides a table with
more than 65536 observations [maximum allowed by my MS Excel 2002 SP1].
Therefore, I am missing a part of the dataset I need...

1) I was thinking about migrating to access and use the same query. I never
used access and from the quick search I did, it doesn't seem like the same
query can be used.

2) What if I set the web query in excel to split the dataset in pieces of
60K or less lines and place them in different parts of the file?

Any suggestion?

Thanks in advance

 

[Jim Thomlinson]

I recommend the Access approach. You can analyze the table in Access that you
create wiht Excel via query or pivot table, but you are going to keep running
into difficulties using Excel to try to store and manipulate that much data.

 

[TK]

marulo:

Not sure how you are bringing the recordset in but
you could use CopyFromRecordset

CopyFromRecordset looks at the end of file (EOF)
not the row limitation 65,536

So to import more than 65,536 rows you need to tell Excel
where to put the additional records

For example the following would put the first 65,536
records of a four field recordset in columns A B C D
and the balance in columns E F G H

'/Starts at A1
Worksheets("Sheet3").Range("A:A,D").CopyFromRecordset rs

'/If more than 65,536 records you must add a second line

'/Starts at E
Worksheets("Sheet3").Range("E:E,H:H").CopyFromRecordset rs

Good Luck
TK

:
...............

So far so good....The problem is that the website now provides a table with
more than 65536 observations [maximum allowed by my MS Excel 2002 SP1].
Therefore, I am missing a part of the dataset I need...
2) What if I set the web query in excel to split the dataset in pieces of
60K or less lines and place them in different parts of the file?

Any suggestion?

Thanks in advance