At the moment I have scripts which run an sql query then generate a CSV file, then send it via email.
The recipients open it in excel.
CSV is very basic. It presents the information, nothing more, no formatting, no column width specification, just pure data.
Is there a better way/file format I can present the data in, which when double clicked on will open MS Excel, and which is within the abilities of a fairly amatur self-taught vbscript programmer?
(when I say ‘self taught’ I will usually mean self motivated teaching with a lot of help from the SDMB! )
Ah, hello there Lobsang and welcome to the wonderful world of Excel spoofing.
Short answer - I don’t know of any alternative format that is easy to generate, encodes column widths and similar formatting info and will look ‘pretty’ when opened up in excel. You might want to consider using excel automation to generate a true excel format file - it’s not that hard, though it can be frustrating sometimes. Here’s a sample script that I just ran as a .vbs file and the results looked pretty good:
set xl = createObject("Excel.Application")
set wb = xl.workbooks.add
set ws = wb.worksheets(1)
ws.cells(1, 1) = "Value for first row and column"
ws.cells(1, 2) = "Another column heading?"
ws.range(ws.cells(1, 1), ws.cells(1, 2)).columns.autofit
wb.saveas "c: empfiles\myexcel.xls"
wb.close
xl.workbooks.close
xl.quit
There’s a lot more that can be done with excel automation - if you have any experience writing msexcel macros in VBA, then all the objects and methods are the same, just slight syntax differences in VBA/VBS and some convention differences because you’re ‘outside’ of the excel file instead of within it.
If you don’t want to go through this, you can create a text with tabs format or an HTML file with tables in it, call the file .xls, and excel will open either of them without much trouble. In fact, the HTML in excel might look ‘pretty’ enough for you - it does auto column resizing and that sort of thing.
Thanks chrisk
I tried your excel code and it works. Also the other ideas are worth considering (html as a .xls file) I know a bit about vb coding, and a bit more about html. not much about excel vb macro coding but I’m a quick learner (or rather efficient assimilator of knowledge)
In fact… I could quite easily modify some of my other scripts which already generate html code… to generate excel files.
One thing to beware of if you experiment with the excel automation is that if you don’t close off the excel application properly, then the excel.exe process keeps on running behind the scenes even after your vbscript has died. Can get very frustrating, and I’ve seen servers get so clogged with excel that they’ll refuse to open any more. This is hard to avoid when you get unexpected errors in the middle of your code while the excel is open.
The cleanup commands I’ve given up are probably a bit of overkill, (close workbook, tell application to close ALL workbooks it might have open, quit application,) but they do work pretty well when you run them all.
)