Initialized Excel application object
This are the few lines you must include in your script whenever you need to do Excel automation.
' Create the excel object
Set objExcel = CreateObject("Excel.Application")
' Do not show the excel workbook
objExcel.Visible = False
Open workbook to process
You can use this for csv file also
Set objWorkbook = objExcel.Workbooks.Open (filePath)
Create an object to represent your first worksheet
Set objWriteSheet = objWorkbook.Worksheets(1)
To find the last row or last column in the worksheet
These 2 lines help to find the last column or last row in your worksheet which has data occupied.
numRows = objWriteSheet.UsedRange.Rows.Count numCols = objWriteSheet.UsedRange.Columns.Count
To delete column
These loop will loop through all the column from right to left and it will look for column with value of "test4" or "test5" and delete the column. Please note that it is always better to loop from right to left (column 7->column 6->column 5 .... column 1) as after you delete the column, the number of column will change. You can do the same for delete row by replacing EntireColumn with EntireRow.
For i = numCols to 1 step -1 if InStr(objWriteSheet.Cells(1,i).value, "test4") Or InStr(objWriteSheet.Cells(1,i).value, "test5") Then objWriteSheet.Cells(1,i).EntireColumn.Delete End If Next
To assign value to a cell
You need to get a worksheet object and just enter the correct row and column index for the correct cell.
objWriteSheet3.Cells(1,14).value = "test"
To use Excel formula
The loop below loop through all the rows (starting from row 2)in the worksheet. By creating an object for the range (objRange), you can use this objRange as an argument for this statement - objExcel.WorksheetFunction.Sum(objRange). You can use other formula such as Average...
For i = 2 to numRows Set objRange = objWriteSheet3.Range(objWriteSheet3.Cells(i,2), objWriteSheet3.Cells(i,13)) objWriteSheet3.Cells(i,14).value = objExcel.WorksheetFunction.Sum(objRange) Set objRange = Nothing Next
Copy 2 different columns and paste it to another worksheet
The lines below define 2 range object for different column (column with index 14 and 28) and use the union to combine them so that the copy can be done for 2 different ranges.
' Copy the result to the new worksheet
Set objRangeD = objWriteSheet3.Range(objWriteSheet3.Cells(1,14), objWriteSheet3.Cells(numRows,14))
Set objRangeE = objWriteSheet3.Range(objWriteSheet3.Cells(1,28), objWriteSheet3.Cells(numRows,28))
objExcel.Union(objRangeD,objRangeE).copy
objWriteSheet2.Range("D1").PasteSpecial
Create chart
First line create a chart object with the following arguments (left, top, width, height).
Second line define this as line chart.
Third line set the data source where you need to define range for it.
The most critical thing here is vbs actually don't understand xlLine. So to make this works, you have to define a constant for this Const xlLine = 4. This link let you have the number for all the different constant use in Excel application.
Set objMychart = objWriteSheet2.ChartObjects.Add(400, 20, 375, 200).Chart objMychart.ChartType = xlLine objMychart.SetSourceData objWriteSheet2.Range(objWriteSheet2.Cells(1,1), objWriteSheet2.Cells(numRows,numCols))
Add chart title and axis title
Again, you need to define your own constant for xlCategory and xlValue. Of course, you can also put number directly.
objMyChart.hasTitle = True objMychart.ChartTitle.Text = "Sales" objMychart.Axes(xlCategory).hasTitle = True objMychart.Axes(xlCategory).AxisTitle.Text = "Month" objMychart.Axes(xlValue).hasTitle = True objMychart.Axes(xlValue).AxisTitle.Text = "$"
Set the maximum scale for y axis
objMychart.Axes(xlValue).MaximumScale = 100
Set the line colour
These line set the first line in the chart, you can change to indicate which line you want to change.
objMychart.SeriesCollection(1).Format.Line.Visible = True objMychart.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0,32,96) objMychart.SeriesCollection(1).Format.Line.Transparency = 0
Save your Excel document
Very important step!
objWorkbook.SaveAs (D:\result.xls), -4143 objExcel.Quit set objExcel = Nothing
