Remove empty columns

#1
Hi team,
i need to delete the empty column in order to "set column name from the first row".
I can't use some external code stage, eventually only OLEDB vbo but it doesn't have the DELETE function.
How can i manage?

Thanks in advance
 

Pete_L

Active Member
#2
When using the OLEDB VBO, you will be able to specify the columns you want to pull from the source (which I assusme is Excel).

If you want to actually delete the column from Excel instead, try the Delete action in the Excel VBO. I have never used that action, but check the VBO Help (the blue "?" icon) and see if it will work for a column.

You could also try to load the Excel worksheet into a collection, then use the Delete Column action of the Utility - Collection Manipulation VBO to remove the column in question.

I hope that one of these options will lead you to a solution.

Best Regards.
 
#3
If you want to remove all empty columns in an excel sheet follow these steps.
1- open EXCEL VBO
2- duplicate 'Remove Blank Rows' action, and rename the action into 'Remove Blank columns'.
3- open the code stage in the new action you created
4- Replace every word 'Row' with 'Column' and 'Rows' with 'Columns'.
the result code will be like this

Code:
Dim worksheet As Object
worksheet = GetWorkbook(handle,Nothing).ActiveSheet

Const xlCellTypeLastCell As Integer = 11
Dim FirstColumn As Integer = 1
Dim LastColumn As Integer = worksheet.Cells.SpecialCells(xlCellTypeLastCell).Column

For i As Integer = LastColumn To FirstColumn Step -1
    If worksheet.Application.CountA(worksheet.Columns(i)) = 0 Then
        worksheet.Columns(i).Delete
    End If
Next
 
Top