Excel Write Range not copying format

Help
, , ,
1

Hi everyone,

We’ve run into a bit of a situation using Write Range; it’s not copying the data format from the source. Here’s the scenario:

  • load a DataTable from an Access database with multiple columns
  • open an existing Excel file
  • clear a range of cells
  • paste the DataTable to the range just cleared

The problem is we sometimes have columns with data like “005” but when the data is pasted, the cells reformat to “5”. The column’s format in the Excel file is set to General. However, if we do it manually (select all data from Access, CTR-C, and paste in Excel) the proper format is retained. The default paste in Excel maintains the source cells’ format; there is another paste option that allows you to maintain the destination cells’ format

To make matters worse, the client needs the data to be in the “005” format, there are many Excel files with multiple tabs in each AND we don’t know which columns need to retain that format. We are automating their manual process.

I understand there are ways to force the format such as prepending the cells with an apostrophe or formatting the cells, but as I mentioned, we won’t know in advance which columns need to retain the format.

Any thoughts?

Thanks,

Carlo.

2

@carlor

After write range you can use an invoke code activity to set the format using vb.net how to set excel cell format in vb.net - Stack Overflow

1 Like
3
1 Like
4

The problem with that solution is having to know in advance which columns and which cells within those columns that I would have to set the format. Plus, that would change the layout of the original file because the original formatting is “General” and this would change it to “text”.

5

Again, I would have to know in advance which cells in which columns in which tabs in which excel files need to have the data formatted.

I was hoping there was a solution I missed.

Thanks,

Carlo.