VB Script to Expand Column in a Excel File. Only Columns with Data

Help Studio
, , , , ,
1

I have tried the following code in VScode(Console). It work’s fine.

Dim excel As Microsoft.Office.Interop.Excel.Application
        Dim wb As Microsoft.Office.Interop.Excel.Workbook
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet
        excel = New Microsoft.Office.Interop.Excel.Application 'create the instance of excel work book'
        'Dim strFilePath = "C:\Users\Abhishek\Desktop\New Microsoft Office Excel Worksheet.xlsx"
        'Dim strSheet = "Sheet1"
        wb = excel.Workbooks.Open(strFilePath) 'Open the excel the file
        'excel.Visible = True
        ws = CType(wb.Sheets(strSheet), Microsoft.Office.Interop.Excel.Worksheet) 'select a sheet and activiates'
        For col = 1 To 19
            ws.Columns(col).AutoFit()
        Next

        wb.Save()
        wb.Close()

While using it in Invoke Code. I get the following issue.
image
As per search I have gone through. I can’t use ws object as it is late binding.
Correct me If I’m wrong and How to resolve it?

ws.Columns(col).AutoFit()
1 Like
2

@abhishek.gupta

You have an activity to do this.Did you happen to try it?

https://docs.uipath.com/activities/docs/auto-fit-x

Cheers

3

Hi @abhishek.gupta ,

Can you try this:

ws.Columns(col).EntireColumn.AutoFit

Regards,

4

I have seen this. I need to do this using VBscript only

5

Hello @abhishek.gupta
Refer to this, You may get some idea

6

Hi
It seems that you are trying to use early binding with the Excel object model in your code. However, when using the Invoke Code activity in UiPath, you need to use late binding.

To use late binding, you need to declare your Excel objects as Object instead of their specific types

Note that you will also need to add the Microsoft.Office.Interop.Excel namespace to your Imports statements in UiPath in order to use the Excel objects in your code.
@abhishek.gupta

7

Didn’t Work. Same issue

8

I have added Microsoft.Office.Interop.Excel to the import section. Still issue is not resolved.

9

I think it’s about the binding
Would recommend to check on that aspect

@abhishek.gupta

10

Can you share something? I’m new to VB script.

1 Like
12

I have Started From there

13

@abhishek.gupta

Please try this


Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open("C:\Users\Abhishek\Desktop\New Microsoft Office Excel Worksheet.xlsx")
Dim ws As Microsoft.Office.Interop.Excel.Worksheet = CType(wb.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
For col As Integer = 1 To 19
     ws.Range(ws.Cells(1, col), ws.Cells(ws.UsedRange.Rows.Count, col)).EntireColumn.AutoFit()
Next
wb.Save()
wb.Close()
excel.Quit()

cheers

14

Sure
Here u go


Dim excel As Object
Dim wb As Object
Dim ws As Object

excel = CreateObject("Excel.Application")
wb = excel.Workbooks.Open(strFilePath)
ws = wb.Sheets(strSheet)

For col As Integer = 1 To 19
    ws.Columns(col).AutoFit()
Next

wb.Save()
wb.Close()

excel.Quit()

Cheers @abhishek.gupta

15

@Anil_G Palaniyappan
I have Tried your code. For some reason it does not work.
image
I’m not sure what is reason. Let me know if anyone knows. @Anil_G
Thank you guys for help me out

16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.