XLS object created in function only...

[cheuschober]

Datasheet / XLS object created in function only...

Okay here's an interesting ponderance--

We all know the DoCmd.SendObject or the DoCmd.Export etc, with acFormatXLS. But, does anyone know if its possible to create an XLS object that exists in the vba itself and can be passed to other functions for alternate work?

Regards,
~Chad

Edit: This is to say, an object that doesn't have a stored location on a physical disk?

 

[The_Doc_Man]

I assume you are thinking about the moral (immoral?) equivalent of what a UNIX command passes between sub-commands on a line joined by a pipe symbol. I.e. default OUTPUT channel pipes to next command's default INPUT channel.

The answer is a tentative no, on the grounds that Office member programs are oriented towards the component object model. This model requires extensive support information - for XLS, like collections of workbooks, worksheets, rows, and columns; things that don't get finalized (created or updated) until you do a SAVE to disk.

A further issue is that "doing the alternate work" usually requires RANDOM access to the collection structures I was discussing, but the "pipe" model of UNIX is not a random-access paradigm. I doubt that you could get a "simple" SendObject or Export to support such a paradigm.

So for that reason, I would say you probably cannot do what you just described. But ya know, every time I say it can't be done, someone else says there is an exception. So consider my answer as only indicative, not definitive.

 

[cheuschober]

I assume you are thinking about the moral (immoral?) equivalent of what a UNIX command passes between sub-commands on a line joined by a pipe symbol. I.e. default OUTPUT channel pipes to next command's default INPUT channel.

The answer is a tentative no, on the grounds that Office member programs are oriented towards the component object model. This model requires extensive support information - for XLS, like collections of workbooks, worksheets, rows, and columns; things that don't get finalized (created or updated) until you do a SAVE to disk.

A further issue is that "doing the alternate work" usually requires RANDOM access to the collection structures I was discussing, but the "pipe" model of UNIX is not a random-access paradigm. I doubt that you could get a "simple" SendObject or Export to support such a paradigm.

So for that reason, I would say you probably cannot do what you just described. But ya know, every time I say it can't be done, someone else says there is an exception. So consider my answer as only indicative, not definitive.

First off, I appreciate the insight greatly!

Specificially I'm plugging the results into email attachments which are then automailed but I can't use docmd.sendobject because the email prog doesn't work well with that (LotusNotes if you're curious -- currently attempting KeithIT's bit o'code in the repository). Rather than generate 20 to 30 local files a night that are simultaneously found in my outgoing mailbox and the recipient's off-site inbox we'd like to create the object and pass them straight as attachments into the outgoing mail message without forcing an embedded object that'll bloat the db. XLS was suggested as a route to look into only because of its compatibility with access but any way to basically get a datasheet-like set of results from a query that can be passed as an attachment through the vb works, be it pdf or xls or, well, just about anything.

So with that in mind, still think it's impossible?

Regards,
~Chad