如何用VB控制Excel文件?
想实现一个功能就是:选中待合并的Excel表格所在的文件夹,将文件夹内所有非空的Excel表格复制粘贴到同一个工作薄中的不同工作表。请问如何实现?
简单写了一个半生不熟的雏形,能够实现本要求,但不容错,不完善,需要改进调整地方很多。自己消化完成。过程中有问题再说。
Option Explicit
Dim EXAPP As Excel.Application
Dim sourceWB As Excel.Workbook
Dim destWB As Excel.Workbook
Dim sourceSHT As Excel.Worksheet
Dim destSHT As Excel.Worksheet
Private Sub Command1_Click()
Dim i As Integer
Dim j As Integer
Dim destcount As Integer
Set EXAPP = CreateObject("excel.application")
Set destWB = EXAPP.Workbooks.Open(Text1.Text)
destcount = destWB.Worksheets.Count
For i = 0 To File1.ListCount - 1
Set sourceWB = EXAPP.Workbooks.Open(Dir1.Path & "\" & File1.List(i))
For j = 1 To sourceWB.Worksheets.Count
Set sourceSHT = sourceWB.Worksheets(j)
sourceSHT.Copy After:=destWB.Sheets(destcount)
destcount = destcount + 1
Next
Next
End Sub
Private Sub Dir1_Change()
File1.Path = Dir1.Path
End Sub
Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
End Sub
Private Sub Form_Load()
File1.Pattern = "*.xlsx"
End Sub