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
Quote:
|
Originally Posted by 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
wrote in message
news:(E-Mail Removed)...
>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.
>
|
Similar Threads
