Instead of using
Why not eliminate the return of the CallStatus, RecvdDate and CallID and add in a COUNT(Exp1) and use the Group By clause on your HospID. If you take your sample .xls file and use the Data:Subtotals function on it as I suggested, you appear to get at least the raw data that you want. Then it may be easier for you to formulate getting the report you need.
SELECT CallLog.CallStatus, CallLog.RecvdDate, DATEDIFF(Day, CallLog.RecvdDate, GETDATE()) As Exp1, Subset.HospID, Subset.CallID" & Chr(13) & "" & Chr(10) & "FROM heatprod.dbo.CallLog CallLog, heatprod.dbo.Subset Subset" & Chr(13) & "" & Chr(10) & "WHERE CallLog.CallID = Subset.CallID And ((CallLog.CallStatus" _
, " Not Like 'closed%'))" & Chr(13) & "" & Chr(10) & "ORDER BY Subset.CallID
As an alternative, you may be able to take the raw data and use a pivot table (which I am not familiar with) to generate the proper totals and format for the report. If that's the method you want to try, I'd suggest posting that in the Excel VBA help section or one of the OZ guys could move it there if they feel it's more appropriate.