VBA-Excel Formula Reference- Calculation Refresh

Question

This is a follow up to my previous post. I successfully was able to open a different workbook on a different drive, copy the data in a range as a picture, and then paste it in ThisWorkbook.The problem I'm running into now is that the .CopyPicture I'm using is capturing the cell values as they calculate so it ends up looking like a bunch of #N/A Requesting Data... values.

I've used a few different things to see if I can get the formulas to calculate before copying them, but it seems like the spreadsheet will not follow through with the calculation until the macro is no longer running.

I checked this post but I'm not entirely sure how to implement the if Application.CalculationState is xLdone then loop else wait. Any help with this?

Original Code:

Dim BBPic As Workbook
Dim test As Workbook
Set BBPic = Application.Workbooks.Open("\\OtherDrive\Shared\OtherGroup\DailySheet.xlsx")
Set test = ThisWorkbook

BBPic.Sheets("Sheet1").Range("B2:E16").CopyPicture
test.Sheets("Summary").Range("B64").PasteSpecial

First attempt:

Dim BBPic As Workbook
Dim test As Workbook
Set BBPic = Application.Workbooks.Open("\\OtherDrive\Shared\OtherGroup\DailySheet.xlsx")
Set test = ThisWorkbook

BBPic.Sheets("Sheet1").Range("B2:E16").CopyPicture
Application.Wait (Now + TimeValue("0:01:00"))
test.Sheets("Summary").Range("B64").PasteSpecial
Workbooks("DailySheet.xlsx").Close SaveChanges:=False

Second attempt:

Dim BBPic As Workbook
Dim test As Workbook
Set BBPic = Application.Workbooks.Open("\\OtherDrive\Shared\OtherGroup\DailySheet.xlsx")
Set test = ThisWorkbook

BBPic.Sheets("Sheet1").Range("B2:E16").CopyPicture
ActiveWorkbook.RefreshAll
test.Sheets("Summary").Range("B64").PasteSpecial
Workbooks("DailySheet.xlsx").Close SaveChanges:=False

Final attempt:

Dim BBPic As Workbook
Dim test As Workbook
Set BBPic = Application.Workbooks.Open("\\OtherDrive\Shared\OtherGroup\DailySheet.xlsx")
Set test = ThisWorkbook

BBPic.Sheets("Sheet1").Range("B2:E16").CopyPicture
ActiveSheet.Calculate
test.Sheets("Summary").Range("B64").PasteSpecial
Workbooks("DailySheet.xlsx").Close SaveChanges:=False

EDIT: 4th attempt using the Application.CalculationState = xlDone

Dim BBPic As Workbook
Dim test As Workbook
Set BBPic = Application.Workbooks.Open("\\OtherDrive\Shared\OtherGroup\DailySheet.xlsx")
Set test = ThisWorkbook

BBPic.Sheets("Sheet1").Select
Do Until Application.CalculationState = xlDone: DoEvents: Loop

ActiveSheet.Range("B2:E16").CopyPicture
test.Sheets("Summary").Range("B64").PasteSpecial
Workbooks("DailySheet.xlsx").Close SaveChanges:=False

"if Application.CalculationState is xLdone then loop else wait" basically means `Do Until Application.CalculationState = xlDone: DoEvents: Loop` (in case you didn't know, colon `:` separates statements in VBA) — Mikegrann, Aug 29 '16 at 19:32
I edited in my 4th attempt using it but it did not seem to work. — plankton, Aug 29 '16 at 19:54
@cyboashu yes. It's funny because I'm stepping into the code and after I open up the sheet, no matter what I do it won't follow through with the calculation, but once I end the macro, the calculations go through. — plankton, Aug 29 '16 at 20:21
here : http://stackoverflow.com/questions/4298934/vba-waiting-for-bloomberg-bdp-calls-to-finish and this one too; http://stackoverflow.com/questions/8669845/bloomberg-data-doesnt-populate-until-excel-vba-macro-finishes — cyboashu, Aug 29 '16 at 20:39
@cyboashu So I broke it out into 2 macros and it worked. Referencing this post (http://stackoverflow.com/questions/8669845/bloomberg-data-doesnt-populate-until-excel-vba-macro-finishes) — plankton, Aug 30 '16 at 13:27

score 1 · Accepted Answer · edited May 23 '17 at 12:22

I broke my macro into two, utilizing Application.Run and Application.OnTime Now + TimeValue("00:00:05") thanks to this post and @cyboashu for informing me. What I was experiencing was true: Bloomberg data will not refresh unless the macro has ended, so you have to break it out into 2 macros with the first refreshing the data and the second performing what you want done.

Sub OpenDailySheet()
'
'Macro
'

'

Dim BBPic As Workbook
Set BBPic = Application.Workbooks.Open("\\OtherDrive\Shared\OtherGroup\DailySheet.xlsx")
Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:05"), "PasteChart"


End Sub

Sub PasteChart()

Dim test As Workbook
Set test = ThisWorkbook

Workbooks("DailySheet.xlsx").Sheets("Sheet1").Range("B2:E16").CopyPicture
test.Sheets("Summary").Range("B64").PasteSpecial
Workbooks("DailySheet.xlsx").Close SaveChanges:=False

End Sub

VBA-Excel Formula Reference- Calculation Refresh

1 Answers1

Linked