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

 

[alon]

I tried to respond days ago, but Google Groups apparently ate my message, despite telling me my message will show up shortly. (Google Groups has been acting very strangely -- it's sometimes days behind on this newsgroup, it often won't fully load a thread, now these posting problems. I should start a thread on another newsgroup).


Anyways, thanks for your response, Tim. I'm only vaguely familiar with the ADO concept, although I've certainly seen a lot written about it in books and online. I'm unclear about what I can do with the interface: is it something I use to create web-based or application-based tools? What software do I use to develop an ADO program? Can you please point me to an example of someone using ADO to query a database and output the results to a text file.

I Google'd around for a bit and see plenty of sites introducing the ADO syntax, but I'm still unclear about what this is ultimately all about.


And to MH -- I should have been more clear. This isn't really a "report", rather this is a list of customers that match certain criteria specified in a userform (for example with order_date > 1/1/2007). For the record, I think I'll follow your advice and go with an Access-based solution, using this as an opportunity to add another database technology to my toolbox (unless of course I discover that ADO is a much better approach!).

Thanks,
Alon

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


wrote in message
news:[email protected]...
>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.
>