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.