‘从其它工作簿(宏运行前未打开)导入数据到当期活动单元格,导入区域在代码内指定
Public Sub ImportData()
Dim i As Long
Dim SourceBook As Workbook '导入源工作簿对象
Dim TargetCell As Range '导入目标位置对象
Dim SourceRange As Range '导入源位置对象
Dim strFilePath As Variant '将要打开的文件的路径
Dim aCell As Range '循环变量
Set TargetCell = Application.ActiveWindow.ActiveCell '设置导入目标位置为当前活动单元格
strFilePath = Application.GetOpenFilename("工作表(*.xls;*.xlsx;*.xlsm;*.xlsb), *.xls;*.xlsx;*.xlsm;*.xlsb") '获取打开文件路径
If strFilePath <> False Then '如果用户没有中止打开
Set SourceBook = Application.Workbooks.Open(strFilePath) '打开工作簿并赋值于全局变量
Else
Exit Sub '跳出程序
End If
Set SourceRange = SourceBook.ActiveSheet.Range("A1:A6") '设置导入源位置为源工作簿的活动工作表的指定区域,地址("A1:A6")根据自己需要修改
i = 0 '初始化偏移值
For Each aCell In SourceRange '遍历源区域单元格
TargetCell.Offset(i, 0).Value = aCell.Value '仅导入值,不带公式,不改变格式,
i = i + 1 '偏移值加1
Next
'释放对象
Set TargetCell = Nothing
Set SourceRange = Nothing
Set SourceBook = Nothing
End Sub
温馨提示:答案为网友推荐,仅供参考