Solved

Change refresh code via macro to VBA for multiple sheet updates

Posted on 2012-04-07
11
527 Views
Last Modified: 2012-06-27
I created this macro to refresh data from a Query.   It works great manually.
I need to convert it to be sheet specific and to run on time intervals.

Sub Refresh()
'
' Refresh Macro
' Macro recorded 1/22/2012 by Plant
'
' Keyboard Shortcut: Ctrl+r
'
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    
    Range("A21601:E21601").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    Range("E1").Select
End Sub

Open in new window


Automation code includes:
Sub Auto_Run()

Call Bun_Oven_Update_Report_Data
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Oven_Update_Report_Data"

Call Refresh
Worksheets("Bun Oven").Select
Application.OnTime Now + TimeValue("00:02:00"), "Refresh"

Call Bun_Proofer_Update_Report_Data
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Proofer_Update_Report_Data"

Call Refresh
Worksheets("Bun Proofer").Select
Application.OnTime Now + TimeValue("00:02:00"), "Refresh"

Call Bun_Mixers_Update_Data_Report
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Mixers_Update_Data_Report"

Call Refresh
Worksheets("Bun Mixers").Select
Application.OnTime Now + TimeValue("00:02:00"), "Refresh"

Open in new window


I am refreshing other data as well.  When I step through, the first time I step into refresh, I recieve a runtime error '1004',    It occurs when going into Sub Refresh(),  Range.("A2").Select

How do I specific which sheets to update at the given time?

Thanks in advance,
Angie
0
Comment
Question by:Angiehncock
  • 6
  • 3
11 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37819589
You need to specify the worksheet as part of the process.  Its somewhat hard to tell the sequence you actually want in your code, because you call the Refresh routine, then you activate a worksheet, then you run the timer.  

I can help, but there are some unknowns here you need to help resolve. I need to know what worksheets you're trying to affect with each of your subroutines - not just the refresh.  If you could repost the above code with those comments, it could help.

What would be best is if you could either provide the code for all your subroutines, or a sanitized workbook with the code which will be most efficient as it facilitates testing.

The way the code is currently written, one additional side affect is the fact that your code is selecting/activating sheets, so that should play messy if the machine is being used for other things, especially in Excel.  Without seeing how it all comes together, its hard to say but I think I can address this as well.

I await your response.

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37819637
Here's an attempt - again, the unknowns.  If you can post all your code or sample workbook, I can help greatly in cleaning this up.  Here goes:

First, we need to create a subroutine that will activate the sheet you want, then call the Refresh routine.  That routine could be rewritten to be more efficient as well, but I'm trying to make minimal changes as I can't see all your code nor test at this point.  I assume we'll leave the Refresh routine alone for now, as it appears you run it from ctrl-r, so we can't pass parameters directly to that routine as that would impact your ability to use the ctrl-r to run it "on demand".

Here's the revised code, and hopefully the documentation will assist:
Sub Refresh()
'
' Refresh Macro
' Macro recorded 1/22/2012 by Plant
'
' Keyboard Shortcut: Ctrl+r
'
    Range("A2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    
    Range("A21601:E21601").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    Range("E1").Select
End Sub
Sub RefreshShtActivate(wksName As String)
    ThisWorkbook.Worksheets(wksName).Activate
    Call Refresh
End Sub


Sub Auto_Run()
Dim wksName As String

Call Bun_Oven_Update_Report_Data
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Oven_Update_Report_Data"

Call Refresh
Worksheets("Bun Oven").Select

'I assume you want to call Refresh using "Bun Oven" as the worksheet parameter.  You can do it like this:
wksName = "Bun Oven"
Application.OnTime Now + TimeValue("00:02:00"), "'RefreshShtActivate" & Chr$(34) & wksName & Chr$(34) & "'"

Call Bun_Proofer_Update_Report_Data
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Proofer_Update_Report_Data"

Call Refresh
Worksheets("Bun Proofer").Select

'I assume you want to call Refresh using "Bun Proofer" as the worksheet parameter.  You can do it like this:
wksName = "Bun Proofer"
Application.OnTime Now + TimeValue("00:02:00"), "'RefreshShtActivate" & Chr$(34) & wksName & Chr$(34) & "'"

Call Bun_Mixers_Update_Data_Report
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Mixers_Update_Data_Report"

Call Refresh
Worksheets("Bun Mixers").Select

'I assume you want to call Refresh using "Bun Proofer" as the worksheet parameter.  You can do it like this:
wksName = "Bun  Mixers"
Application.OnTime Now + TimeValue("00:02:00"), "'RefreshShtActivate" & Chr$(34) & wksName & Chr$(34) & "'"

End Sub

Open in new window


Note, how the onTime works:

Application.OnTime EarliestTime:=(time to run it next),Procedure:="'ProcedureName" & Chr$(34) & stringParameter & Chr$(34) & "'"

Note, how the procedure and parameter are enclosed in quotations, and there are single quotations at the start, where the Chr$(34) is and at the end.

Hope this helps.  upload a sample and I can check it all out and help with some improvements if you like.

Cheers,

Dave
0
 

Author Comment

by:Angiehncock
ID: 37821129
This does work, but now I am getting  error codes in relationship to the main file of the excel program.    It reads"  The macro "c\DATALOGGING\DATALOGGING\SpringfieldDatalogging.xls'!' Auto_run can not be found.  It comes up with all of the seperate sub routines when they are called.  
I tried to add a line of code to add the Application.Run "'C:\DATALOGGING\DATALOGGING\SpringfieldDataLogging.xls'!'Auto_Run'"
but still get errors "method run or object failed"   .
Please advise,

thanks,
angie
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37821320
Please upload a sample at least with all your code
0
 

Author Comment

by:Angiehncock
ID: 37822603
I have attached the code.   I am getting compiling errors at the application.OnTime line at the last chr(34).  

I have additional code that I have used, however it runs in auto until it looks for the file.   At this time I receive "the macro "c:\ blah blah " can not be found, error.   I have compiled without error prior to the new code entry.  I recieved the same macro error.

Thanks for your help

Angie

Sub Auto_Run()
Dim wksName As String

Call Bun_Oven_Update_Report_Data
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Oven_Update_Report_Data"

Call Refresh
Worksheets("Bun Oven").Select
wksName = "Bun Oven"
Application.OnTime Now + TimeValue("00:02:00"), "'RefreshShtActivate" & Chr$(34) & wksName & Chr&(34) & "'"



Call Bun_Proofer_Update_Report_Data
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Proofer_Update_Report_Data"

Call Bun_Proofer_Refresh
Application.OnTime Now + TimeValue("00:02:00"), "'Bun_Proofer_Refresh" & Chr$(34) & "'"

Call Bun_Mixers_Update_Data_Report
Application.OnTime Now + TimeValue("00:05:00"), "Bun_Mixers_Update_Data_Report"

Call Bun_Mixers_Refresh
Application.OnTime Now + TimeValue("00:02:00"), "'Bun_Mixers_Refresh" & Chr$(34) & "'"

Call Bun_Cooler_Refresh
Application.OnTime Now + TimeValue("00:02:00"), "'Bun_Cooler_Refresh" & Chr$(34) & "'"



End Sub
Sub RefreshShtActivate(wksName As String)

ThisWorkbook.Worksheets(wksName).Activate
Call Refresh

End Sub

Sub Bun_Oven_Update_Report_Data()

'updating Bun Oven Speed
If Worksheets("Bun Oven").Range("F3").Value <> vbNullString Or Worksheets("Bun Oven").Range("F4").Value <> vbNullString Then

    Worksheets("Bun Report").Range("A1:B5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Worksheets("Bun Oven").Range("E2:F2").Copy
    Worksheets("Bun Report").Range("A1").PasteSpecial xlPasteValues
    Worksheets("Bun Oven").Range("E3:F5").Copy
    Worksheets("Bun Report").Range("A2").PasteSpecial xlPasteValues
    Worksheets("Bun Report").Range("A6:B10").Copy
    Worksheets("Bun Report").Range("A1:B5").PasteSpecial xlPasteFormats

'Setting cell parameters
With Worksheets("Bun Report").Range("B1")
    .NumberFormat = "m/d/yyy h:mm"
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

With Worksheets("Bun Report").Range("A1")
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

    Worksheets("Bun Report").Range("B4").NumberFormat = "0.00"
    
End If
 
'updating Bun Oven Temperatures
If Worksheets("Bun Oven").Range("F6").Value <> vbNullString Or Worksheets("Bun Oven").Range("F7").Value <> vbNullString Then

Worksheets("Bun Report").Range("A1:B5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Worksheets("Bun Oven").Range("E2:F2").Copy
    Worksheets("Bun Report").Range("A1").PasteSpecial xlPasteValues
    Worksheets("Bun Oven").Range("E6:F8").Copy
    Worksheets("Bun Report").Range("A2").PasteSpecial xlPasteValues
    Worksheets("Bun Report").Range("A6:B10").Copy
    Worksheets("Bun Report").Range("A1:B5").PasteSpecial xlPasteFormats

'Setting cell parameters
With Worksheets("Bun Report").Range("B1")
    .NumberFormat = "m/d/yyy h:mm"
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

With Worksheets("Bun Report").Range("A1")
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

    Worksheets("Bun Report").Range("B4").NumberFormat = "0.00"
    
End If



End Sub

Sub Bun_Proofer_Update_Report_Data()
'updating Bun Proofer Prooftime Data
If Worksheets("Bun Proofer").Range("F3").Value <> vbNullString Or Worksheets("Bun Proofer").Range("F4").Value <> vbNullString Then

    Worksheets("Bun Report").Range("C1:D5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Worksheets("Bun Proofer").Range("E2:F2").Copy
    Worksheets("Bun Report").Range("C1").PasteSpecial xlPasteValues
    Worksheets("Bun Proofer").Range("E3:F5").Copy
    Worksheets("Bun Report").Range("C2").PasteSpecial xlPasteValues
    Worksheets("Bun Report").Range("C6:D10").Copy
    Worksheets("Bun Report").Range("C1:D5").PasteSpecial xlPasteFormats

'setting cell parameters
With Worksheets("Bun Report").Range("D1")
    .NumberFormat = "m/d/yyy h:mm"
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

With Worksheets("Bun Report").Range("C1")
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

Worksheets("Bun Report").Range("D4").NumberFormat = "0.00"

End If
 'updating Bun Proofer Temperature Data
 If Worksheets("Bun Proofer").Range("F6").Value <> vbNullString Or Worksheets("Bun Proofer").Range("F7").Value <> vbNullString Then

    Worksheets("Bun Report").Range("C1:D5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Worksheets("Bun Proofer").Range("E2:F2").Copy
    Worksheets("Bun Report").Range("C1").PasteSpecial xlPasteValues
    Worksheets("Bun Proofer").Range("E6:F8").Copy
    Worksheets("Bun Report").Range("C2").PasteSpecial xlPasteValues
    Worksheets("Bun Report").Range("C6:D10").Copy
    Worksheets("Bun Report").Range("C1:D5").PasteSpecial xlPasteFormats

'setting cell parameters
With Worksheets("Bun Report").Range("D1")
    .NumberFormat = "m/d/yyy h:mm"
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

With Worksheets("Bun Report").Range("C1")
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

Worksheets("Bun Report").Range("D4").NumberFormat = "0.00"
       
       
       
End If

'updating Bun Proofer Humidity Data
 If Worksheets("Bun Proofer").Range("F9").Value <> vbNullString Or Worksheets("Bun Proofer").Range("F10").Value <> vbNullString Then

    Worksheets("Bun Report").Range("C1:D5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Worksheets("Bun Proofer").Range("E2:F2").Copy
    Worksheets("Bun Report").Range("C1").PasteSpecial xlPasteValues
    Worksheets("Bun Proofer").Range("E9:F11").Copy
    Worksheets("Bun Report").Range("C2").PasteSpecial xlPasteValues
    Worksheets("Bun Report").Range("C6:D10").Copy
    Worksheets("Bun Report").Range("C1:D5").PasteSpecial xlPasteFormats
 
'setting cell parameters
With Worksheets("Bun Report").Range("D1")
    .NumberFormat = "m/d/yyy h:mm"
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

With Worksheets("Bun Report").Range("C1")
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

Worksheets("Bun Report").Range("D4").NumberFormat = "0.00"
      
       
       
End If



End Sub

Sub Bun_Mixers_Update_Data_Report()
'updating Bun Mixer Sponge Data
If Worksheets("Bun Mixers").Range("F5").Value <> vbNullString Or Worksheets("Bun Mixers").Range("F6").Value <> vbNullString Then

Worksheets("Bun Report").Range("E1:F9").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Worksheets("Bun Mixers").Range("E2:F10").Copy
    Worksheets("Bun Report").Range("E1").PasteSpecial xlPasteValues
    Worksheets("Bun Report").Range("E10:F19").Copy
    Worksheets("Bun Report").Range("E1:F9").PasteSpecial xlPasteFormats
    
With Worksheets("Bun Report").Range("F1")
    .NumberFormat = "m/d/yyy h:mm"
End With
 
With Worksheets("Bun Report").Range("F2")
    .NumberFormat = "General"
End With



Worksheets("Bun Report").Range("F9").HorizontalAlignment = xlCenterAcrossSelection
Worksheets("Bun Report").Range("F9").NumberFormat = "0.00"

End If
'updating Bun Mixer Dough Data
If Worksheets("Bun Mixers").Range("F16").Value <> vbNullString Or Worksheets("Bun Mixers").Range("F17").Value <> vbNullString Then

Worksheets("Bun Report").Range("E1:F9").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Worksheets("Bun Mixers").Range("E12:F20").Copy
    Worksheets("Bun Report").Range("E1").PasteSpecial xlPasteValues
    Worksheets("Bun Report").Range("E9:F17").Copy
    Worksheets("Bun Report").Range("E1:F9").PasteSpecial xlPasteFormats
    
With Worksheets("Bun Report").Range("F1")
    .NumberFormat = "m/d/yyy h:mm"
    
    With Worksheets("Bun Report").Range("F2")
    .NumberFormat = "General"
    End With
End With



Worksheets("Bun Report").Range("F9").HorizontalAlignment = xlCenterAcrossSelection
Worksheets("Bun Report").Range("F9").NumberFormat = "0.00"

End If

End Sub


Sub Bun_Oven_Refresh()

Worksheets("Bun Oven").Range("A2").QueryTable.Refresh BackgroundQuery:=False
Worksheets("Bun Oven").Range("A21602:D65536").Clear
Application.Goto Worksheets("Bun Oven").Range("A2")

End Sub

Sub Bun_Proofer_Refresh()

Worksheets("Bun Proofer").Range("A2").QueryTable.Refresh BackgroundQuery:=False
Worksheets("Bun Proofer").Range("A21602:D65536").Clear
Application.Goto Worksheets("Bun Proofer").Range("A2")


End Sub
Sub Bun_Mixers_Refresh()

Worksheets("Bun Mixers").Range("A2").QueryTable.Refresh BackgroundQuery:=False
Worksheets("Bun Mixers").Range("A21602:D65536").Clear
Application.Goto Worksheets("Bun Mixers").Range("A2")


End Sub
Sub Bun_Cooler_Refresh()

Worksheets("Bun Cooler").Range("A2").QueryTable.Refresh BackgroundQuery:=False
Worksheets("Bun Cooler").Range("A21602:D65536").Clear
Application.Goto Worksheets("Bun Cooler").Range("A2")


End Sub


Sub Refresh()

Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A21602:D65536").Clear
Application.Goto Range("A2")

End Sub

Open in new window

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 41

Expert Comment

by:dlmille
ID: 37822614
in your auto_run routine, you have a call to Refresh on line 7

What is the sheet name that you are refreshing?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37822654
I'm having a bit of a hard time.  I really want to help you with your Auto_Run macro, as it confuses me as to what you're trying to do.

It appears you are running subroutines, then scheduling those same subroutines to be run again at some point in the future.  Are you concerned that your routines might get run out of sequence?

Can you state in words what you're trying to do so I can help?  Otherwise, I can just keep correcting syntax and respond when you have problems, but I feel compelled to ask the steps you're trying to accomplish because looking at your Auto_Run routine it appears there is a bigger problem with all this scheduling of routines to be run.

Please advise.  I'm happy to help any way I can.

-----------------------------------------------------------------
Back to the problem at hand...

Here's a copy of your current routine and attached file that will advise you what is running when, etc., as a clean demonstration of how everything is working without actually doing anything but testing the "model":

It appears you're doing the same operation on a given sheet with each refresh, so we move that back to a common module.  Here's your code:

Option Explicit

Sub Auto_Run()
Dim wksName As String

    Call Bun_Oven_Update_Report_Data
    Application.OnTime Now + TimeValue("00:05:00"), "Bun_Oven_Update_Report_Data"

    wksName = "Bun Oven"
    Application.OnTime Now + TimeValue("00:02:00"), "'Bun_Refresh" & Chr$(34) & wksName & Chr$(34) & "'"

    Call Bun_Proofer_Update_Report_Data
    Application.OnTime Now + TimeValue("00:05:00"), "Bun_Proofer_Update_Report_Data"

    Call Bun_Refresh("Bun Proofer")
    wksName = "Bun Proofer"
    Application.OnTime Now + TimeValue("00:02:00"), "'Bun_Refresh" & Chr$(34) & wksName & Chr$(34) & "'"

    Call Bun_Mixers_Update_Data_Report
    Application.OnTime Now + TimeValue("00:05:00"), "Bun_Mixers_Update_Data_Report"

    Call Bun_Refresh("Bun Mixers")
    wksName = "Bun Mixers"
    Application.OnTime Now + TimeValue("00:02:00"), "'Bun_Refresh" & Chr$(34) & wksName & Chr$(34) & "'"

    Call Bun_Refresh("Bun Cooler")
    wksName = "Bun Cooler"
    Application.OnTime Now + TimeValue("00:02:00"), "'Bun_Refresh" & Chr$(34) & wksName & Chr$(34) & "'"

End Sub

Sub Bun_Refresh(wksName As String)

    ThisWorkbook.Worksheets(wksName).Activate
    MsgBox "Running Refresh on sheet: " & ActiveSheet.Name

End Sub

Sub Bun_Oven_Update_Report_Data()

    MsgBox "Running(Bun_Oven_Update_Report_Data)..."

End Sub

Sub Bun_Proofer_Update_Report_Data()

    MsgBox "Running (Bun_Proofer_Update_Report_Data)..."

End Sub

Sub Bun_Mixers_Update_Data_Report()

    MsgBox "Running (Bun_Proofer_Update_Report_Data)..."

End Sub

Open in new window


See attached and run it as a demonstration model.  Let's get the demonstration model working, then we can apply to the live model at the appropriate time for final testing.

PS - feel free to shorten the times to speed through the steps, if it helps ;)

Dave
testRuns-r1.xls
0
 

Author Comment

by:Angiehncock
ID: 37824020
Dave,

Sorry so confusing.  I am a beginner and trying to create the most efficient and easiest code possible.  Any advise is appreciated.  

To begin I am downloading data into a SQL database.  From here I have created queries into an excel spreadsheet.  The sheets have alarms set up based on the formulas, which are included in the worksheets.  From here , if the formulas are true, I want to take the true conditions from individual worksheets and place them onto a reporting sheet.  The reporting sheet must update every five minutes, if alarming conditions are present.   The data must refresh from the sql databases every two minutes.  

This  process will allows be refreshing and updating.   It is important to have a sequence of refresh and updating as it follows the process flow.  I have created a cleaner refresh process, that refreshes and clears the latter part of the sheet.   I need to do this because of memory.  The sheets get to large and the system slows down.    However, I had to create a sub routine for each sheet which assigned it to the sheet.   I would like to have a generic one that would cause all the sheets to update at the same time.  When I tried this, I received application errors.  

Again, thank you for efforts,   they are  greatly appreciated.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37824316
When you run the sample I posted, above, does it work properly - as-is?  I coded the Bun_Refresh as the generic sheet update.

Dave
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37936093
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now