How to remove all unwanted spaces from excel column

[Stalin_R]

Hi,

How to remove all unwanted spaces from excel column by using invoke code activity. I am using below code for pivot table and in ws worksheet , Perticlular or all columns i want to trim or remove spaces in all rows. How to update. Let me know if you have different approach

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim p_ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range ‘capturing the range of sheet1 ‘’’
Dim p_rng As Microsoft.Office.Interop.Excel.Range ‘capturing the range of pivottable shhet1’’
‘Dim e_rng As Microsoft.Office.Interop.Excel.Range
Dim x As Integer
Try
excel = New Microsoft.Office.Interop.Excel.ApplicationClass’create the instance of excel work book’
wb = excel.Workbooks.Open(sPath)‘Open the excel the file’
excel.Visible=True
ws=CType(wb.Sheets(“DXC Technology”),Microsoft.Office.Interop.Excel.Worksheet)‘select a sheet and activiates’
ws.Activate
ws.Columns.AutoFit()
x=ws.UsedRange.Rows.Count
rng=ws.Range(“A1”,“G”+x.ToString)‘Selects the given range of sheet 1’
p_ws=CType(wb.Sheets.Add,Microsoft.Office.Interop.Excel.Worksheet)‘create a empty and and name the sheet as pivot table’
p_ws.Name=“Review Summary Report”
p_rng= CType(p_ws.Cells(8,1),Microsoft.Office.Interop.Excel.Range) ‘Specify the first cell for the pivot table’
'Creation pivot Cache and pivot table ’
Dim oPivotCache As Microsoft.Office.Interop.Excel.PivotCache=Nothing
Dim oPivotTables As Microsoft.Office.Interop.Excel.PivotTables=Nothing
Dim oPivotTable As Microsoft.Office.Interop.Excel.PivotTable=Nothing
oPivotCache=CType(wb.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase,rng),Microsoft.Office.Interop.Excel.PivotCache)
oPivotTables=DirectCast(p_ws.PivotTables(Type.Missing),Microsoft.Office.Interop.Excel.PivotTables)
oPivotTable=oPivotTables.Add(oPivotCache,p_rng,“Review_Summary_Report”,Type.Missing,Type.Missing)‘first paramenter is cache,range the table should start,table name’
oPivotTable.PivotCache.Refresh
oPivotTable.EnableFieldList=False
‘Creation of pivot Fields’
Dim oPivotField As Microsoft.Office.Interop.Excel.PivotField=Nothing
oPivotField=CType(oPivotTable.PivotFields(“ReviewRating”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the row name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
oPivotField=CType(oPivotTable.PivotFields(“ReviewedPortal”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField
oPivotField=CType(oPivotTable.PivotFields(“PostedDate”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField
oPivotField=CType(oPivotTable.PivotFields(“Client Comment”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField
oPivotField=CType(oPivotTable.PivotFields(“ReviewRating”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount
oPivotField.Name=“Review Rating Vs Review Portal”
p_ws.Cells(1, 3)= "'PROJECT NAME : " & UCase(strProjectName)
p_ws.Range(“C1:E1”).Merge
p_ws.Cells(2, 3)= “'REVIEW RATING Vs REVIEW PORTAL”
p_ws.Range(“C2:E2”).Merge
p_ws.Columns.AutoFit()
excel.DisplayAlerts = False
excel.ActiveWorkbook.Save
excel.ActiveWorkbook.Close
Catch es As Exception
System.Windows.MessageBox.Show(es.Message)
End Try

[Pablito (Pawel Wozniak)]

Hi @Stalin_R,
It’s a huge part of code I must admit. I think it’s hard to figure it out. Maybe people will help you if you will show screens with example of what you need to achieve.

[Stalin_R]

Hi Pablito,

I figured it out. I directly removed space from Datatable column like below(Screenshot 1). Eventhogh I want to know without converting excel to datatable , I want to remove all space on perticular column.

Below excel screen shot(Screenshot 2) just refer “Client comment” column first tow values has some spaces.

One more Question : (Refer Screenshot 3)
How to verify in the particular row I have some value in particular column .In the below Excel screenshot ,I have “ReviewedPortal” and “ReviewedCompany” columns. I want to know howmany rows we have the combinations like ReviewedPortal=“Glassdoor” and “DXC Technology”. For example, in the screenshot ,i have 9 rows are satishfying my logic, So it should return 9. How to write logic for this using assign activity.

Screenshot 1:

Screenshot 2:

Screenshot 3:

[Pablito (Pawel Wozniak)]

As you figured out, one of the solution is conversing file to datatable and then work on this - and honestly this is much better and easier solution where RPA have it’s strength side. Invoking code may be a solution but might be much more complicated. Why using Studio if you can do everything by a code? - Hopefully you know what I mean

[Stalin_R]

One more Question : (Refer Screenshot 1)
How to verify in the particular row, I have some value in particular column .In the below excel screenshot 1 ,I have “ReviewedPortal” and “ReviewedCompany” columns. I want to know how many rows we have the combinations like ReviewedPortal=“Glassdoor” and “DXC Technology”. For example, in the screenshot ,i have 9 rows are satisficing my logic, So it should return 9. How to write logic by using assign activity.

Screenshot 1:

[Pablito (Pawel Wozniak)]

The easiest way would be to get this datable using read range and keep it in data table. Then using for each row activity you need to go one by one row where you will have integer-type variable and if statement which say if column("ReviewedPortal").ToString = "Glassdoor" and column("ReviewedCompany").ToString = "DXC Technology" assign `Variable = Variable +1.