CODEWITHSUNDEEP
vbaexcel
plankton
plankton

Reputation: 325

VBA-Excel Formula Reference- Calculation Refresh

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

Upvotes: 1

Views: 1061

Answers (1)

plankton
plankton

Reputation: 325

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

Upvotes: 1

Related Questions