troubleshooting Question

Update data daily with Excel VBA

Avatar of bellboy2k
bellboy2kFlag for United States of America asked on
Visual Basic ClassicMicrosoft Excel
6 Comments1 Solution457 ViewsLast Modified:
I have an Excel VBA Macro that goes and pulls data from a file and updates my SS automatically, but it has to have user input of the date and update pivots. I would like to have it run daily without user input, here is the macro.

Sub get_power_data()
    On Error GoTo wtfjh
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    thisbook = ActiveWorkbook.Name
   
    Range("i1").Value = ""
    'tdate = InputBox("Target date to retrieve? Format yyyy-mm-dd", "Import")
    UserForm1.Show
    If Range("i1") = "" Then GoTo finish
    tdate = Year(Range("i1").Value) & "-" & Format(Month(Range("i1").Value), "00") & "-" & Format(Day(Range("i1").Value), "00")
    xdate = DateValue(tdate)
   
    If xdate = Date Then
        MsgBox "Today isn't over yet.", vbOKOnly + vbInformation, "Too Soon"
        GoTo finish
    ElseIf xdate > Date Then
        MsgBox "Peek at future not available until next software release.", vbOKOnly + vbInformation, "Way Too Soon"
        GoTo finish
    ElseIf xdate <= Sheets("daily trends").Range("h1").Value Then
        MsgBox "You should already have this.", vbInformation + vbOKOnly, "Already Retrieved"
        GoTo finish
    End If
   
    Target = "FullDayOutput-" & tdate & ".csv"
    Workbooks.Open Filename:= _
        "http://nenxidea.ner.cingular.net/reports/sitesDownCommPwrWTimeWindow/" & tdate & "/" & Target

    'determine range of data and copy
    Range("A1").End(xlDown).Select
    temprow = ActiveCell.Row
    Range("a2", "ab" & temprow).Select
    Selection.Copy
    Windows(thisbook).Activate
    Sheets("data").Select
    Range("F1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    temprow = ActiveCell.Row
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    lastrow = ActiveCell.Row
    Windows(Target).Activate
    ActiveWindow.Close
    Windows(thisbook).Activate
   
    'copy formulas
    temprow = temprow - 1
    Range("a" & temprow, "e" & temprow).Select
    Selection.AutoFill Destination:=Range("a" & temprow, "e" & lastrow), Type:=xlFillDefault
    Range("ah" & temprow, "aj" & temprow).Select
    Selection.AutoFill Destination:=Range("ah" & temprow, "aj" & lastrow), Type:=xlFillDefault

finish:
    Sheets("daily trends").Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Exit Sub
   
wtfjh:
    If Err.Number = 13 Then
        MsgBox "Invalid date", vbCritical + vbOKOnly, "Error 13"
    Else
        MsgBox Err.Description, vbCritical + vbOKOnly, "Error" & Err.Number
    End If
   
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Columns("D:H").Select
    Selection.ColumnWidth = 14
    Range("H1").Select
   
    GoTo finish
   
'update pivot sheet
 
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros