回复 7楼 七彩爆炸鸡
工作表的数据可以复制过去,但工作表格式无法复制。
Private Sub Form_Load()
Dim MyXlsApp, xlSheet1, excel_sheet1
'避免出现:运行时错误91,未设置对象变量或with block变量
Dim wsh
Set wsh = CreateObject("Wscript.Shell")
wsh.run "reg delete HKCU\Software\Microsoft\Office\Excel\Addins /f", 0
'读Excel文件
Set MyXlsApp = CreateObject("Excel.Application")
'打开模板文件
Set excel_sheet1 = MyXlsApp.Workbooks.Open("C:\Users\Admin\Desktop\B.xlsx")
Set excel_sheet2 = MyXlsApp.Workbooks.Open("C:\Users\Admin\Desktop\A.xlsx")
MyXlsApp.Visible = False
excel_sheet1.Worksheets(1).Activate
'设置第1个工作页为活动工作表,数字为几就是第几张表
'excel_sheet1.Worksheets("Sheet1").activate
' 设置第1个工作页为活动工作表,直接指定表名称
Set xlSheet1 = excel_sheet1.Worksheets(1)
'Set xlSheet1 = excel_sheet1.Worksheets("Sheet1")
excel_sheet2.Worksheets(1).Activate
'设置第1个工作页为活动工作表,数字为几就是第几张表
'excel_sheet2.Worksheets("Sheet1").activate
' 设置第1个工作页为活动工作表,直接指定表名称
Set xlSheet2 = excel_sheet2.Worksheets(1)
'Set xlSheet2 = excel_sheet2.Worksheets("Sheet1")
rowcount = excel_sheet1.ActiveSheet.UsedRange.Rows.Count
Columnscount = excel_sheet1.ActiveSheet.UsedRange.Columns.Count
If xlSheet1.Cells(1, 1) = "" And rowcount = 1 And Columnscount = 1 Then '写个判断语句,避免没有数据时行数和列数显示为1
MsgBox "sheet1" & "中没有数据"
Else
'MsgBox "sheet1" & "的行数为:" & rowcount & "
列数:" & Columnscount
Dim X, Y, I, J
X = rowcount
'X坐标轴,行数
Y = Columnscount
'Y坐标轴,列数
I = 1
J = 1
For J = 1 To Y + 1
If J = Y + 1 Then
J = 1
I = I + 1
If I = X + 1 Then
Exit For
End If
End If
'xlSheet1.Cells(I, J).Select
xlSheet2.Cells(I, J) = xlSheet1.Cells(I, J)
Next
End If
excel_sheet1.Close (True)
excel_sheet2.Close (True)
MyXlsApp.Quit
Set MyXlsApp = Nothing
End Sub