Copying sheets from one Excel to another

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 :slight_smile:

1 Like

Might be because of this?


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

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

1 Like

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

Could you please share your workflow? :slight_smile:

1 Like

prova2.xaml (140.2 KB)

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 :slight_smile:

1 Like

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


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

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.

1 Like

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.


1 Like

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

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’
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’
ws1=CType(wb.Sheets(sheetName),Microsoft.Office.Interop.Excel.Worksheet) ‘excel sheet name from where data to copy’

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

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

Catch es As Exception
End Try

1 Like

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

1 Like

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)

1 Like