|
有幾百個excel文件的數(shù)據(jù)需要導入到一個excel中 查了下其他人的代碼，我修改了下，但是不行 哪位高手能幫幫忙啊，多謝了 附件中CZ是目標文件，其他的是數(shù)據(jù)源（有幾百個這樣的文件） 需要將源數(shù)據(jù)中的 A2:D26導入到目標文件 如果源數(shù)據(jù)名稱的最后一位是1就寫入目標文件的sheets(1) , 類推 2， 3 多謝啦 問題自己解決了 非常感謝夜兄的幫忙 我的代碼： Private Sub CommandButton2_Click() Dim myDialog As FileDialog, oFile As Object, strName As String, n As Integer Dim FSO As Object, myFolder As Object, myFiles As Object Dim fn$ Set myDialog = Application.FileDialog(msoFileDialogFolderPicker) n = 1 With myDialog If .Show <> -1 Then Exit Sub Set FSO = CreateObject("Scripting.FileSystemObject") '這是文件夾選擇,點選到你存放文件的那個 Set myFolder = FSO.GetFolder(.InitialFileName) Set myFiles = myFolder.Files a = 3 b = 3 c = 3 For Each oFile In myFiles strName = UCase(oFile.Name) strName = VBA.Right(strName, 3) If strName = "xls" Or strName = "XLS" Then '這是擴展名選擇 '下面就可接著寫打開文件讀取數(shù)據(jù)再寫入的語句了,如下: fn = myFolder & "\" & oFile.Name Workbooks.Open Filename:=fn k = Mid(oFile.Name, Len(oFile.Name) - 4, 1) * 1 Application.ScreenUpdating = False Select Case k Case 1 For i = 1 To 24 For j = 1 To 4 Workbooks(1).Sheets(k).Cells(a + i, j) = Workbooks(2).Sheets(1).Cells(i + 2, j) Next Next a = a + 24 Case 2 For i = 1 To 24 For j = 1 To 4 Workbooks(1).Sheets(k).Cells(b + i, j) = Workbooks(2).Sheets(1).Cells(i + 2, j) Next Next b = b + 24 Case Else For i = 1 To 24 For j = 1 To 4 Workbooks(1).Sheets(k).Cells(c + i, j) = Workbooks(2).Sheets(1).Cells(i + 2, j) Next Next c = c + 24 End Select Application.ScreenUpdating = True Workbooks(2).Close n = n + 1 End If Next End With End Sub [ 本帖最后由 XIEShichen 于 2009-9-10 20:57 編輯 ] |
|
|
