TheOtherITGuy
Guest
|
 Posted: Mon Jan 21, 2008 1:29 am Post subject: Help Help$$ modifying the recorded (VBA) Macro with Excel ma |
 |
|
English is not my first language so the contents of the question might
be lengthy and confusing. But I assume the answer should not be a
difficult if you know VBA well.
In addition to below what I am saying, basically I would like to
coorect the format so that it will pull the right data from the right
column and follow the excel process as recorded in macro.
I am also wondering if Pivot table is my other alternative to get the
desired result. In case if you have a question e-mail me at (email)
nathan2100@gmail.com(/email). My skype id is <theotherITGuy>
DailyReport (an Excel Macro)
If I am creating the report of each day of the week, generally I run
the report the next day and do manual processes to get the final
data ; I am able to record and save the all the manual processes in an
VBA macro and run it for test. It gives me the correct data if I run
for the same day, but I tried to run 1 or 2 or 3 day back, it does not
give the right data.
Here is what I have done:I will go to TOOLS, Macros,POPULATE, RUN THE
REPORT for day of the week
Go to TotalHours Section of the Report (an Excel Sheet), E63 for
today (this value referes to the value from the states, ca, AL, FL and
so on , select only CA for this time
(VBA code recorded by excel macro looks like this, I have modified
some) so following is vba code generated by excel macro while I
performed manual processes using another module (vba macro used to
pull the data from SQL server)
Sub DailyReport()
Application.Run "'FiOS DP 01-17-State Report.xls'!populate"
Dim statesall As String
Sheets("CA").Select
Range("E63").Select
Selection.Copy
Sheets("States").Select
ActiveSheet.Paste
ElseIf Sheets("FL").Select And Range("E63") > 0 Then
Selection.Copy
Sheets("States").Select
ActiveSheet.Paste
Else: statesall = "call IT"
End If
Sheets(Array("CA", "CT", "DE", "FL", "IN", "MA", "MD", "NH", "NJ",
"NY", "OR", "PA", "RI", _
"TX", "VA", "WA")).Select
ActiveCell.FormulaR1C1 = "=(R[4]C/States!R[4]C)*States!RC"
Range("E63").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("States").Select
Range("F63").Select
Application.CutCopyMode = False
Selection.Copy
Range("E63").Select
ActiveSheet.Paste
Sheets("States").Select
Sheets("CA").Select
X = Range("E63").Select
Sheets("FL").Select
Y = Range("E63").Select
Sheets("States").Select
Z = Range("F63").Select
Dim check As String
If Z > X + Y Then
check = "pass"
Else: check = "problem"
End If
ActiveWorkbook.Save
End Sub
( however this automation doesn't work if I am working to create the 1
day or earlier's data report)
My module 1: (this works fine)this is the main/base module which runs
before I recorded the earlier macro
Dim reportDate As Integer
Public Sub populate()
Dim rptCol As String
rptCol = getCol()
populateTab "Non-Fios DP", "Non-Fios DP", rptCol
End Sub
Private Sub populateTab(rptTab As String, dbView As String, rptCol As
String)
On Error GoTo err
Dim DBConnection As ADODB.Connection
Dim RS As ADODB.Recordset
Dim strSQL As String
Dim state As String
Set DBConnection = New ADODB.Connection
Set RS = New ADODB.Recordset
DBConnection.CommandTimeout = 1200
DBConnection.Open "Provider=SQLOLEDB.7;Password=master;Persist
Security Info=True;User ID=master;Initial Catalog=FiOS;Data
Source=VPSSQL01;"
strSQL = "select * from [fn_get_TM_State_Report_NEW]('" & (Date -
reportDate) & "') where state is not null"
RS.Open strSQL, DBConnection
RS.MoveFirst
Do Until RS.EOF
state = RS![state]
Worksheets(state).Range(rptCol & "9").Cells.Value = RS![Row9]
Worksheets(state).Range(rptCol & "10").Cells.Value = RS![Row10]
Worksheets(state).Range(rptCol & "11").Cells.Value = RS![Row11]
Worksheets(state).Range(rptCol & "15").Cells.Value = RS![Row15]
Worksheets(state).Range(rptCol & "16").Cells.Value = RS![Row16]
Worksheets(state).Range(rptCol & "23").Cells.Value = RS![Row23]
Worksheets(state).Range(rptCol & "24").Cells.Value = RS![Row24]
Worksheets(state).Range(rptCol & "25").Cells.Value = RS![Row25]
Worksheets(state).Range(rptCol & "26").Cells.Value = RS![Row26]
Worksheets(state).Range(rptCol & "27").Cells.Value = RS![Row27]
Worksheets(state).Range(rptCol & "28").Cells.Value = RS![Row28]
Worksheets(state).Range(rptCol & "29").Cells.Value = RS![Row29]
Worksheets(state).Range(rptCol & "32").Cells.Value = RS![Row32]
Worksheets(state).Range(rptCol & "33").Cells.Value = RS![Row33]
Worksheets(state).Range(rptCol & "34").Cells.Value = RS![Row34]
Worksheets(state).Range(rptCol & "35").Cells.Value = RS![Row35]
Worksheets(state).Range(rptCol & "36").Cells.Value = RS![Row36]
Worksheets(state).Range(rptCol & "37").Cells.Value = RS![Row37]
Worksheets(state).Range(rptCol & "38").Cells.Value = RS![Row38]
Worksheets(state).Range(rptCol & "39").Cells.Value = RS![Row39]
Worksheets(state).Range(rptCol & "40").Cells.Value = RS![Row40]
Worksheets(state).Range(rptCol & "41").Cells.Value = RS![Row41]
Worksheets(state).Range(rptCol & "42").Cells.Value = RS![Row42]
Worksheets(state).Range(rptCol & "44").Cells.Value = RS![Row44]
'Worksheets(state).Range(rptCol & "32").Cells.Value = RS![Row32]
Worksheets(state).Range(rptCol & "45").Cells.Value = RS![Row45]
Worksheets(state).Range(rptCol & "47").Cells.Value = RS![Row47]
'Worksheets(state).Range(rptCol & 485").Cells.Value = RS![Row35]
Worksheets(state).Range(rptCol & "49").Cells.Value = RS![Row49]
Worksheets(state).Range(rptCol & "50").Cells.Value = RS![Row50]
Worksheets(state).Range(rptCol & "51").Cells.Value = RS![Row51]
Worksheets(state).Range(rptCol & "53").Cells.Value = RS![Row53]
Worksheets(state).Range(rptCol & "54").Cells.Value = RS![Row54]
Worksheets(state).Range(rptCol & "55").Cells.Value = RS![Row55]
Worksheets(state).Range(rptCol & "59").Cells.Value = RS![Row59]
Worksheets(state).Range(rptCol & "63").Cells.Value = RS![Row63]
Worksheets(state).Range(rptCol & "68").Cells.Value = RS![Row68]
'Worksheets(state).Range(rptCol & "45").Cells.Value = RS![Row45]
'Worksheets(state).Range(rptCol & "46").Cells.Value = RS![Row46]
Worksheets(state).Range(rptCol & "77").Cells.Value = RS![Row77]
Worksheets(state).Range(rptCol & "78").Cells.Value = RS![Row78]
Worksheets(state).Range(rptCol & "79").Cells.Value = RS![Row79]
Worksheets(state).Range(rptCol & "81").Cells.Value = RS![Row81]
Worksheets(state).Range(rptCol & "82").Cells.Value = RS![Row82]
Worksheets(state).Range(rptCol & "83").Cells.Value = RS![Row83]
Worksheets(state).Range(rptCol & "84").Cells.Value = RS![Row84]
Worksheets(state).Range(rptCol & "85").Cells.Value = RS![Row85]
Worksheets(state).Range(rptCol & "86").Cells.Value = RS![Row86]
Worksheets(state).Range(rptCol & "88").Cells.Value = RS![Row88]
Worksheets(state).Range(rptCol & "89").Cells.Value = RS![Row89]
Worksheets(state).Range(rptCol & "91").Cells.Value = RS![Row91]
Worksheets(state).Range(rptCol & "96").Cells.Value = RS![Row96]
Worksheets(state).Range(rptCol & "98").Cells.Value = RS![Row98]
Worksheets(state).Range(rptCol & "99").Cells.Value = RS![Row99]
Worksheets(state).Range(rptCol & "102").Cells.Value = RS![Row102]
Worksheets(state).Range(rptCol & "105").Cells.Value = RS![Row105]
Worksheets(state).Range(rptCol & "106").Cells.Value = RS![Row106]
Worksheets(state).Range(rptCol & "107").Cells.Value = RS![Row107]
Worksheets(state).Range(rptCol & "108").Cells.Value = RS![Row108]
Worksheets(state).Range(rptCol & "112").Cells.Value = RS![Row112]
Worksheets(state).Range(rptCol & "113").Cells.Value = RS![Row113]
Worksheets(state).Range(rptCol & "115").Cells.Value = RS![Row115]
Worksheets(state).Range(rptCol & "116").Cells.Value = RS![Row116]
Worksheets(state).Range(rptCol & "120").Cells.Value = RS![Row120]
Worksheets(state).Range(rptCol & "121").Cells.Value = RS![Row121]
Worksheets(state).Range(rptCol & "122").Cells.Value = RS![Row122]
Worksheets(state).Range(rptCol & "123").Cells.Value = RS![Row123]
Worksheets(state).Range(rptCol & "128").Cells.Value = RS![Row128]
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Exit Sub
err:
If err.Number = 3021 Then
Resume Next
Else
MsgBox err.Number & " " & err.Description
'MsgBox state
Resume Next
End If
End Sub
Private Function getDate() As Integer
reportDate = InputBox("How many days back?", "Set Report Date")
getDate = Weekday(Date - reportDate, vbMonday)
End Function
Private Function getCol() As String
Select Case getDate()
Case 1:
' newWeek
getCol = "B"
Case 2:
getCol = "C"
Case 3:
getCol = "D"
Case 4:
getCol = "E"
Case 5:
getCol = "F"
Case 6:
getCol = "G"
Case 7:
getCol = "H"
End Select
End Function
***********************************
Context:=20010
End Function
Function DayName(InputDate As Date)
'---------------------------------------------
'--- A Function That Gives the Name of the Day
'--- http://www.fontstuff.com/vba/vbatut01.htm
'---------------------------------------------
Dim DayNumber As Integer
DayNumber = Weekday(InputDate, vbSunday)
Select Case DayNumber
Case 1
DayName = "Sunday"
Case 2
DayName = "Monday"
Case 3
DayName = "Tuesday"
Case 4
DayName = "Wednesday"
Case 5
DayName = "Thursday"
Case 6
DayName = "Friday"
Case 7
DayName = "Saturday"
End Select
End Function
Note: Anybody could suggest me a Pivot table may a solution for this,
however I couldn't figure it out how to get those calculated data from
one cell which consists of almost 40 states of data. |
|
