Why the first free row is not displaying correct?

I want to copy the records of more than one excel a to a main excel i have to paste the record to the main excel to the first free row but the value of first free row is not showing correct. I have 4 record in 1st excel, 4 record in 2nd excel and 2 record in 3rd excel. Still it count wrong value if i deleted all records.

here is the my code

set excelfile to “E:\Nutan\Product Declaration\MainFile\ExcelOperation.xlsx”

set getfiles to ‘E:\Nutan\Product Declaration\’+Year+’\’+Month+’\’+Day

Folder.GetFiles Folder:getfiles FileFilter:’*’ IncludeSubfolders:False FailOnAccessDenied:True SortBy1:Folder.SortBy.NoSort SortDescending1:False SortBy2:Folder.SortBy.NoSort SortDescending2:False SortBy3:Folder.SortBy.NoSort SortDescending3:False Files=> Files

loop foreach item in Files
Excel.LaunchAndOpen Path:excelfile Visible:True Password:’’ ReadOnly:False LoadAddInsAndMacros:False Instance=> ExcelMain
Excel.GetFirstFreeColumnRow Instance:ExcelMain FirstFreeRow=> FreeRow
console.Write Message: FreeRow
Excel.LaunchAndOpen Path:item Visible:True Password:’’ ReadOnly:False LoadAddInsAndMacros:False Instance=> Excelread
Excel.GetFirstFreeColumnRow Instance:Excelread FirstFreeRow=> Row
Excel.Advanced.CopyCells Instance:Excelread StartColumn:‘A’ StartRow:2 EndColumn:‘G’ EndRow:Row-1
Excel.Advanced.PasteAt Instance:ExcelMain Column:‘A’ Row:FreeRow
Excel.Close Instance: Excelread
Excel.CloseAndSave Instance: ExcelMain
end

This is the Output
Checking script…
Loading robot…
Running script…
150
162
174
Execution completed successfully.

1 Like

Hi nutan,
you are right - the function “Excel.GetFirstFreeColumnRow” does not work properly. It returns a 2 on the first run of a newly created Excel file.
And when you enter and save some values - delete them afterwards - it still has the old last free row somewhere stored :face_with_raised_eyebrow:

But luckily there is another function available that does its job as expected: Excel.Advanced.GetFirstFreeRowOnColumn

Here is my code that works:
set excelfile to "C:\Robin\Tests\Mainfile\ExcelOperation.xlsx"

DateTime.Current DateTimeFormat:DateTime.DateTimeFormat.DateAndTime Offset:'0' CurrentDateTime=> CurrentDateTime
set Year to CurrentDateTime.Year
set Month to CurrentDateTime.Month
set Day to CurrentDateTime.Day

set getfiles to "C:\Robin\Tests\\" + Year + "\\" + Month + "\\" + Day

Console.Write Message: getfiles

Folder.GetFiles Folder:getfiles FileFilter:"*" IncludeSubfolders:False FailOnAccessDenied:True \
            SortBy1:Folder.SortBy.NoSort SortDescending1:False SortBy2:Folder.SortBy.NoSort \
            SortDescending2:False SortBy3:Folder.SortBy.NoSort SortDescending3:False Files=> Files

Excel.LaunchAndOpen Path:excelfile Visible:True Password:"" ReadOnly:False LoadAddInsAndMacros:False Instance=> ExcelMain

loop foreach item in Files
Console.Write Message: item
Excel.Advanced.ActivateCell Instance:ExcelMain  Column:"A"  Row:"1" 
#    Excel.GetFirstFreeColumnRow Instance:ExcelMain FirstFreeRow=> FreeRow
Excel.Advanced.GetFirstFreeRowOnColumn Instance:ExcelMain  Column:"A"  FirstFreeRowOnColumn=> FreeRow
Console.Write Message: FreeRow
Excel.LaunchAndOpen Path:item Visible:False Password:"" ReadOnly:False LoadAddInsAndMacros:False Instance=> Excelread
#    Excel.GetFirstFreeColumnRow Instance:Excelread FirstFreeRow=> Row
Excel.Advanced.GetFirstFreeRowOnColumn Instance:Excelread  Column:"A"  FirstFreeRowOnColumn=> Row
Console.Write Message: Row

Excel.Advanced.CopyCells Instance:Excelread StartColumn:"A" StartRow:1 EndColumn:"G" EndRow:Row-1
Excel.Advanced.PasteAt Instance:ExcelMain Column:"A" Row:FreeRow
Excel.Close Instance: Excelread
end
Excel.CloseAndSave Instance: ExcelMain
2 Likes