Prev: Question on the 'shell' command in VB6
Next: TableAdapter, BindingSource and DataSet - the first record.
From: TheOtherITGuy on 20 Jan 2008 20:30
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(a)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.
 | 
Pages: 1
Prev: Question on the 'shell' command in VB6
Next: TableAdapter, BindingSource and DataSet - the first record.