VBA-Excel公式参考 - 计算刷新 [英] VBA-Excel Formula Reference- Calculation Refresh

问题描述

这是我的的跟进上一篇文章。我成功地能够在不同的驱动器上打开一个不同的工作簿，将一个范围内的数据复制为图片，然后将其粘贴到 ThisWorkbook 中。我正在运行的问题现在是使用 .CopyPicture 我正在使用的是捕获单元格值，因为它们的计算结果看起来像一堆＃N /请求数据... 值。

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.

我查了这篇文章但是如果Application.CalculationState是xLodone，那么我不完全确定如何实现，然后循环else等待。任何帮助？

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?

原始代码：

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

首次尝试：

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

第二次尝试：

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

最终尝试： p>

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

编辑：使用 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

推荐答案

我把宏分成两部分，利用 Application.Run 和 Application.OnTime Now + TimeValue（00:00:05）感谢这篇文章和@cyboashu通知我。我遇到的是真实的：彭博数据不会刷新，除非宏已经结束，所以你必须将它分解成2个宏，首先刷新数据，第二个执行你想要完成的工作。

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公式参考 - 计算刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！