matttclark
Board Regular
- Joined
- Jan 13, 2011
- Messages
- 100
Is there a way in VBA to find Date and Dollar values on imported data and set the appropriate formatting?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
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
<o></o>
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.
<o></o>
Is there a way to go through a workbook 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.
<o></o>
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.00)"
<o></o>
Here are samples of what I do now:
<o></o>
<o></o>
Dollars are always 13.5 and dates 10.5 users don’t like autofit as they want columns to look uniform
<o></o>
Thanks for suggestions!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>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
<o
></o>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.
<o
></o>Is there a way to go through a workbook 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.
<o
></o>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.00)"
<o
></o>Here are samples of what I do now:
<o
></o>
Rich (BB code):
Columns("E:AD").Select
Selection.ColumnWidth = 13.5<o:p></o:p>
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)
<o:p></o:p>
Columns("W:Z").Select<o:p></o:p>
Selection.NumberFormat = "mm/dd/yyyy;@"<o:p></o:p>
Selection.HorizontalAlignment = xlCenter
Selection.ColumnWidth = 10.5"Dollars are always 13.5 and dates 10.5 users don’t like autofit as they want columns to look uniform
<o
></o>Thanks for suggestions!
