将范围从封闭的工作簿获取到数组

问题描述

如何从封闭的工作簿中获取一系列数据到VBA中的数组中?最好不打开工作簿.

How do I get a range of data from a closed workbook into an array in VBA? Preferably without opening the workbook.

谢谢

推荐答案

不确定是否可以在不打开工作簿的情况下进行操作.我建立了如下功能.

Not sure if you can do it without opening the workbook. I built a function like below.

Private Function GetValue(path, file, sheet, ref)
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open(path & Application.PathSeparator & file)
Worksheets(sheet).Activate
GetValue = Range(ref)
wb.Close savechanges:=False
Application.ScreenUpdating = True
End Function

然后像使用它

Dim path As String
Dim file As String
Dim sht As String
Dim rng As String
path = ThisWorkbook.path
file = "book.XLSX"
sht = "Sheet1"
rng = "A1:D300"

ActiveSheet.Range("A1:D300").Value = GetValue(path, file, sht, rng)

这篇关于将范围从封闭的工作簿获取到数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！