excel - VBA/Excel - 将工作表复制到另一个工作簿(替换现有值)
问题描述
我正在尝试将值从一张表复制到另一张工作簿表中。但是我无法让 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
当我尝试运行我的 Sub 时,它会打开src
文件并停在那里。没有值被复制并粘贴到我的currentWbk
我究竟做错了什么?
解决方案
也许我的潜艇会帮助你
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
推荐阅读
- javascript - javascript方法的返回值
- javascript - 从链接运行脚本
- c++ - 如何使用宏根据参数定义不同的数据类型?
- javascript - 将函数作为 props 传递给 React 组件会如何影响性能?
- php - 如何将临时表中的 id 更新为实际表
- ruby - CSV - 处理对某些字段具有相同值的每组连续行
- vba - 如何在 VBA 中的 MS Word 中的 mousedown 上初始化宏?
- javascript - 无法读取未定义 VUEX 的属性“提交”
- c# - 在unity / c#中单击按钮时如何突出显示按钮?
- operating-system - 分页中的用户页面和主管页面有什么区别