Alternatives to Excel VBA (Query Tables returning more than a million rows)

[gotesman]

I know how to develop VBA applications in Excel. I'm trying to
accomplish a simple task:

1) let a user pick some values from a UserForm
2) plug those values into a SQL statement
3) run the query (using an Excel QueryTable), and
4) write the results into a file (format can be XLS, TXT, CSV).


I've done this type of report many times in Excel. Steps 1-3 are no
problem.


The problem is that this particular query will return more than 1.5
million rows -- too big for even Excel 2007 to handle. After
researching, I don't think there's a great way for me to create this
report in Excel.


So here are questions about a couple of alternatives I'm considering:

1) I don't know Access (I use SQL Server/Oracle), but would it be my
best bet here? Can it elegantly handle data from Oracle? Would
learning Access and how to program it (considering I already know
Excel programming) be difficult?

2) Should I look into writing a little application in Visual Basic,
rather than VBA? I've never done it, but wonder if the learning curve
here would be minimal. I can acquire a copy of Visual Studio 2005.

3) Can anyone think of a good way to handle this in Excel? The best I
can think of is to write a loop returning 65k rows at a time into
separate sheets. Is there a better approach that would allow me to
use Excel, which I already know (and love!)?


Thanks in advance for help on any of this.

 

[MH]

I know how to develop VBA applications in Excel. I'm trying to
accomplish a simple task:

1) let a user pick some values from a UserForm
2) plug those values into a SQL statement
3) run the query (using an Excel QueryTable), and
4) write the results into a file (format can be XLS, TXT, CSV).


I've done this type of report many times in Excel. Steps 1-3 are no
problem.


The problem is that this particular query will return more than 1.5
million rows -- too big for even Excel 2007 to handle. After
researching, I don't think there's a great way for me to create this
report in Excel.

1.5 million rows in a report? Who has time to read a report with 1.5
million rows?

So here are questions about a couple of alternatives I'm considering:

1) I don't know Access (I use SQL Server/Oracle), but would it be my
best bet here? Can it elegantly handle data from Oracle? Would
learning Access and how to program it (considering I already know
Excel programming) be difficult?

No, if you use SQL Server and Oracle you already know enough about
relational databases to hit the ground running with Access. Use VBA to
build your SQL string from a Form and execute it as a pass-through query (in
the language of the database you are querying Transact SQL for SQL Server
and PL/SQL for Oracle) to build the table (or view) you want to export and
Access as your front-end can view the data using a linked table or you can
export it as a text file (or give the user the option to do both).

2) Should I look into writing a little application in Visual Basic,
rather than VBA? I've never done it, but wonder if the learning curve
here would be minimal. I can acquire a copy of Visual Studio 2005.

The learning curve would be a bit steeper using VS2005 than it would be if
you were to use VB6. For some reason MS has decided to change everything.

3) Can anyone think of a good way to handle this in Excel? The best I
can think of is to write a loop returning 65k rows at a time into
separate sheets. Is there a better approach that would allow me to
use Excel, which I already know (and love!)?

Use Access, you may struggle for a short while but the long-term benefits
will outweigh this.

Thanks in advance for help on any of this.

You're welcome.

MH

 

[Tim]

You could just use ADO directly (not difficult - plenty of examples around)
and just write the records directly to a text file.
Writing the data to separate sheets in a workbook wouldn't be that useful
for whoever has to *use* the data: presumably they aren't going to use Excel
for this.

Tim