2

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

Excel cell format

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:

Format changed

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?

Update

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.

Improve this question
3
  • Is the data accurate at least? For example, 23/03/1900 = 80 in Excel. So, does the first cell under the 18-24 header have the right data in it? It sounds like there's something in either your formula or your VBA that's causing the cell type to change. Would need to see what those look like in order to better help you.
    – jrichall
    Jan 23, 2018 at 18:13
  • @jrichall - Yes, the cells contain the numbers I expect, the first cell being 80 as you said, and these numbers appear when I change the formatting back to "Number". The cells contain a COUNTIF function, referencing other fields (including the age range column header) in order to count rows of data exported from Access in to a different sheet in the workbook. The puzzling thing is that all the other cells next to this column contain the same formula except for referencing their header for the age range, but they are not changed to dates.
    – Tony
    Jan 23, 2018 at 22:16
  • I've added some more information after updating the template and adding a cover sheet. The problem appears to have gone away.
    – Tony
    Jan 23, 2018 at 22:49

3 Answers 3

Reset to default
0

I'm posting this as a work around, not an answer, in the hope it will be useful to someone else who has this problem.

If you see cell formatting changing, for no apparent reason, in files generated from a template it might be worth adding another sheet to the workbook template which does not contain any formulas or references. Make sure this sheet is selected when the template is saved/closed.

New files generated from the template should no longer have their formatting changed.

I have only tested this using Office 2013 (Access & Excel).

Improve this answer
0

I had this issue... what I did is reformatting the end result of the template, clearing all data and saving it as the new template. Problem fixed... go figure.

Improve this answer
1
0

I had a similar issue with Excel that it changes the format of cells from "accounting" to "user defined" _($* #,##0.00_);_($* (#,##0.00);_($* ""_""??_);_(@_) .

I could fix it by setting the Local parameter of the Workbook.SaveAs function to True.

Description of Local parameter (from MS VBA Docs):

True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .