Prev: Why picture1.loadpicture(a): picture2.paintpicture picture1,0,0: picture3.paintpicture pucture2,0,0 'Invalid Picture?
Next: Wholesale to Home 9999
From: Jan Hyde (VB MVP) on 29 Jan 2008 06:23
TheOtherITGuy <Nathan2100(a)gmail.com>'s wild thoughts were
released on Sun, 20 Jan 2008 17:30:49 -0800 (PST) bearing
the following fruit:

>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.

There lies the first problem, this is a VB6 group. VBA is a
different animal and you should really be looking for an
Excel VBA group.

J

>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.

--
Jan Hyde

https://mvp.support.microsoft.com/profile/Jan.Hyde
 | 
Pages: 1
Prev: Why picture1.loadpicture(a): picture2.paintpicture picture1,0,0: picture3.paintpicture pucture2,0,0 'Invalid Picture?
Next: Wholesale to Home 9999