问题描述
这是我的。我成功地能够在不同的驱动器上打开一个不同的工作簿，将一个范围内的数据复制为图片，然后将其粘贴到 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.
我查了和@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公式参考 - 计算刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！