Copying sheets from one Excel to another

[CamiCat]

Hello friends,
when I try to copy the “Sheet1” contained in 20180718.xlsx (17.4 KB) to the sheet “Foglio1” of another file Arricchimento_2018_07_18_162227.xlsx (14.0 KB)
the column “Numero Polizza” changes its format.
How is it possible to copy and save the file while mantaining the same format?
Thank you so much,
Camilla

[Niket_Ghai]

Might be because of this?

[CamiCat]

Yes I think It is due to this format.
Howis it possible to overcome the problem?
Thank you so much,
Camilla

[Niket_Ghai]

Try using this Ignore Error…it might work
I mean if its’s possible to make changes in the excel file

[CamiCat]

Unfortunately I should make this operations without opening the Excel file.
Do you know if it is possible?
Thank you,
Camilla

[Niket_Ghai]

Could you please share your workflow?

[CamiCat]

prova2.xaml (140.2 KB)

[Niket_Ghai]

I frankly don’t find any solution other than changing the excel somehow. Will get back to you if i get any progress on this
Regards
Niket

[loginerror (Maciej Kuźmicz)]

Hi @CamiCat

I was looking for the solution to change the numbers without opening the Excel, but couldn’t find any.

However, I do use this simple VBA code within Excel Scope activity to convert my values to actual numbers:

Sub ConvertTextToNumbers()
[A:A].Select
With Selection
.NumberFormat = “0.000”
.Value = .Value
End With
End Sub

You just have to replace the [A:A] to match your column, save it as .vbs file and execute it within Excel Scope activity by using the Invoke VBA activity.

I hope someone knows the way to save the numbers in your desired format without using the Excel Scope activity. Now that I think about it, maybe there is a community package with Excel activity that does that?

[CamiCat]

Thank you so much @loginerror.
Can you also please share me a VBA code that saves the excel file?
Thank you so much,
Camilla

[loginerror (Maciej Kuźmicz)]

You can add “Save Workbook” activity at the end of your Excel scope for that.

No need to use the VBA too excessively, and even this method is an annoying workaround to what we would like → to be able to save the numeric value by using write range without Excel scope.

[balupad14 (Balamurugan)]

Hi @CamiCat,

Sorry ya. I have seen this now. I have developed an activity to copy the sheet to inside work and another file. May be in future you can use this.

BalaReva.Excel.Activities

Hi Friends, I have included the below new custom components in the package. 1.Create Workbook.(Create a new worksheet) 2.Copy to File (Copy the worksheet to another excel file). 3.Copy to WorkBook (Copy the worksheet inside the workbook). 4.HideUnhide the worksheet 5.Protect Unprotect the worksheet [image] Create WorkBook Properties [image] Sample : CreateWorkbook.zip (18.5 KB) Copy To File This activity copies the work sheet to an another excel file. If the another file is not ex…

Regards
Balamurugan.S

[CamiCat]

Thank you so much @balupad14 for your precious help.
I’ll use it in the future.
Camilla

[Nero9 (Jacob)]

I have used invoke code activity to copy data from one excel to another excel with retaining excel data format

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim nb As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
Dim p_ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range ‘capturing the range of sheet’
Dim p_rng As Microsoft.Office.Interop.Excel.Range ‘capturing the range of SHeet’
Try
excel = New Microsoft.Office.Interop.Excel.ApplicationClass’create the instance of excel work book’
wb = excel.Workbooks.Open(filepath)‘Open the excel the file from where data to copy’
excel.Visible=True
ws1=CType(wb.Sheets(sheetName),Microsoft.Office.Interop.Excel.Worksheet) ‘excel sheet name from where data to copy’
ws1.cells.Copy()

nb = excel.Workbooks.Open(filepath1)‘Open the excel the file where data to be paste’
excel.Visible=True

p_ws=CType(nb.Sheets(“SheetName”),Microsoft.Office.Interop.Excel.Worksheet)‘excel sheet name where data to paste’
p_ws.cells.PasteSpecial(XlPasteType.xlPasteValues)

Catch es As Exception
System.Windows.MessageBox.Show(es.Message)
End Try

[CamiCat]

Thank you so much @Nero9.
I’ll try and let you know as soon as possible.
Cami

[Shiv_Achari948 (Shivprasad)]

Copy Sheet.xaml (6.8 KB) Example.xlsx (10.4 KB)

in the above code : Excel Scope will be your source excel file
use “copy sheet” activity within the excel application scope (source excel)

Now go to copy sheet property
destination : enter destination file path and sheet name
input : sheet name of source file(that is the excel scope sheet name)

Your format will be preserved in destination without any changes (same as source excel)