Solved

Excel VBA Macro to prepare data for export

Posted on 2013-01-21
10
473 Views
Last Modified: 2013-01-28
I have only basic VBA knowledge and looking for help with VBA code to automate a process I do every week manually. The attached workbook is from Crystal Reports and has non-printing characters or leading/trailing spaces in the date column (Column A) which are not properly formatted as a short date. I currently use the Easy-XL add in to remove spaces before and after on all cells which solves that problem so the column can be properly formatted. However, I want this as part of the macro instead of having to do this step manually. The date from A2 must also be entered in the last row of Column A which would be A51 once the next step of inserting needed rows is accomplished. The report is always for one week and once processed will always have 51 rows of data (header+7 rows per day for 7 days+weekly total row=51)

In Column B, labeled "Acuity" there are missing rows. If no patients for that acuity came in Clarity does not put a zero in for null data. Hence the missing rows. However, where the data is exported to I must have a row matching each acuity for each date with zeroes for all null values or else I get errors. So in Column B I go down the column and see that for each date it goes 1,2, 3, 4, 5,6, and T and then on the very last row in this column is the weekly total row which comes in blank but must have a capital "W" inserted.

I have attached two versions of the file. The first one is how the data comes to me. The second one I processed how I would normally do. Any rows or data manually added are highlighted in yellow to make it easier for you to see what I am referring to. I do not normally highlight any cells.

I want the macro not only for my use but so I can pass it on to some other less Excel savvy users in my organization who want the same report but lack the knowledge to do the manual steps I perform. So a macro which I can assign a shortcut key will make it easier to pass on. My attempts at trying to write this macro on my own were far from successful.

Thank you for your assistance.
EDacuity.xls
EDacuity-final.xls
0
Comment
Question by:KendallRN
  • 5
  • 5
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Have you tried recording the steps you take to do it manually as a macro?

Can the report be modified to provide the data required?

mlmcc
0
 

Author Comment

by:KendallRN
Comment Utility
Since it isn't always the same rows needing  to be added recording a macro will not work. Some weeks I only need to add one row and other weeks I add 4 rows and they are not in the same place or for the same acuity. Sometimes it is missing an acuity 1 row for one date and then an acuity 5 row for another date. It is never missing an acuity "T" (Total) row for any date and there is always at least 3 rows of data for each date.

There needs to be certain amount of "logic" to check for what numbers are missing and add the appropriate rows. Also, recording a macro using another macro or add-in does not work as it does not capture those steps.

For cleaning up the extra spaces and non-printing characters this VBA code works:
Sub Clean_Trim()
     
    Dim CleanTrimRg As Range
    Dim oCell As Range
    Dim Func As WorksheetFunction
     
    Set Func = Application.WorksheetFunction
     
    On Error Resume Next
    Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
    If Err Then MsgBox "No data to clean and Trim!": Exit Sub
     
    For Each oCell In CleanTrimRg
        oCell = Func.Clean(Func.Trim(oCell))
    Next
     
End Sub

Open in new window

This is only part of the process. Finding where to insert rows and what date/acuity to assign automatically is a larger challenge programatically.

As for modifying the Crystal Report I have been told by our report writers it is not possible to have this done on the Crystal side. I had requested it when the report was built. I do not have access or knowledge to write the Crystal report directly.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What is the issue?

Is it missing dates?
There are ways to add them in the report.

mlmcc
0
 

Author Comment

by:KendallRN
Comment Utility
The issue is the missing rows of data. Yes, it needs to add the date, acuity, and a row of "0"s for the missing rows.

So the code would start by looking at B2 and check is the value 1? if Yes next cell is it's value 2 if yes then go to next cell and is it's value 3? If no then insert row and look one row down and to column A grab the date and put it in the inserted rows column A and in column B the value it was checking for that did not exist. Then for columns C through AA enter the value 0. Then continue with checking on the next row in column B to see if it's value is 4, then the next one 5, then the next one T (although the T row is never missing). Then repeat the process for a total of 7 cycles of this loop. Then after the 7th loop it would then grab the date from A2 and insert the date in A51 and put "W" in cell B51.

After that select cells A1:AA51 and run the Clean_Trim() code posted in my previous post. Set column A format to the short date format and then end the macro.

I can write the steps in English as above but not in VBA.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
It would be a lot easier and most likely more accurate to add the rows to the report.

mlmcc
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:KendallRN
Comment Utility
I would definitely agree with you. However, when the report was built the report writer told me it was not possible for Crystal to add data that did not exist (null). This is a count of patient arrivals based on the date/time they arrived. If no patients arrive of a certain acuity within that day then the row does not exist. An acuity 6 is not really a valid acuity but represents those with no acuity assigned. Crystal is already doing that. The T is a total of acuities 1-6 or could also be seen as all arrivals regardless of acuity.

Since I am not a Crystal Report writer I have to rely on his expertise. I see you have lots of points for Crystal Software and know by far more how it works. I don't know if you have expertise in writing Crystal Reports from EPIC data? If you do then you may know how it can be done and could advise me what to suggest to our report writers.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
It is possible.  I have done it with much more complex data thn that.  it takes some ingenuity and ability to think outside the box.

So you have data like
Date         PatientId      Acuity
3 Jan           101                 1
3 Jan            102                 2

mlmcc
0
 

Author Comment

by:KendallRN
Comment Utility
At the record level in EPIC the data would be:
Arrival Date/Time     PatientID   Acuity
1/13/2013 1000          34830      3
1/13/2013 0945           0483        4
1/13/2013  1005          08309      3
1/13/2013  1030          3587        2
1/13/2013   1045          9912       2


Currently the Crystal report would put that out as: (Count of patients for ea./hour/acuity)
Date       Acuity     09      10      11
1/13         2            0         2       0
1/13         3            0         2       0
1/13         4            1         0       0
1/13         T            1         4       0

What I am needing is this:
Date       Acuity     09      10      11
1/13          1            0         0       0
1/13          2            0         2       0
1/13          3            0         2       0
1/13          4            1         0       0
1/13          5            0         2       0
1/13          T            1         4       0
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
Something like this

mlmcc
Database10.mdb
Sample-report.rpt
0
 

Author Closing Comment

by:KendallRN
Comment Utility
I am going to close this question and work with trying to get the report coming out of Crystal to better suit the need and decrease the need for a complicated macro.  Thanks for your help and timely responses, mlmcc.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now