How to read big .xlsm excel file around 34,444 KB file

Help Studio
, ,
1

I tried invoke code and also I tried read range but it takes hours to read the file. Can someone suggest easiest and earliest way to read big data .xlsm file ?

Thanks

2

Hi @Pal_Patel

If you use either workbook activities or excel activities to read and convert large data file to datatable. It will take a huge time to read the data.

If you are trying to do data manipulations in the excel. You have two approaches by using macros or excel as database.

Select the approach which suites for you and go with it.

Hope it helps!!

3

I want to do if column A has negative value then convert to 0. So for that I am reading the file. Can someone help me to create macro for that and File name should be dynamic.

4

Hi @Pal_Patel

  • Workbook Activities: Generally faster and don’t require Excel to be installed.
  • Excel Application Scope with Specific Range: Optimize reading by using specific ranges and disabling macros.
  • Invoke VBA: Use VBA to quickly manipulate and export data to a CSV file, then read it with UiPath.
5

Thanks , I have this code for the macro but giving the error, where the sheet name is , it gives error there.

Sub ConvertNegativesToZero()
Dim ws As Worksheet
Dim col As Range
Dim cell As Range

**' Set the worksheet to the one containing your data**

** Set ws = ThisWorkbook.Sheets(“Cal Qty OH”)**

' Set the column to the one you want to modify
Set col = ws.Range("N2:N" & ws.Cells(ws.Rows.Count, "N").End(xlUp).Row)

For Each cell In col
    If cell.Value < 0 Then
        cell.Value = 0
    End If
Next cell

End Sub
image

6

Check if the issue is because issue might be with the quotation marks in the line where you set the worksheet. In VBA, you should use straight quotes (") instead of curly quotes (“ ”).

7

Hi, I checked, It is straight. It was working before and from past few days its failing.

8

Okay @Pal_Patel

Follow the below steps-
→ Use the Excel process scope activity inside of it insert the Use excel file activity and give the path of the excel file.
→ Store the Sheet name in a String variable called sheetName. Insert this assign activity inside Use excel file activity,

- Assign -> sheetName = "Sheet1"

→ After assign activity drag and drop the Invoke vba activity.

Store the below vb code in Text file,

Sub ReplaceNegativeValues(sheetName As String)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(sheetName)
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = 1 To lastRow ' Adjust if you have headers or specific row range
        If ws.Cells(i, 1).Value < 0 Then
            ws.Cells(i, 1).Value = 0
        End If
    Next i
End Sub

→ Save the text file which has macros in the Local folder and copy the file path then give the path in the code file path field.
→ In method name field give this with in double quotes as “ReplaceNegativeValues”.
→ In invoke vba activity click on add argument and pass the sheetName variable.

By following these steps the negative values in column A replaces with 0.

Hope it helps!!

9

It gives error of Invoke VBA: Parameter not optional. (Exception from HRESULT: 0x8002000F (DISP_E_PARAMNOTOPTIONAL))

I want to use it for column N and yes it has header. I changed A to N but Do I need to change any other thing ?

10

You might made a wrong in the code or flow… @Pal_Patel

I have made changes in the code as per your requirement, download the below text file and give the downloaded text file path to the Code file path field in Invoke vba activity,
ReplaceNegativeValues.txt (473 Bytes)

Check the below workflow for better understanding,
Sequence2.xaml (16.3 KB)

Sequence2
Sequence2804×1224 81.8 KB

Check the below output data,
Input -
image

Output -
image

Hope it helps!!

11

Hey, IT gives me error if I use your code or mine code .
image
It looks like file has something wrong.

12

If your excel file doesn’t have any confidential data share the file or else share the dummy data excel file… @Pal_Patel

Then I’ll look into it.

Hope you understand!!

1 Like
13

Test File.xlsx (9.2 MB)
Actual File format is .xlsm but I converted to .xlsx since this is not allowing to upload .xlsm. thanks for the help.

14

I have tried with the given Test File it is working fine… @Pal_Patel

If you are getting the errors again, try to debug the code and tell me at which activity it was getting error and share the screenshot of the output file.

15

I removed other sheets , because the file is too big and it is not allowing me to upload. I am getting highlight on the sheet when I do debug and it gives run time error ‘429’.

16

@Pal_Patel

Please use f8 or f12 to do step wise debug and check which line is faling depending on that we can go with further action needed

Cheers

17

Is there some settings that I need to update ?

18

@Pal_Patel

Copy the macro into excel developer tab visual basic

Then presss f8 or f12 to run the macro step by step

If in put arguments are need for macro then wirite one more test sub below and cell the macro

Eg:


Sub test()
ReplaceNegativeValues "Sheet1"
End sub

Now select test and then click f8/f12

Cheers

1 Like
19

Capture.PNG
Capture.PNG1330×206 68 KB

20

@Pal_Patel

As per error the sheetname procided might be wrong…can you check the exact sheetname…may be there are few spaces or anything in actual sheetname

Cheers