command for vbscripts 2

[brunello67]

Hi
i'm very new and i dont know VBScript.
i have to filter some raws in an excel file and i found a VBScript code that is working but it is partial.
this is it:

Dim objExcel
Dim objWorkbook
Dim objWorksheet

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("\\SERVER\files claudio\assett.xls")
Set objWorksheet = objWorkbook.Worksheets("test")

objExcel.Visible = True

With objWorksheet
.Range("C1").AutoFilter 3,"EBD"
End With


but i need to save the file and to rename
is there anybody that can continue this code with missing part?
thanks
Bruno
Italy

 

[xwb]

Have a look at SaveAs documentation. The main difference between VBA and VBScript is VBScript only has positional parameters. The syntax of saveas is

SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

If you wish to specify a password in VBA, you could do something like

workbook.SaveAs FileName:="xxx.xlsx", Password:="LetMe1n"

but in VBscript, you have to specify all the parameters up to the password. The FileFormat is specified in Excel File Format

objWorkbook.SaveAs "xxx.xlsx", 51, "LetMe1n"

 

[Don Child]

Hi brunello67,

Along with what XWB said, here are some constants that I've been using in my recent work, and other snippets from the app:

Const xlTextFormat = 2
Const xlTextQualifierDoubleQuote = 1
Const xlDelimited = 1
Const xlOverwriteCells = 0
Const xlWorkbookNormal = -4143
Const XLSX_Workbook = 51
Const xlExpression = 2
Const xlAscending = 1
Const xlHeader = 1
Const xlYes = 1
'...
'...
'...
Set Excel_o = CreateObject("Excel.Application")
'Import a Tab-Delimited text file
Excel_o.WorkBooks.OpenText TSV_File_Path_s,,,xlDelimited,,,,, True
'...
'...
'...
Set WorkBook_o = Excel_o.Workbooks( TSV_BaseFileName_s )
Set WorkSheet_o = WorkBook_o.Worksheets(1)
WorkSheet_o.UsedRange.ClearContents
WorkSheet_o.Cells.NumberFormat = "@"
'...
'...
'...
WorkSheet_o.Columns.Autofit
Set Range_o = WorkSheet_o.Cells
SetGreenBar( Range_o )
WorkSheet_o.Rows("1:1").Select
Excel_o.ActiveWindow.SplitColumn = 0
Excel_o.ActiveWindow.SplitRow = 1
Excel_o.ActiveWindow.FreezePanes = True
WorkSheet_o.Range("A1").Entirerow.Font.Bold = True
WorkBook_o.Saved = True
'...
'...
'...

' Set Automatic Filter options at the top of each column
Range_o.AutoFilter

'// Suppress Overwrite/Replace prompt
Excel_o.DisplayAlerts = False
WorkBook_o.SaveAs XLS_File_Path_s, XLSX_Workbook
WorkBook_o.Close
Excel_o.Quit

Wscript.Quit
return

'...
'...
'...
Sub SetGreenBar( Range_o )
Dim AlternateRowsFormula_s
AlternateRowsFormula_s = "=mod(row()+1,2)"
Range_o.Interior.ColorIndex = 0
Range_o.FormatConditions.Add xlExpression, , AlternateRowsFormula_s
Range_o.FormatConditions(1).Interior.Color = RGB( 198, 224, 180 )	' Light Green
End Sub