macro running to soon

[g48dd]

Excel 2003; I have two Macros in a SS that is full of data Web Query's that have to be refreshed once or twice daily. When the sheet updates it changes the column width and I have to reset column width for whole sheet so you can read it. I tried to just put the two together so that after the refresh data takes place then the sheet would just set its columns back to where they were. So I put call columnset into the Macro that handles the refresh. But what happens is the column set actually is running first even though it is at the end of the Refresh Macro. Which makes it useless. How do I change this?

Option Explicit
 
Sub ColumnSet()
'
' ColumnSet Macro
' Macro recorded 4/2/2011 '
'
    Range("A:A,B:B,C:C").Select
    Range("C1").Activate
    Selection.ColumnWidth = 24
    Range("D:D,E:E").Select
    Range("E1").Activate
    Selection.ColumnWidth = 6
    Range("F:F,G:G").Select
    Range("G1").Activate
    Selection.ColumnWidth = 12
    Range("H:H,I:I,J:J,K:K").Select
    Range("K1").Activate
    Selection.ColumnWidth = 6
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Range("L:L,M:M").Select
    Range("M1").Activate
    Selection.ColumnWidth = 9
 
End Sub
___________________________________________________________
 
Sub Refresh()
'
' Refresh Macro
' Macro recorded 4/2/2011'
'
'
    ActiveWorkbook.RefreshAll
    Call ColumnSet
End Sub

As you can see the Macro for setting the column width was actually created first, then the Macro for refresh. However the Macro Refresh includes the Macro columnset.

 

[Andrew Poulsom]

Have you set the Queries' Background property to False?

 

[mole999]

you could make it shorter and neater

Sub Refresh()

    ActiveWorkbook.RefreshAll
    Range("A:A,B:B,C:C").ColumnWidth = 24
    Range("D:D,E:E,H:H,I:I,J:J,K:K").ColumnWidth = 6
    Range("F:F,G:G").ColumnWidth = 12
    Range("L:L,M:M").ColumnWidth = 9

End Sub

 

[g48dd]

No, I am not sure where I would do that. This sheet is going to have almost 200 queries. I have 200 printers on the web, they each have a HTML sheet that has information that is useful every day, eg, level of black ink 10%. I am building the workbook to retrieve this data, I come in the morning and refresh the sheet it takes some time, for all information to be downloaded, there are now 50 queries on this one sheet and I am wondering if it is a good idea to put all 200 on same worksheet?

Ken

 

[Andrew Poulsom]

Right click inside your Query and choose Data Range Properties. Then uncheck 'Enable background refresh' and click OK.

 

[g48dd]

I just tried it, it still runs the reset column width first, its really not a big deal, I can run two macros one after the other, I was just trying to see if I could put them together..... I will keep reading see if I can find a reason why it would run the column reset first.... I have been thinking about putting a time delay on it, that might work.

Ken

 

[ndendrinos]

your idea might work ...see if 5 seconds are enough to refresh

Sub Refresh()
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeSerial(0, 0, 5)
    Range("A:A,B:B,C:C").ColumnWidth = 24
    Range("D:D,E:E,H:H,I:I,J:J,K:K").ColumnWidth = 6
    Range("F:F,G:G").ColumnWidth = 12
    Range("L:L,M:M").ColumnWidth = 9
End Sub

 

[g48dd]

It is not working, I tried the delay, the delay had no effect, it still ran the column reset before running the refresh. I have 20 printers on one sheet, that is 2 queries for each printer, it is too much for one page, so I started over and I am going to work with 10 printers on each tab. I am un-checking the options in Data Range Properties that says Adjust Column Width and the option that says Preserve Cell Formatting The only formatting I have in the cells is the width. None of this will probably help the macro problem but sometimes it is best to keep things simple and just run two macros. I do have another question about Web Queries but I am going to start new thread. It is a different kind of problem. I really appreciate the help and I will come back and up date this if I find away to make it work, since I have started over it is easy to set the Data Range Properties for each Web Query and that might solve the problem... when I said I had unchecked the Background Refresh I did but only for one query, I then realized I have to do it for all queries so I just started over.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you<o></o>
Ken<o></o>

 

[mole999]

Have you tried

Sub Refresh()
Event1
Event2
    Range("A:A,B:B,C:C").ColumnWidth = 24
    Range("D:D,E:E,H:H,I:I,J:J,K:K").ColumnWidth = 6
    Range("F:F,G:G").ColumnWidth = 12
    Range("L:L,M:M").ColumnWidth = 9
End Sub

Sub Event1
refresh some item
End Sub

Sub Event2
refresh some other item
End Sub

i.e stepping out from the main sub, so that each refresh is given time to do its own work, before control returns

 

[g48dd]

HI, I finshed resetting my first 20 printers and what was a big help, was that I didn't know about the Range Properties Box and how to get to it. The whole reason I was running two macros was because my SS columns were messed up after I ran the Refresh. If I go in to the Properties box and uncheck Adjust Column Width and I uncheck Enable Background Refresh and I CHECK Overwrite existing cells with new data, clear unused cells.... then my columns stay right where I put them and there is no reason to run a macro to reset them. It also seems to run much faster.

I do like the idea of refreshing each data download sepreately, I think that would help insure I get a good down load, I am not sure exactly how I set that up but I am looking at your code if I have any question I will post again,

Thanks Everyone for your help I consider this solved
Ken