If your data do not occupy all the rows, then make it more explicit so that it scan only rows with data. If doing this is troublesome, you can just scope it such as it is big enough (e.g.A1:A800) to cover your case. Once this is done, you should not experience slowness as the scanning can be done faster.
Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts
Thursday, May 5, 2022
Excel Slow Performance - Calculating (8 Threads)
If you experience slowness while working on your Excel workbook and you can see the calculating message appear at the bottom right of your Excel workbook. It could be due to you are using formula such as COUNTIF and your argument is to scan all the rows (e.g. A:A).
If your data do not occupy all the rows, then make it more explicit so that it scan only rows with data. If doing this is troublesome, you can just scope it such as it is big enough (e.g.A1:A800) to cover your case. Once this is done, you should not experience slowness as the scanning can be done faster.
If your data do not occupy all the rows, then make it more explicit so that it scan only rows with data. If doing this is troublesome, you can just scope it such as it is big enough (e.g.A1:A800) to cover your case. Once this is done, you should not experience slowness as the scanning can be done faster.
Monday, July 29, 2013
Excel Automation Using vbs
This post share how to do Excel automation through the use of vbs. Please take note that this might not be the most optimum way to get things done and you are always welcome to suggest new way by comment on this post.
Initialized Excel application object
This are the few lines you must include in your script whenever you need to do Excel automation.
Open workbook to process
You can use this for csv file also
Create an object to represent your first worksheet
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.
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.
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.
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...
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.
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.
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.
Set the maximum scale for y axis
Set the line colour
These line set the first line in the chart, you can change to indicate which line you want to change.
Save your Excel document
Very important step!
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
Subscribe to:
Posts (Atom)