For Each File error

[winnie_toh]

Dear all,

I encountered error when i try to read the file in the folder (excel file), it seems like it creates a ~ at the front of the file and does not allow to read it.
do you know why? it says either it is corrupted or i do not have permission to use the file. But i am able to open the file from folder. Any help is great appreciated!

[Shiva_Nikhil]

@winnie_toh

first copy the file path which is in currentfile.tostring
and check it manually whether the file getting opened or having any password so that you will get an idea

use run command or press windows+r

or
check the value of the cuurentfile.tostring by using logmessage above excel process scope
how the value you are getting

cheers

[Yoichi]

Hi

It’s probably temporary file of excel. If you cannot remove the file in advance, how about the following as workaround?

not CurrentFile.Name.StartsWith("~")

Regards,

[mkankatala (Mahesh Kankatala)]

Hi @winnie_toh

Could you confirm with Excel application is installed in your machine. If not the Excel process scope will not works.

Hope it helps!!

[Eric_Wong_Test (Eric Wong)]

It would help if you used Kill Process to kill Excel first, then read Excel

[winnie_toh]

Thank you @Yoichi san! it works well! I have another question. If i wish to read excel into a data table to put into 5th columns onwards, what is the best solution? I tried adding columns before read, but encountered below which i believe it was becoz i added those columns:

if i add the columns after reading, then it will appear on the right side of the data table. I need the first 4 columns to be on the left side.

[Yoichi]

Hi,

Can you try to turn off AddHeader of ReadRange?

Regards,

[winnie_toh]

Dear @Yoichi san,
Thank you for always supporting!! Now it manages to read range, however, when i try to update 1 of the cell, error encounered “No row at position 1”.
And when i open the datatable, i also noticed the columns seems not correct as below.

[Yoichi]

HI,

Can you share your input and expected output?

Regards,

[winnie_toh]

Hi,
Before reading range, i added 3 columns to the data table.

Then after read range, i expect the result to be like:
[Category][Accountee][Type][Following by the read range data table columns]

Then i will update the cells by column name.

[Yoichi]

Hi,

ReadRange activity overwrites the datatable. So, there is no column which you added in advance in the datatable after using ReadRange.

Regards,

[winnie_toh]

Dear @Yoichi san, in this case, i can only add column after that. is there a way to add it on the left side? :')

[Yoichi]

Hi,

If you want to add column and move it to the first position, the following will work.
First, add DataColumn using AddDataColumn activity
Next, call dt.Columns(“columnname”).SetOrdinal(0) using InvokeMethod activity as the following image.

Regards,

[winnie_toh]

@Yoichi san, thank you for the advise!

i encounter error when trying to do that. Am i doing it right? Kindly advise, as follows.
My Target object: GSalesDT.Columns(“Type”).SetOrdinal(0)

[Yoichi]

HI,

Probably TargetObject property should be as the following. Can you check it?

GSalesDT.Columns("Type")

Regards,

[winnie_toh]

yes no more error! However, do you know what below means?
It stated i need to compile the invoke method before use.

[Yoichi]

HI,

Usually this is caused by double quote except 0x22 ("). For now, can you try re-type your double quote or copy and paste this characte "

Regards,

[winnie_toh]

@Yoichi

"

WOW, it works!! Yoichi san, thank you so much for you guidance!! You are live saver!!! <3