Excel VBA可以将值从封闭的工作簿复制并粘贴到活动的工作簿Mac OS X [英] Excel VBA to copy and paste as values from a closed workbook to an active workbook, Mac OS X

问题描述

我知道这是一种常见的查询类型，但是在主动搜索之后，我还无法找到针对我的特定实例的答案.我也是VBA的新用户，所以请多多包涵.

I know this is a common type of query, but I have not been able to find an answer yet to my specific instance after active searching. I am also a new VBA user, so please bear with me.

我想从封闭的工作簿中将包含实时公式(范围A1:HW6000)的整个数据表(HISTORY.XLSM中的AllDATA选项卡)导入到活动的工作簿中，但只能将其值粘贴到活动的工作簿中.更简单地说，要将复制的工作表中的工作表作为值(而不是实时形式)复制到活动工作表中.

I want to import an entire sheet of data (AllDATA tab in HISTORY.XLSM) that contains live formulae (range A1:HW6000) from a closed workbook into an active workbook, but only paste as values into the active workbook. More simply, want to replicate one sheet from the closed workbook as values (not live formuale) into the active worksheet.

我在Mac OS X上.

I am on Mac OS X.

这是我一直在使用的代码(运行活动工作簿中的代码).

This is the code that I have been working with (running the code from the active workbook).

Sub GetRange() 
  With Range("A1:HW6000") 
    .Formula = "='C:\[HISTORY.xlsm]ALLDATA'!A1" 
    .Value = .Value 
End With 
End Sub 

此代码似乎将格式导入，但未将实际粘贴的值导入到我的活动工作簿中.

This code seems to import formats but not actual pasted values into my active workbook.

有什么想法吗?

推荐答案

您应该像这样进行真正的粘贴

You should do a real copy-paste like that

Sub ImportData()
    Dim App As New Excel.Application 'create a new (hidden) Excel

    ' remember active sheet
    Dim wsActive As Worksheet
    Set wsActive = ThisWorkbook.ActiveSheet

    ' open the import workbook in new Excel (as read only)
    Dim wbImport As Workbook
    Set wbImport = App.Workbooks.Open(Filename:="C:\History.xlsm", UpdateLinks:=True, ReadOnly:=True)

    'copy the data of the import sheet
    wbImport.Worksheets("AllDATA").Range("A1:HW6000").Copy
    wsActive.Range("A1").PasteSpecial Paste:=xlPasteFormats 'paste formats
    wsActive.Range("A1").PasteSpecial Paste:=xlPasteValues  'paste values

    App.CutCopyMode = False 'clear clipboard (prevents asking when wb is closed)
    wbImport.Close SaveChanges:=False 'close wb without saving
    App.Quit 'quit the hidden Excel
End Sub

这篇关于Excel VBA可以将值从封闭的工作簿复制并粘贴到活动的工作簿Mac OS X的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！