How to copy a single column values in a sheet from one excel to other excel without changing in their format

Hi
From a excel file I am taking the single column values and pasting in another excel but not able to retain the same format

please help me to copy the data without losing the format in excel

Thanks & Regards,
A Manohar

2 Likes

Hi @Manohar1,

Best way to do that is to use Invoke code activity and write a piece of code …

 Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim excelDiscount As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet

     wb = excel.Workbooks.Open(" File Path", [ReadOnly]:=False) 'Open the excel the file' 
    excel.Visible = True
    ws = CType(wb.Sheets(" Sheet Name"), Microsoft.Office.Interop.Excel.Worksheet) 'select a sheet and activiates'
    ws.Activate()

   ws.Range("A:A").Copy

  ws.Range("G:G").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues)

 wb.Save()
    wb.Close()
    excel.Quit()

Hi @Manohar1

I have created workflow based on your requirement.
Please open and run the workflow it will give you the solution for your query.

CopyData.zip (20.2 KB)

bro it is working or not @Manohar1

Hi @HareeshMR
Getting these Error continuously after tried with different excel files

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ----> System.NullReferenceException: Object reference not set to an instance of an object.
at UiPathCodeRunner_551868b7ca9649f0bc63a28e872c558d.Run()
— End of inner ExceptionDetail stack trace —
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
at UiPath.Core.Activities.Workflow.CompilerRunner.Run(Object args)
at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Please once review my code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim excelDiscount As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet

 wb = excel.Workbooks.Open("C:\Users\30003313\Documents\B & C\x.xlsx", [ReadOnly]:=False) 
excel.Visible = True
ws = CType(wb.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet) 
ws.Activate()

ws.Range(“A1:A6”).Copy

ws.Range(“B1:B6”).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues)

wb.Save()
wb.Close()
excel.Quit()

Attaching Excel sheet:

x.xlsx (8.3 KB)

@Mohansadaiyapillai

It is not working bro format is changing

Try giving the range as B1 only, i mean ws.Range(“B1”).

Can you share the workflow you have?

Hi @HareeshMR

Still getting same error

Main.xaml (11.6 KB)

Sorry @Manohar1, It was my mistake and i missed to create a new object there, that’s why it is erroring out.
Here is the modified code :

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
 wb = excel.Workbooks.Open("C:\Users\30003313\Documents\B & C\x.xlsx") 
excel.Visible = True
ws = CType(wb.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet) 
ws.Activate()
ws.Range("A1:A6").Copy
 ws.Range("B1").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValuesAndNumberFormats)

wb.Save()
wb.Close()
excel.Quit() 

Check this and let me know if it is working :slight_smile:

Hi @HareeshMR

Thanks a lot it is working
How can we paste this to another excel

Try the below Xaml file @Manohar1 Hope that will be working for you.
instead of giving decimal zeros try with decimal numbers.

CopyData.zip (19.6 KB)

Thanks
Mohanraj

@HareeshMR and @Mohansadaiyapillai

Thanks for your Help

Issue got solved
everything is working fine

2 Likes

Here is the code to paste in new excel…

  Dim excel As Microsoft.Office.Interop.Excel.Application
  Dim wb As Microsoft.Office.Interop.Excel.Workbook
  Dim ws As Microsoft.Office.Interop.Excel.Worksheet
  
  Dim excelDiscount As Microsoft.Office.Interop.Excel.Application
  Dim wbDiscount As Microsoft.Office.Interop.Excel.Workbook
  Dim wsDiscount As Microsoft.Office.Interop.Excel.Worksheet
  		
  excelDiscount = New Microsoft.Office.Interop.Excel.Application
  excel = New Microsoft.Office.Interop.Excel.Application
   wb = excel.Workbooks.Open("C:\Users\30003313\Documents\B & C\x.xlsx") 
  excel.Visible = True
  ws = CType(wb.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet) 
  ws.Activate()
  ws.Range("A1:A6").Copy
  
  wbDiscount = excel.Workbooks.Open("C:\Users\30003313\Documents\B & C\NewFile.xlsx") 
  wsDiscount = CType(wbDiscount.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet) 
   wsDiscount.Range("A1").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValuesAndNumberFormats)
  
   wsDiscount.save()
   wbDiscount.close()
   excelDiscount.Quit)()
  wb.Save()
  wb.Close()
  excel.Quit()
2 Likes

Thanks @HareeshMR

Main.xaml (11.9 KB)

Solved…
Thanks & Regards,
A Manohar

1 Like

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