how to save cells formating in excel?

[WebStar]

Hi,
I have an excel files with many macros behind. The file is 3MB big after I cleared every sheet. The problem is that all these macros are only working if all cells are formated as text. My file is a template and when the user starts it he will be required to enter some datas and a new file is saved with his initializations. This save is made by this:

Application.ActiveWorkbook.SaveAs txtPath & XLName

The path and name are introduced by the user.
The problem is that when the file is saved the cells formatings are lost and not all cells are set as text cells. What can I do to save these formatings as well?
Any help would be apreciated. Thank you!

 

[Nelviticus]

You can explicitly set the format of a single cell to 'text' with:

mySheet.Cells(lngRow, lngColumn).NumberFormat = "@"

or set it for all the cells on the sheet with:

mySheet.Cells.NumberFormat = "@"

When you save the workbook it will save the cell formatting too.

You might be able to reduce the size of your base workbook by doing this:
- export all your modules & forms
- removing them all
- save & close the file
- open it again
- import all the modules & forms
- save & close it again.

This can sometimes reduce file size enormously, because Excel doesn't tidy up temporary data very well.

N.