Function DirFajl(Putanja As String, Optional Kriterija As String = "*")

On Error GoTo Error_Handler

    Dim Fajl As String

    Fajl = Dir(Putanja & "*." & Kriterija)

        Do While Fajl <> vbNullString

            If Fajl <> "." And Fajl <> ".." Then

                    Debug.Print "- " & Fajl

            End If

            Fajl = Dir

        Loop

Error_Handler_Exit:

    On Error Resume Next

    Exit Function

Error_Handler:

    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _

            "Error Number: " & Err.Number & vbCrLf & _

            "Error Source: DirFajl" & vbCrLf & _

            "Error Description: " & Err.Description, _

            vbCritical, "An Error has Occured!"

    Resume Error_Handler_Exit

End Function

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False

Application.DisplayAlerts = False

directory = "c:\test\"

fileName = Dir(directory & "*.xl??")

Do While fileName <> ""

        Workbooks.Open (directory & fileName)

        For Each sheet In Workbooks(fileName).Worksheets

                total = Workbooks("import-sheets.xls").Worksheets.count

                Workbooks(fileName).Worksheets(sheet.Name).Copy _

                        after:=Workbooks("import-sheets.xls").Worksheets(total)

        Next sheet

        Workbooks(fileName).Close

        fileName = Dir()

Application.ScreenUpdating = True

Application.DisplayAlerts = True

excelFile= array("knjiga1.xlms","knjiga2.xlms","knjiga3.xlms")

dir="c:\excelFile\"

 

for f=1 to ubound(excelFile)

    fileName=Dir(dir & excelFile(f))

    ...

    kod koji otvori excel file i kopira sve listove u trenutni excel file

    ...

next f

Private Sub CommandButton2_Click()

On Error GoTo Error_Handler

   

    Dim putanja As String

    Dim Fajl As String

    Dim kriterija As String

    Dim puninaziv As String

    Dim book As Workbook

   

    Application.ScreenUpdating = False

    kriterija = "xls"   ' za xls (excel 2003 i nize fajlove)

   putanja = Application.ThisWorkbook.Path 'nalazi tekuci direktorijum

    puninaziv = putanja & "\*." & kriterija

    Fajl = dir(puninaziv)

        Do While Fajl <> vbNullString

            If Fajl <> "." And Fajl <> ".." And Fajl <> Application.ThisWorkbook.Name Then ' da ne importuje tekuci fajl

         

                Set book = Workbooks.Open(Fajl, True, True)

                    For Each tabla In book.Sheets

                    If tabla.UsedRange.Cells.Count > 1 Then ' da li i sheetu ima podataka

                       tabla.Copy after:=Sheet1

                    End If

                Next tabla

 

                book.Close

                 

            End If

            Fajl = dir

        Loop

        Application.ScreenUpdating = True

       

Error_Handler_Exit:

    On Error Resume Next

    Exit Sub

Error_Handler:

    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _

            "Error Number: " & Err.Number & vbCrLf & _

            "Error Source: DirFajl" & vbCrLf & _

            "Error Description: " & Err.Description, _

            vbCritical, "An Error has Occured!"

    Resume Error_Handler_Exit

End Sub

Option Explicit

 

Function Kopiraj()

 Dim ObEx As Excel.Application

 Dim Vork As Excel.Workbook, VorkN As Excel.Workbook

 Dim ExSit As Excel.Worksheet, ExSitN As Excel.Worksheet

 Dim excelFile, DirF As String, Filename As String, Putanja As String

 Dim f As Integer, I As Integer

 

excelFile = Array("knjiga1.xlms", "knjiga2.xlms", "knjiga3.xlms")

DirF = "c:\excelFile\"

Putanja = DirF & "Zbirna.xlms"

Set ObEx = New Excel.Application

Set VorkN = ObEx.Workbooks.Add

VorkN.SaveAs Putanja

For f = 0 To UBound(excelFile)

    Filename = Dir(DirF & excelFile(f))

    If Filename <> "" Then

    Putanja = DirF & Filename

    Set Vork = ObEx.Workbooks.Open(Putanja)

    For Each ExSit In Vork.Sheets

    I = I + 1

    ExSit.Copy VorkN.Sheets(I)

    VorkN.Sheets(I).Name = Filename & "_" & ExSit.Name

    Next ExSit

    Vork.Close

    End If

Next f

VorkN.Save

VorkN.Close

End Function