how to write a batch file to format excel?

Question

i'd like to write a batch file that would open a static Excel document and format it (from say 10 pages to 2 pages, taking out the empty columns) so that it is print ready. any ideas on how to format the excel document using my batch file? It could be a .VBS file as well I suppose.

Answer 1

I think you want some VBA with this.

You can also use Perl Spreadsheet::WriteExcel , Spreadsheet::SimpleExcel , Win32::OLE

Answer 2

It could be a .VBS file as well I suppose.

Try this:

'takes out (hides) empty columns in 1st worksheet
'drag and drop excel file into this .vbs

set xl = createobject("excel.application")
set wb = xl.workbooks.open(wscript.arguments(0))
set ws = wb.worksheets(1)
xl.visible = true

numCols = ws.usedrange.columns.count

for col = numCols to 1 step -1
  set r = ws.columns(col)

  if xl.worksheetfunction.counta(r) = 0 then
    'column has no values, so hide it
    r.columnwidth = 0

    'if you want to delete the column
    'r.delete
  end if
next

Answer 3

Does it need to be an "external" tool... What if you define the processing functions right in the file as a VBA-script?

Once done, if you save the file containing those macros as a XLT (template), all further copies created with this template will contain the macros.

There is an event-handler called Workbook_Open that is being excecuted when the workbook has been opened.

If you need further assistance to this, let me know. regards