如何使用VBA将数据从一个Excel文件复制到另一个文件夹中的另一个文件?

问题描述：

我是VBA的新手.我正在处理一个文件，该文件需要从位于不同文件夹中的另一个文件复制数据.

I am new to VBA. I am working on one file which needs to copy the data from another file which is located in different folder.

Folder 1 --> Main File---> Dashboard至Folder 2 --> Calculations---> Master File

现在，宏将从仪表板"工作表中执行，数据将从主文件"中复制并粘贴到仪表板"中.

Now the macro is to be executed from the "Dashboard" worksheet and the data is to be copied from the "Master File" and to be pasted in "Dashboard".

我尝试了一些代码，但没有帮助.

I tried few codes but they didn't help.

请帮帮我.

答

这是我使用的通用副本值宏，希望可以将您带入正确的路径.

This is a generic copy values macro I use, hopefully will put you on the right path.

Sub TransferData()
'transfer stuff from workbook 1 to workbook 2

    Dim strPath1 As String
    Dim strPath2 As String
    Dim wbkWorkbook1 As Workbook
    Dim wbkWorkbook2 As Workbook

    'define paths and filenames
    strPath1 = "C:\take from this.xlsx"
    strPath2 = "C:\put in this.xlsx"

    'open files
    Set wbkWorkbook1 = Workbooks.Open(strPath1)
    Set wbkWorkbook2 = Workbooks.Open(strPath2)

    'copy the values across
    wbkWorkbook2.Worksheets("Sheet1").Range("A1:B3").Value = _
        wbkWorkbook1.Worksheets("Sheet1").Range("A1:B3").Value

    'close the two workbooks
    wbkWorkbook1.Close (False)
    wbkWorkbook2.Close (True)

End Sub

修改

由于将工作簿1设置为运行vba的工作簿，因此在上面，您将收到错误消息，因为工作簿1确实已经打开.

Since you are setting workbook 1 as the workbook that is running the vba, in the above you will get errors because workbook 1 is indeed already open.

尝试以下代码将数据值从此工作簿"传输到工作簿2:

Try below code to transfer data values from 'this workbook' to workbook 2:

Sub TransferDataV2()
'transfer stuff from this workbook to workbook 2

    Dim strPath2 As String
    Dim wbkWorkbook1 As Workbook
    Dim wbkWorkbook2 As Workbook

    'define paths and filenames
    strPath2 = "C:\put in this.xlsx"

    'open files
    Set wbkWorkbook1 = ThisWorkbook '### changed this
    Set wbkWorkbook2 = Workbooks.Open(strPath2)

    'copy the values across
    '### change the sheet and range to what you need
    wbkWorkbook2.Worksheets("Sheet2").Range("A1:B3").Value = _
        wbkWorkbook1.Worksheets("Sheet1").Range("A1:B3").Value

    'close the workbook
    wbkWorkbook2.Close (True)

End Sub

如何使用VBA将数据从一个Excel文件复制到另一个文件夹中的另一个文件?

相关推荐