代码之家 › 专栏 › 技术社区 › oliverbj

VBA/Excel-将工作表复制到其他工作簿(替换现有值)

vba excel

oliverbj · 技术社区 · 6 年前

我正在尝试将值从一个工作表复制到另一个工作簿工作表中。但是,我无法让Excel实际将值粘贴到另一个工作簿。

这是我的密码。

Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim src As Workbook ' SOURCE
    Dim currentWbk As Workbook ' WORKBOOK TO PASTE VALUES TO

    Set src = openDataFile
    Set currentWbk = ActiveWorkbook

     'Clear existing data
     currentWbk.Sheets(1).UsedRange.ClearContents

     src.Sheets(1).Copy After:=currentWbk.Sheets(1)

    ' CLOSE THE SOURCE FILE.
     src.Close False  ' FALSE - DON'T SAVE THE SOURCE FILE.
     Set src = Nothing

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

下面是函数 openDataFile

Function openDataFile() As Workbook
'
Dim wb            As Workbook
Dim filename      As String
Dim fd            As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the file to extract data"

' Optional properties: Add filters
fd.Filters.Clear
fd.Filters.Add "Excel files", "*.xls*" ' show Excel file extensions only

' means success opening the FileDialog
If fd.Show = -1 Then
    filename = fd.SelectedItems(1)
End If

' error handling if the user didn't select any file
If filename = "" Then
    MsgBox "No Excel file was selected !", vbExclamation, "Warning"
    End
End If

Set openDataFile = Workbooks.Open(filename)

End Function

当我试图运行我的潜艇时,它会打开 src currentWbk

我做错什么了?

1 回复 | 直到 6 年前

Alexey C 6 年前

也许我的潜艇会帮助你

Public Sub CopyData()
    Dim wb As Workbook
    Set wb = GetFile("Get book") 'U need use your  openDataFile  here
    Dim wsSource As Worksheet
    Set wsSource = wb.Worksheets("Data")'enter your name of ws

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add

    wsSource.Cells.Copy ws.Cells
    wb.Close False

End Sub

推荐文章