I'm using an Excel template to generate a report from Access using VBA. When the newly created file is opened a number of cell's formatting has been changed from "Number" to "Date".
A table in the template has cells formatted as "Number":
These cell do not have data pushed in to them from Access, they have formulas which reference another sheet in the workbook. After the export of data is complete I open the Excel file and see this:
This is the only column in this table which does this, but other table cells in the sheet are also changed to "Date" format when they only contain numbers.
I wrote the VBA code, so I know it's not the code changing the format and there are no macros defined in the template.
I have looked for answers but none of them seem to work or are not applicable to this problem. For example, all the following SU questions do not provide the answer for me:
I would like to find a solution that does not require changes to the global settings of Excel. The Access database will be sent to a client and I don't want to have them changing their settings just for this, they are also going to be using a different version of Excel (I have 2013)
I have tried formatting some cells using VBA while pushing data in to them, but the client would like to be able to edit the Excel template without having to change the VBA code in the database; hence the reason for pushing data in to a separate sheet which is then referenced by the table. If I hard code the formatting of certain cells they won't be able to edit it.
How do I stop Excel changing the formatting?
I'm starting to think this is caused by a bug in Excel 2013. Unfortunately I don't have another version available to me to test this theory, but the change to the formatting only happens to the sheet currently visible when the template is saved.
I've added a "cover sheet" to the workbook containing information about the report. This sheet does not reference any of the data in other parts of the workbook. With this sheet selected I saved and closed the template. Reports produced using this latest template do not change the cell formatting when the report is generated and then opened.
I now also notice that other sheets, similar to the one having formatting issues, do not have cells randomly reformatted as they were not the 'active' sheet when the template was saved/closed.
This makes me think it's a fault with Excel and nothing to do with my code; and although it gets around the problem for now I'd like to find a solution, or at least, an explanation of this curious behaviour.