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

Manohar1 · May 16, 2019, 10:06am

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

HareeshMR · May 16, 2019, 12:16pm

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()

Mohansadaiyapillai · May 16, 2019, 12:35pm

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)

Mohansadaiyapillai · May 16, 2019, 2:00pm

bro it is working or not @Manohar1

Manohar1 · May 17, 2019, 4:58am

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)

Manohar1 · May 17, 2019, 5:00am

@Mohansadaiyapillai

It is not working bro format is changing

HareeshMR · May 17, 2019, 5:26am

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

Can you share the workflow you have?

Manohar1 · May 17, 2019, 5:32am

Hi @HareeshMR

Still getting same error

Main.xaml (11.6 KB)

HareeshMR · May 17, 2019, 5:47am

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

Manohar1 · May 17, 2019, 5:51am

Hi @HareeshMR

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

Mohansadaiyapillai · May 17, 2019, 5:57am

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

Manohar1 · May 17, 2019, 5:59am

@HareeshMR and @Mohansadaiyapillai

Thanks for your Help

Issue got solved
everything is working fine

HareeshMR · May 17, 2019, 6:02am

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()

Manohar1 · May 17, 2019, 6:06am

Thanks @HareeshMR

Main.xaml (11.9 KB)

Solved…
Thanks & Regards,
A Manohar

system · May 20, 2019, 6:06am

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

Topic		Replies	Views
Copy one excel value to another without changing the colors Activities excel	4	278	July 27, 2023
How do I copy a sheet in excel to another excel file with the format retained exactly from the source sheet to the destination sheet? Help	2	2520	March 1, 2019
Issue : Copy Paste Excel data from one worksheet to another in same file Using invoke code Help excel , activities	2	3965	February 14, 2018
Copy Paste from one excel workbook to another with format Studio excel , studio , question , tools	8	1495	September 9, 2022
Append columns from one sheet to other sheet at different column Help excel , activities , question	5	904	November 20, 2019

Most Active Users - Yesterday
ashokkarale
Yoichi
lrtetala
naveen.s
Anil_G
PeCour
Ahmad_Rais
AJ_Ask
Soudios
chunkin.zhang
More details...

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

Related Topics