Merge excel with formula

Hi,
I have to merge multiple excel file into one but the date are return in int and the formula transform it into dd/mm/yyyy but in the new excel file it doesn’t have this formula so my question is:

"Is it possible to merge or copy/paste value from one excel to another one with formula? "

with formula:
image

without formula
image

i already try this one : How to merge excel files into 1 excel file which excel has formulas

and like this too :
image

in my process i do the conversion but it take to much time:

give a try on using Excel Application Scope and the corresponding read range.

We encountered that in case of dates (also depending on others factors) the read range result can differ,(WorkBook, EAS), but EAS had more often usable read range results. Just give a try

Hi,

in the write range activity enable the preserve format and check if you get the values in same format from the input excel you are referring to. just thought. thanks.
and also one suggestion from my side we can use invoke code activity and use VB code copy and paste the range from one work book to another and also we can have option like we can copy and paste the data with formulas.

if you want i can share some VB code. thanks.

mergeExcel.xaml (16.9 KB)

i put in the 2 test

path = path for all excel file
pathFuse= path to the excel where i put all data in

preserve format work fine thx but date format is change to mm/dd/yy and not the dd/mm/yyyy

if you want to change the date format in the excel itself you can use invoke code activity and use simple vb code to format the entire columns with the date format you want.

can you give a example? i

Hi,

Please use the below VB code to format the date to custom format. Use invoke code activity and i have given sample column A change accordingly and try. thanks.

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb1 As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb1 = excel.Workbooks.Open(“Your work book path”)
ws1 = CType(wb1.Sheets(“Your work sheet name”), Microsoft.Office.Interop.Excel.Worksheet)
ws1.Range(“A1”).NumberFormat = “dd/mm/yyyy”
wb1.Save
wb1.Close
excel.Quit
ws1 = Nothing
wb1 = Nothing
excel = Nothing
GC.Collect

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