excel-find and convert/format dates and dollar values
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.00)"
This file is unconverted data so there are no $$ yet.
Columns X, Y and Z are date columns and other columns except A-D are $ columns.
The date columns come in differently formatted, in this particular file Column X is "general" (and I also realized that this is a third format added to the 2 above. This is mm/yyyyy as text)
The columns i convert to 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)"
Just to be clear--the sample code in my original post was from the code for a different file, so the columns referenced are different that the ones in the sample file.
I need to create a macro for the sample file and others which got me to thinking if there was a way to search on the values, formats or ther criteria in the cell contents to determine how to format the cell or column, rather than manualy plug them in.
In thinking more, columns are alwasy the same, by that i mean if it has a date in a certain format in row '1' all values in that colum down to the end will have the same data type and require the same format.
Don't know if that helps, possibly since this will reuqire some sort of looping maybe you can test a cell in each column for a single row or maybe the last two-three rows (to account for blanks) and assign the whole column based on that??? Don't know if this would cut down on looping or make it easier?
That's beautiful, yet simple ( I guess they go hand in hand)! Accepting as the solution wish I could give more points.
If you have time to clarify one thing for me it would help me learn. I recieve an undefined variable error for 'prts' if the module is set to option explict. How should that be defined? I tried Integer, Long and Object based on what I think you are doing in the code, but then I receive an error 'Expected Array' at this line:
If UBound(prts) = 1 Then
Thanks if you have time to address, it's cool if you don't. Thanks again for the solution.
yeah..... Dim prts() As String worked: Dim prts As String as I originally typed didn't (doh!)......
thanks again Saqib, this will save me tons of time.
Follow up note to anyone else using this. I ran this on about 10 reports and worked like a charm except on 1 report. I didn't not realize previously that I could have blanks in my data (the sample report I gave to Saqib didn't). If this is the case for you, then the range selection used above may fail to cover all rows/columns if you have a blank in column A or row 2. In my case I replaced it with:
For Each cel In ActiveSheet.UsedRange ' UsedRange on new exports
Reading on VBA out there, UsedRange is fraught with perils, however since my reports are 'clean' exports, it should work for my case. If you use Saqib' s code, please be sure to research the different options to select a used range that would apply to your situation.
Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.