Free Microsoft Excel 2013 Quick Reference

Activeworkbook.Refresh All stopping code

Hi All,

I have a workbook that sucks data in from Access, I was using the following code 3 times to refresh the tables;

Code:
Worked fine, Jon von der Heyden suggested that I use;

Code:
In its stead, This seems to work much much quicker than my previos code but the rest of my code does not run after the refresh.

I know the obvious answer is to use the querytable.Refresh but can't understand why the Refreshall would block my code. I don't get any errors its simply that it seems to ignore my code.

Can anybody shed any light on this?

My full code is Code:
Sub Data_Integration()

Dim lRow As Long

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

ActiveWorkbook.RefreshAll

lRow = Sheets("OCC Data").Range("A" & Rows.count).End(xlUp).Row

With Sheets("OCC Data")    
    For i = 2 To lRow
        If .Cells(i, 32) = "00/00/0000" Then
            .Cells(i, 32).ClearContents
            .Cells(i, 33).ClearContents
        End If
    Next i
    
    .Columns("AF").TextToColumns Destination:=.Range("AF1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
    .Columns("AG").TextToColumns Destination:=.Range("AG1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
    
End With
With Sheets("Cost Data")
    .Columns("E").TextToColumns Destination:=.Range("E1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End With
With Sheets("Timesheet Data")
     .Columns("I").TextToColumns Destination:=.Range("I1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
    .Columns("J").TextToColumns Destination:=.Range("J1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
    .Columns("K").TextToColumns Destination:=.Range("K1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End With


Post your answer or comment

comments powered by Disqus
Hi!

Have just used the "Refresh AllWorkBookPivots" code that I got on the new "Ozgrid Search" ..... works well !


	VB:
	
 AllWorkbookPivots() 
    Dim pt As PivotTable 
    Dim ws As Worksheet 
     
    For Each ws In ActiveWorkbook.Worksheets 
         
        For Each pt In ws.PivotTables 
            pt.RefreshTable 
        Next pt 
         
    Next ws 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Is there similar code that would refresh all ( or selected) MS queries within a spreadsheet ?

Thanks,

Colin

Hey guys,

I am trying to refresh an excel sheet using vb .I am using the following code

myPath =
"C:Documents and SettingsrvenkataDesktopreport" 
    strInput = myPath& "Copy of test.xls"
    Set objExcel = CreateObject("Excel.Application")
    objExcel.DisplayAlerts = FALSE
    objExcel.Visible = TRUE
    Set objWorkbook = objExcel.Workbooks.Open(strInput)
    Call objWorkbook.RefreshAll()
    wscript.sleep 5000	
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit
Problem is that it comes back with a message:-

"This action will cancel a pending refresh data command. Continue?"

which requires my interverntion.

So i used
For Each Sheet In objWorkbook.Worksheets
   For Each pivot In sheet.PivotTables
       pivot.PivotCache.BackgroundQuery=false
  Next
Next
I found this code in net...I donno abt pivot tables much but I inserted the above code just before Refresh ALL statement..Its also returning error "Call was rejected by callee" ..I am not using any sql connections ..I am just getting some tables from a site ..What to do???

How to set BackgroundQuery property to false to all the tables??

Please help..THanks in Advance

Hi

I am using the 'refresh all' function to update a table of data, which it retrieves from a .csv file which resides on a web based server. The 'refresh all' function works on a macro I recorded to a button, as the sheet runs in a forced full screen mode, to make it a nicer front end for users.

It works fine as long as the user inputs the correct username and password for the FTP site, when prompted. The problem comes when they enter an incorrect password, or just click to close. It then produces a runtime error, and opens up the VB editor to debug. Is there some way I could create a text box popup instead, prompting them to check their password? The problem is also, after the first runtime error, attempting it again will produce the same result unless the application is quit and restarted.

Many thanks!

Here is the relevant code from the module :-


	VB:
	
 RefreshAll() 
     
    Application.DisplayAlerts = False 
    ThisWorkbook.RefreshAll 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Is it possible to trigger my macro to run by using the "Refresh All" button found on the Data ribbon in Excel 2010? Like you would configure a hotkey.

I have a macro to copy the last populated row of an active sheet, open an existing file, paste the row, save, and close. I would like to use the "Refresh All" button on the Active Sheet to do this. Is this possible? Any suggestions for potential code? If not, I will have to add an icon to the Quick Access toolbar.

Thank you.

Hi,

I am using Excel 2007 and wrote a lengthy script in SQL Server 2005 to pull data into Excel 2007. I placed the script in the new Excel 2007 External Data SQL window (if you don't know it it is under Data, Properties, etc.). This allows for tables to be generated in Excel more easily than previous Excel versions.

Anyway, there is a way to Refresh All data connections in Excel 2007 and I want to be able to trap that event. Does anyone know how I could do that? Is there a built in event, or would I have to use a class module etc? If a class module, could someone pt me to some code that does this? I have looked all over the web.

Depending on my boss, I may write code to pull in the data and refresh it that way. I would've preferred pulling in the recordset programmatically through vb in the first place, but at my new job they stressed trying to use built in excel functionality...so be it...

Anyway, the solution might be to bring my 400 line SQL script into vb...lol

If anyone knows how to trap the Refresh All event (or Refresh one Event), I would greatly appreciate any code and/or help.

Thank You...

Hello Guys,

I have an Excel with multiple worksheet. Some of the worksheets are populated using External Data thru MS query. and Other worksheets are attached and summarized with these external data worksheet.
=========
I populate a worksheet(COL A thru COL O) and then creates 4 additional columns (say COL P, COL Q, COL R AND COL T. COL P is the concatenation of COLC+ YEAR/MONTH FROM COLB, COL Q concatenation of (COL A,COL C), COL R is the SUM of Col N+ COL O and Col T IS THE sum of COL D.

When I entered concat formula for COL Q in cell Q2 - no problem.
When I entered concat formula for COL R in cell R2 - no problem.
and now I clicked REFRESH ALL BUTTON, everything works fine. Point to be noted here is Still I didn't entered formula for COL P. I save the file and gets out. No issues

I open the file again no problem, everything looks ok and in place. No I entered concat formula for COL P in cell P2 , and save, No problem

My problem at this stage of game
=======================
When I click the REFRESH ALL button again, all the values gets replaced in the COL P2, Q2 and R2 with LABEL FOR COL P, COL Q AND COL R.

There is no macro or VBA code running behind the scene. I am not able to figure out how the P2, Q2, R2 cell gets populated with the col LABELs. I have trying to figure out this but so far no success..

Please let me know if everything is clear here and any help will be greatly appreciated!

sba

Does anyone know how to create a PDF using code that also 'executes' the creation of the document, not just opening the print menu and then stopping and waiting for the user to take the next step?

I have the code which opens the printer menu, but I still have to "manually" tell it to create the PDF from my list of printers.

Could I create the PDF "ALL VIA CODE" with NO user input???

Thank you for any advice,
Matt

Where would I find the code for the "Refresh All" button located on the External Data Toolbar. I would like to create my own button
and place it on the worksheet.

Thanks
Wrecker 1

Hi guys. I am trying to write a subroutine that will refresh all pivot tables in a workbook. There are 12 of them. I have all of the code but can't figure out how to find out the name of each pivot table. I can do it by recording the macro but since I have several files to do this with it would be much faster if I write it manually. Can anyone tell me know to get the name of the Pivottables.

I am importing a text file that has a lot more than 256 columns. The
data will be updated weekly with a different text file. I know how to
get past the 256 column limit when importing a text file like this.
This link explains the method I am using to import the text file.
http://office.microsoft.com/en-gb/as...548201033.aspx Since I am
importing onto several sheets I get the dialogue box asking me to
choose a file to import from for every sheet when I use RefreshAll. The
same file that is used by the first sheet will be used by the rest of
the sheets. Each sheet just imports a different section of columns from
the text file. I see why Excel makes me do this. When I import the .txt
file into the first sheet I reach the 256 column limit. So on the next
sheet I run another Data>Import External Data. This time choosing to
skip the columns I have already imported into the first sheet. Then I
just repeat this process with every sheet. A bit of a pain to setup,
but it only has to be done once. That is the solution Microsoft gives
in the link I posted above when you are importing more than 256 columns
worth of data. So really I am performing a seperate query for each
sheet. I want to avoid choosing the same file 30 plus times (once for
each sheet) when I click refresh all. Is there a code that would allow
me to choose the file for the first sheet, and the other sheets would
follow suite? I want to us code. I just don't know how to go about
creating such a code. I have tried recording a macro (which is at
work), but of course this won't work everytime since the filename
changes and I don't know exactly how many sheets will be used. Right
now I have to spot check what I have to make sure I still have enough
sheets to import all of the columns. Thank you for any help you can
offer.

Hi!

I want to delete all VBA code from the EXCEL Workbook dinamically (so
from VBA program). I use this method:
...
On Error Resume Next
With ActiveWorkbook.VBProject
For x = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(x)
Next x
For x = .VBComponents.Count To 1 Step -1
.VBComponents(x).CodeModule.DeleteLines _
1, .VBComponents(x).CodeModule.CountOfLines
Next x
End With
On Error Goto 0
...

After that I have saved the workbook and I have opened it again, I get
the message box: "this workbook have macros. Would you like to allowe
them? ..." But there is not any more VBA code.

What need I to do, that after the deletion of VBA code, I do not get
this messege at the opening of the workbook again?

Thanks a lot!

Charlie

I have set up a Database query and query will get data to populate all
worksheets in a workbook. At the moment I have to go to each worksheet and
press refresh data button to update. Is there a VBA code whereby I could
refresh all worksheets in the workbook.

Thanks

I have a macro that Refreshes multiple pivot tables, then performs various
actions after the pivot tables are supposed to refresh.

However, the code keeps chugging along even before it finishes refreshing my
Pivots. Is there any way to "Pause" until all the refreshes are complete and
then finish executing the rest of the code?

I tried adding a backgroundquery = false to the end, but I guess that only
works for QueryTables and not PivotCaches.

Thanks,
Dan

I hope someone can help me with what is a relatively new but recurring problem. I have a workbook with about 200 seperate web queries. Each of the queries is to the same website, but each locates a different table of data. The data in some but not all of the tables changes daily, so I always use "refresh all" option when I open the workbook. Recently, a relatively small but signficant percentage (2-8%) of the queries is returning incomplete data: the data table is truncated either at the end or in the middle. Some existing data is left off, and some dates indicating future data is left off. Some web queries seem more prone to this than others, but none of them fails all the time - it appears to be random as to which ones fail and which ones don't on each "refresh all" command. When I survey the 16 worksheets where the web queries reside, I can individually refresh each of the ones that failed and the query returns accurate data each time. So, I concluded there is nothing wrong with the individual web queries as each one works fine when refreshed one at a time. It seems that something is causing a random glitch in the "refresh all" command.

I can run through the worksheets each time I refresh to look for failed queries and reinitiate them individually, but that sort of defeats the purpose of the automated "refresh all" command. Does anyone have an idea as to what might be causing this? One more bit of information - the website is a popular one on Yahoo (Yahoo Sports) and it occasionally freezes due to overload. This has happened occasionally while I'm trying to refresh, causing me to have to abort the process and try again later. Is is possible that the site is experiencing momentary, micro-freezes causing the truncated random tables?

Thanks to anyone who can help me with this. It's not life threatening, but it is annoying.

RBC123

Greetings,

I really hope that this is not a redundant post - I searched the archives and did not find any threads that matched the problem that I'm having. My apologies in advance to the mods if the answer is out there and I missed it....

I have a workbook with multiple tabs on it. Three of the tabs are pulling data from a sql database: two of them are returning pivot table reports and the third returns a large data table. There is an additional tab that summarizes the results of the data table into a pivot table.

The problem that I'm experiencing is when I use the refresh all button the pivot table (summarizing the sql query) is the first tab that refreshes and I need it to be the last. The results of all of these tabs are being pulled to the primary tab as a report for my ceo.

Is there any way that I can dictate what order Excel refreshes data? I am admittedly very inexperienced with VBA and I have a hunch that this is where the answer lies.

Thanks for your help.

Madan

Hi,

I need help with writing a macro that will refresh all the active cells in my "Master" worksheet.

Every month, I append data from one Excel worksheet to the end of my Master worksheet. I want all the cells in the Master worksheet to be formatted as text. However when I copy the cells, I get different formats, i.e. text, general, number, etc. Simply selecting the new data and applying the text format from the Format Cells popup, does not work. I know I need to "re-enter" all the data for the new format to take effect but I really don't want to F2-> Enter thousands of cells.

Does anyone know how to write a macro that will refresh all active cells in a worksheet?

Thanks in advance!!

Here's another daft question!

When I started using VBA, all the code for each module was shown in the code window. This was a bit cumbersome when looking for one sub in particular.

Now when I load VBA, all the Subs and Functions have their own page, and can be selected from the right hand drop down list. This is much easier for entering code, and finding subs.

However, it is sometimes useful to see 'the big picture', so how do I swap between the two types of display?

d4v3

Is there a way to close all open code windows without closing VBA? Is there a shortcut or a macro to accomplish this goal?

I am getting an error message when refreshing all pivot tables... it is stating that the pivot data file could not be opened... I don't have any links and the data reporting from my pivot tables is within my current file... is there a way to find and delete whatever it is looking for because it doesn't need to look for it anymore?

Hello!

I have a sheet with an external data source which is a csv file. I am using ActiveWorkbook.refresh as part of a macro I have written but every time it runs it asks me to choose the file in an Import Text File box. Is there anyway of disabling this and just refresh the file currently connected? I have tried application.displayalerts=false.

Thanks!

James

Hi, I've set up my Office 07 sheet to import data from various web html
tables. Upon clicking "Refresh all" my data is all updated correctly, but as
there are more rows fetched each time, I'm currently having to manually do a
new "fill down" to get my vlookup sheet to contain the last few rows. I then
have to manually update the range of each of my charts.

I hope and assume that there is a better way to do this

The exact situation is:
1) I import a bulk amount of weekly data into one sheet
2) A second sheet does a large amount of vlookup/index/match calculations to
format the data in such a way that I can make useful weekly charts out of it.
3) Charts are created from that formatted data.

I had kind of hoped that Excel would be intelligent enough to work all this
out for itself upon Refreshing the data, but I guess I'm going to have to
write some kind of VB or Macro? Can someone point me in the right direction
please?

thanks,

Alistair

On Excel 2002, the web query "Refresh All" function
fails. individual web query refresh is ok.

I have a spreadsheet which has 20 worksheets in it and each sheet contains
around 10 queries from an Access database. However, when I use the 'Refresh
All' funtion I get the following error message "[Microsoft][ODBC Microsoft
Access Driver]Cannot open any more databases".

I created a macro which refresh the queries on one worksheet at a time, but
after the running the macro on the forth the sheet the same error message
appeared.

Does anyone know how I can refresh the queries in my spreadsheet or have I
simply exeeded the number of Access queries that an Excel worksheet can
handle?

Hello:

I have a large report that has 104 imported external data links. These
links refer to 104 differnt Access queries for a file located on a shared
file server.

If I click on each link individually and select "Refresh Data" from the
data menu, no problem. But if I click on the "Refresh All" button on the
External Data tool bar I run into some problems.

After a second or two of "Connecting to data source" appears in my lower
left window, I receive an "Unspecified Error" error. When I click OK I get
the following window: Data file: "xyz" not found. Connect to "xyz" instead?
(yes, the exact same file name.)

When I click on "Yes", I'm taken to the following window: Data Links
Property. When I click on "Test Connection" it fails.

Any suggestions? Is there something about using a file on a server I need
to be careful about?

Thanks.


No luck finding an answer? You could always try Google.