Output to Excel (but without saving a file)

[Big Pat]

Hi,

I need to output a table to Excel frequently, but I don't need to save the file. (Basically, another macro in Excel cleans it up by removing various errors etc. then I paste it back into another table. Doesn't make a lot of sense I know but it's a task I'll need to repeat often as we migrate data to a new system in the next couple of months)

So, the macro asks me to specify a location and filename, then it opens Excel. But is there a way to get it to just open Excel, as if you had clicked File > New? It's not a big deal, but it would save me having to remember to clear out the clutter I don't need.

 

[ted.martin]

You do need a path but if you like, you could then use the Kill function to delete the Excel file in your chosen directory once you have finished with it.

The OutputTo code does need a path so you have no choice at that stage but clearing up afterwards is good housekeeping.

 

[Big Pat]

Thanks for the response. The "Kill" function? Sounds ominous! I'm not aware of that one.

How would I implement this? It doesn't sound like it would go in the Access macro as I can't see that I would want to kill Excel having just launched it.

Maybe you mean it should go in the Excel macro? I have a custom button on my toolbar which runs the macro. I RECORDED this macro by the way. I don't know enough about this stuff to write one from scratch.

But then I wouldn't want to kill Excel without having had a chance to copy the corrected data back to Access.

Confused!

 

[ted.martin]

If you search for Kill you will find the code you need. The only question is therefore when to call it.