updating an excel file with vb script

bamako · March 8th, 2010, 01:29 PM

Hi there,

I would appreciate if you could see what I am doing wrong in the following vb script:

I am running the following from the command line as "Start script.vbs". This script suppose to adjust a column width and add a freezepanes and save the changes. Currently it is asking a dialoge box and to see if I would like to save this file. The file also stays open and only opens in read only view.

' script.vbs
Dim objXL
Set objXL = CreateObject("Excel.Application")
With objXL
.Workbooks.Open ("D:\files\2.xls")
.Application.Columns("B").Select
.Application.ActiveWindow.FreezePanes = True
.Application.Columns("A:AE").Select
.Application.Columns("A:AE").EntireColumn.AutoFit
.Application.Rows("3:20").EntireRow.AutoFit
.Application.Quit
End With
Set objXL = Nothing

Much appreciate your help

Shasur · March 8th, 2010, 10:06 PM

Hi

You need to write some code toe save the file

Code:

Dim objXL
Set objXL = CreateObject("Excel.Application")
With objXL
    	.Workbooks.Open ("D:\files\2.xls")
        .Application.Columns("B").Select
        .Application.ActiveWindow.FreezePanes = True       
        .Application.Columns("A:AE").Select
        .Application.Columns("A:AE").EntireColumn.AutoFit
        .Application.Rows("3:20").EntireRow.AutoFit
	.Application.Quit
.Workbooks("2.xls").Save
.Workbooks("2.xls").Close (False)
   End With

Set objXL = Nothing

Cheers
Shasur

bamako · March 9th, 2010, 04:46 AM

Thanks Shasur.

One more thing, I would like to call this code from a java programme e.g.

Code:

Runtime.getRuntime().exec("cmd /c start calling.vbs 'D:\Zia_ALU\HA\CR\mytest\final\2.xls' ")

Is there a way to pass the file name as a parameter to the above vb script?

bamako · March 9th, 2010, 05:48 AM

Thanks for the response. The script is working but I need to make another adjustment so I pass the file name and file path as parameters to this script. Any idea how this can be done so If I call somthing like

"Start script.vbs pfilepath filepath"

Code:

'script.vbs
Dim objXL
Dim sfilname = pfilename
Dim sfilepath = pfilepath
Set objXL = CreateObject("Excel.Application")
With objXL
    	'.Workbooks.Open ("D:\2.xls")
    	.Workbooks.Open (sfilepath + sfilname)
    	'Adjust column width
    	.Application.Cells.Select
    	.Application.Cells.EntireColumn.AutoFit      	
        'Adjust row height
        .Application.Rows("3:500").EntireRow.AutoFit
        'FreezePane
        .Application.Range("B5").Select
        .Application.ActiveWindow.FreezePanes = True   
        'Auto filter
        .Application.Rows("4:4").Select
    	.Application.Selection.AutoFilter
    	.Application.Range("B5").Select
	.Workbooks(sfilname).Save
	.Workbooks(sfilname).Close (False)
	.Application.Quit
End With
Set objXL = Nothing