Is there a way in VBA to find Date and Dollar values on imported data and set the appropriate formatting?
Using Excel 2003 with data from Access using either 1) Query -Tools, Office Links, Analyze with Excel or 2) the DoCmd.OutputTo acQuery… MicrosoftExcelBiff8(*.xls)
from within Access
I am in the process of creating report specific macros’ for our teams users that do a lot of formatting on each query and turn it into a report, but am wondering if there is a better way. Now I create a macro to manually select and format the columns that have date values and the same for dollar values. I have to go through each report and code these columns in, and also go back and update with changes. Users can run these from an add-in any time they want against the imported data.
Is there a way to go through a workbook in code and find cells with dates and convert them if needed and set the date format? Most come up as dates but some as text. The two formats I start with are either d/mm/yy (as date or text) and dd-mmm-yy (as date or text). These I change to dd/mm/yyyy.
So far dollar values always seem to be as numbers in “#,##0.00;(#,##0.00)” format. and I just change the format of these to "$#,##0.00_);[Red]($#,##0.
Here are samples of what I do now:
Selection.ColumnWidth = 13.5
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)
Selection.NumberFormat = "mm/dd/yy;@"
Selection.HorizontalAlignment = xlCenter
Selection.ColumnWidth = 10.5"
Dollar columns are always 13.5 wide and dates 10.5 users don’t like autofit as they want columns to look uniform