DPC Spreadsheet (MVCA)

[mjones]

Good Afternoon,

Has anyone encountered a problem with getting the TimeSeriesID(5) = 298095 'Current Day Precipitation to 0600 to load in the Raw 1 portion of the MVCA DPC spreadsheet? When looking into the problem, the code one getting stuck on one of my stations (South Castor at Kenmore) because that station does not have a precipitation gauge.  Does anyone have any suggestions for how to fix this?

I mainly have an issue with these 2 codes:

1. 'This If statement determines if the precipitation gauge has output a complete dataset between 00-06:00 am.
        If (.Sheets("Raw1").Range("Q" & (Application.WorksheetFunction.Match(GaugeName(i), .Sheets("Raw1").Range("Q1:Q500"), 0) + 2))) = 7 Then

2.         .SaveData = True
        End With
    ElseIf i = 5 Then
        'Loads Previous 6 hours of Precipitation
        KiWISDate = Left(KiWISDate, 16)
        KiWISDate = KiWISDate & "T" & Hour(InputDate) - 6 & ":00:00.000-05:00&to=" & Right(KiWISDate, 10) & "T" & Hour(InputDate) & ":00:00.000-05:00"
        URL2 = URL1 & TimeSeriesID(i) & KiWISDate
        With ThisWorkbook.Sheets("Raw1").QueryTables.Add(Connection:=URL2, Destination:=ThisWorkbook.Sheets("Raw1").Range("P2"))
            .BackgroundQuery = True
            .TablesOnlyFromHTML = True
            .Refresh BackgroundQuery:=False
            .SaveData = True
       

[cmcguire]

Hey Michael,

The "TimeSeriesID(5) = 298095 'Current Day Precipitation to 0600" is in the module KiWISLoader.KiWIS_Import.  These time series IDs are based on WISKI Timeseries groups.  If this station does not have a precipitation gauge, it should be left out of the  two precipitation based Timeseries groups.

1. 'This If statement determines if the precipitation gauge has output a complete dataset between 00-06:00 am.
        If (.Sheets("Raw1").Range("Q" & (Application.WorksheetFunction.Match(GaugeName(i), .Sheets("Raw1").Range("Q1:Q500"), 0) + 2))) = 7 Then

This is from the module KiWIS2Excel.Raw1Import.  The first statement is looking for a specific text string based on the GaugeName(i) array.  Since this site/row does not have precipitation data, the GaugeName(i) text string is not found in column Q of Raw1 and the statement will crash.  Based on the next quote, it looks like this line is looking for the data in the Q column but the KiWISLoader module is putting the data into column P which would also cause it to crash.

2.         .SaveData = True
        End With
    ElseIf i = 5 Then
        'Loads Previous 6 hours of Precipitation
        KiWISDate = Left(KiWISDate, 16)
        KiWISDate = KiWISDate & "T" & Hour(InputDate) - 6 & ":00:00.000-05:00&to=" & Right(KiWISDate, 10) & "T" & Hour(InputDate) & ":00:00.000-05:00"
        URL2 = URL1 & TimeSeriesID(i) & KiWISDate
        With ThisWorkbook.Sheets("Raw1").QueryTables.Add(Connection:=URL2, Destination:=ThisWorkbook.Sheets("Raw1").Range("P2"))
            .BackgroundQuery = True
            .TablesOnlyFromHTML = True
            .Refresh BackgroundQuery:=False
            .SaveData = True

This is from the module KiWISLoader.KiWIS_Import.  It's hard to say what is crashing it without knowing the line that is bugging out.  If the data is loading into Raw1 ok, your issue is probably with KiWIS2Excel.  You could try changing the range to Q to match the first quote (since you only have 5 different types of data to load and probably aren't using the Q column). 

The second thing you can try is the Textbox Debugger code at the top of the module.  Insert the Textbox Debugger code after "URL2 = URL1 & TimeSeriesID(i) & KiWISDate" to see the link that is being queried.  Copy and paste this link into your web browser to determine if it was assembled properly.

[mjones]

I have the new DPC sheet working for our sites except the hour update button on raw 1 is not working, but the daily update button is.  When the hour update button is selected I get an errors that says, "Compile Error: Method or data member not found. And it highlights this section of code:

Private Sub UserForm_Initialize()
HourPicker.DTPicker5.Value = Date + TimeSerial(Hour(Now), 0, 0)
End Sub

Do you know what may be causing this issue @Christopher McGuire?

[cmcguire]

Check the HourPicker form to see if there is a date picker.  Sometimes it needs to be redrawn after the MSCOMCT2.OCX date picker widget is installed.

[mjones]

That does not seem to be the problem.  Do you have any other suggestions?

[cmcguire]

Check to make sure if your date picker name matches the one in the code.  In the VBA editor window click on View->Properties.  Then double click on the HourPicker form and right click on the datepicker.  If the date picker's name is not DTPicker5 it might be creating this error.  Also check that the Format is set to 2 - dtpTime.

[mjones]

Perfect thanks.  That fixed it. The issue was that it was actually named DTPicker1.