当前位置: 动力学知识库 > 问答 > 编程问答 >

Excel VBA - import multiple xlsx worksheets and append to master workbook sheet

问题描述:

I am trying to import multiple XLSX/XLS sheets into my Master Workbook's "temp" sheet, each workbooks content should be appended after the last entry.

This works well with just one file, but I cannot seem to work out the logic behind looping through all selected workbooks. Also, considering performance and speed, is a wise to open all selected workbooks after they have been selected or should I rather open them after the import of the previous file is done?

Would appreciate your kind help.

EDIT:

To be a bit more precise, the problem with the script is the part where I am supposed to look through all open workbooks. Suggestions anyone?

For Each Workbooks In lngCount

With Workbooks

.Sheets(1).Cells.Copy Destination:=MasterWB.Sheets("temp").Range("A65536").End(xlUp).Offset(1, 0)

.Close False

End With

Next

Full VBA Code:

Sub import_XLS()

Dim wb As Workbook

Dim lngCount As Long

' speed up by turning screenupdating off

Application.ScreenUpdating = False

' set workbooks

Set MasterWB = ActiveWorkbook

With ActiveWorkbook.Sheets("temp")

.Visible = True

.Cells.Delete

End With

' Open the file dialog

With Application.FileDialog(msoFileDialogOpen)

.InitialFileName = ""

.Title = "Please select the converted User Activity files for import"

.Filters.Add "Excel Files", "*.xls; *.xlsx", 1

'.Filters.Add "Excel Files", "*.xls", 1

.AllowMultiSelect = True

If .Show = -1 Then

' Open the files

For lngCount = 1 To .SelectedItems.Count

Workbooks.Open .SelectedItems(lngCount)

Next lngCount

Else

Exit Sub

End If

End With

' open selected workbook in read only and copy all cells of worksheet 1

For Each Workbooks In lngCount

With Workbooks

.Sheets(1).Cells.Copy Destination:=MasterWB.Sheets("temp").Range("A65536").End(xlUp).Offset(1, 0)

.Close False

End With

Next

' hide temp sheet, close workbook without saving changes and free memory

'MasterWB.Sheets("temp").Visible = False

'wb.Close False

Set wb = Nothing

End Sub

网友答案:
Dim wb As Workbook
....

For Each wb In Workbooks
    with wb
        if .name <>MasterWB.Name
        ....    
    end with
Next wb
分享给朋友:
您可能感兴趣的文章:
随机阅读: