Excel not updating sheets after pasting data 1

[SteveCop07]

I have quite a few spreadsheets which are updated monthly from an Access database export. Access does some pre-processing and data selection and exports the raw information to the relevant workbooks.

The workbooks have a raw data sheet (where Access has pasted it's export) and a series of presentation sheets. The presentations get their data by formulae from the raw data sheet, either for direct display or further calculation then display. Can't just link to the original Access database as many of the sheets have to go offsite.

This has all worked reliably for more than 10 years. However, on conversion to Access 2013 the process no longer functions. The presentation sheets simply do not seem to see changed data in the import sheets. I have tried with both manual and automatic calculation but they both fail to update. All the formulae are in place but do not update themselves automatically. If I edit any one then it does immediately update but none of the others follow. The only way to update all the presentation sheets is manually to copy and repaste the imported data - not very stylish although I suppose I could do it automatically as the workbook opens. Can anyone perhaps explain what the issue might be or any alternative work-around?

 

[kjv1611]

Did you happen to check on the screen refresh? I'm using Office 2010, haven't tried 2013 yet, so can't say whether it's something with that version of Office. But I have run into that issue at times for some reason, and b/c of that, I built a macro linked to a button in Excel to set the options correctly for refresh and calculations.. Here's what I use behind a button:

Sub FixRefreshScr()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

I know it seems silly to have to do this, but I've had times where those options mysteriously were apparently set incorrectly.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SteveCop07]

Thanks kjv1611 for the idea. Sadly in this case it has not fixed the problem. I am also encountering numerous issues where Access cannot complete it's export and gives a range of error messages, none of which are relevant (eg things like "target object locked", "range not found", "format not found" (these are not the exact wordings - just a flavour of the things that are happening) but they all seem to come down to re-using the data area where the last pasting was done. If I delete everything then OK but if it encounters an area which is, perhaps, a row smaller than it now wants then it just throws up its hands and says "I give up"!. If I leave the target spreadsheets in the old xls format then everything is fine still so that is going to have to be the way forward for a while I think. My other option is just to spit out the data to a blank sheet every time then copy the exported data and manually paste into Excel. Really crude and something I want to avoid like the plague but it gets the job done and as these are only monthly exports, I might settle for that as a path of least resistance!!!!

 

[xlhelp]

Sounds to me like an Alpha-Numeric problem. Have you checked to see that the data arriving is indeed NUMERIC? You might be receiving data that is marked as TEXT rather than a number. That may be the reason why when you edit a cell, it populates.


Avoid Hangovers. Stay drunk.

 

[SkipVought]

It seemed, from the OP's post, "All the formulae are in place but do not update themselves automatically. If I edit any one then it does," that a FORMULA was being edited. That was puzzeling.

However, I can strongly agree with xlhelp, that very probably what appears to be NUMBERS are actually TEXT.

Copy a cell containing a 1.

Select the cells containing these TEXT values.

Edit > Paste Special -- MULTIPLY.

This will coerce TEXT containing numeric characters, to be converted to NUMBERS.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SteveCop07]

Thanks all.

The data is a mixture of both numbers and text and neither type updates at all. This is no different to what has been exported to the same spreadsheets in .XLS format for years and they work fine. There are two main differences that I have uncovered, certainly between 2003 and 2013, they may have been introduced with 2007, I don't know.
1. The problem I have outlined here which is, in summary, if you "push" data into an Excel file, it is not recognised by any other sheet than the receiving sheet, even if linked by formula
2. If you send a different amount of data (eg more or less lines), the latest versions of Excel do not handle this properly and usually fall over with a "Cannot delete named range" error.
My solutions:
1. There are two ways to get the imported (or "pushed") data recognised. Both require a macro to run as the workbook is opened. A) Copy the whole of the import sheet and immediately repaste it in position, or much more stylishly, B) use the CalculateFullRebuild command to force Excel to recalculate every formula, whether it thinks it needs to or not. Auto calc and F9 do not work because pushing the data in no longer tells Excel something needs to recalc. So the answer is put the following in the "ThisWorkbook" object:

Private Sub Workbook_open()
' Runs whenever the spreadsheet is opened.
Application.CalculateFullRebuild
End Sub

I found this by chance today - the answer to a prayer!

2. In the earlier version of Excel, pasted data was given a range name and the sending application adjusted the area for the name, making it larger or smaller to suit the new data. This no longer seems to happen. A range name is created on the first export but it does not change later. Result: a shorter export is likely to leave lines of the older data in place whilst a longer export usually fails with a "Cannot delete named range" error. The solution, annoying though it is, is that the export receipt area must be cleared before the next export. A simple macro will do this:

Sub ClearImportSheet()
' Deletes anything on the import sheet so that a new export can be run from the database

Sheets("MI_TEMP_EXTRACT").Select
Cells.Select
Selection.ClearContents
Range("A1").Select

End Sub

At present I am running this manually in Excel before I do the next export. If I were really bright I might be able to find a way of making Access do this process for me, opening up the Excel workbook, running the ClearImportSheet VBA then saving and closing the workbook before it does it's export. I'm sure that must be possible but I don't know how to do it.... yet!

Hope these notes might help someone else faced with these problems.

 

[SkipVought]

I do all my Excel data acquisition from Excel, PULLING the data when needed, via MS Query, rather than having data PUSHED into Excel. That might be an alternative.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SteveCop07]

Thanks for that idea, Skip - I have never looked at MSQuery before but it looks very interesting and powerful. And you are right, pulling the data would certainly overcome the issues I am experiencing. Plus, the data in Excel can be so easily selected and manipulated - potentially saves me having to write special queries in Access for one off exercises. Brilliant tip! Thank you very much!

 

[Deniall]

I believe that Ctrl+Alt+Shift+F9 is equivalent to
the VBA command "Application.CalculateFullRebuild".

 

[kjv1611]

One note on MS Query - at least it's been my case. Make sure your query is what you want when you build it. I've had the pleasure of dealing with this. This may only happen when you're trying to query the same workbook, I don't know.

When I've created a query, and then needed to go back later to make a minor change - ANY change - I'd end up having to totally rebuild the query from scratch. This may not be the case when querying sources outside Excel, but it's worth noting, I think.

Also, if you query an Excel workbook - make CERTAIN that workbook doesn't get its name or location changed... even if it's the same workbook you're working within. If that happens, you'll wind up having to rewrite a query again.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

With respect to kjv's comments, I have not had this problem of editing an existing query within a workbook or with any query.

It is true that when changing the workbook path/name, your query will always point to the ORIGINAL workbook path/name. But this has never been a problem for me, as I always anticipate and accomodate this by programatically modifying the Connection String. it is not difficult to do, but it must be accomplished using VBA (macro) code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]