我想实现:在sheet1表中点击一个控件按钮,则弹出窗体选择导入excel文件,确定后导入的excel文件会替换sheet2表的内容,但不替换sheet2表的表头(第一二行)。然后提示,导入成功。
在EXCEL中好像不支持CommonDialog1.ShowOpen(打开对话框)而应用:
NewBookName=Application.GetOpenFilename("Excel文件(*.xls & .xla),*.xls;*.xla", , "导入"),所以我将楼上的代码更改如下。
1、在EXCEL中增加一个CommandButton1按钮,双击该按钮,将以下代码复制过去就可以了。
2、代码如下:
Private Sub CommandButton1_Click()
Dim NewBook As Workbook, OldBook As Workbook
Dim SrcSheet As Worksheet, DstSheet As Worksheet
Dim Rg As Range
NewBookName=Application.GetOpenFilename("Excel文件(*.xls & .xla),*.xls;*.xla", , "导入")
If NewBookName= "" Then Exit Sub
Set OldBook = ActiveWorkbook
Set DstSheet = OldBook.Worksheets(2)
Set NewBook = Workbooks.Open(NewBookName)
Set SrcSheet = ActiveSheet
Set Rg = SrcSheet.Range(SrcSheet.Cells(3, 1), SrcSheet.Cells(SrcSheet.[A65536].End(xlUp).Row, SrcSheet.usedrange.columns.count))
Rg.Copy DstSheet.Cells(3, 1)
End Sub