Importing data from from all .xls files inside folder

[bdepizzol]

Hi! I'm new here.

I have a workbook that handles data from several source files located in a folder (C:\documents\, for instance). I'm trying to create an Excel macro that creates one sheet for every file inside this existent workbook. It should create a new sheet (if it already doesn't exist), copy all the content inside this sheet and then close the data source.

The code I have so far is not working. It's the following:

Sub FindExcelFiles()
  Dim FileName As String
  Dim FolderPath As String
    FolderPath = "C:\Documents\"
    FileName = Dir(FolderPath & "\*.xls", vbNormal)
      Do While FileName <> ""
        FileName = Dir
        Workbooks.Open (FileName)

      Loop
End Sub</pre>

Can anyone help me?

 

[wigi]

Give this a try please:

Sub FindExcelFiles()
    Dim FileName As String
    Dim FolderPath As String
    Dim wb As Workbook
    FolderPath = "C:\Documents\"
    FileName = Dir(FolderPath & "\*.xls", vbNormal)
    Do While FileName <> ""
        Set wb = Workbooks.Open(FileName)
        wb.Sheets(1).Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = Left(Left(FileName, InStrRev(FileName, ".")), 32)
        FileName = Dir()
    Loop
End Sub

 

[bdepizzol]

I'm receiving this error message on line 8:

Run-time error '1004'
'<var>Filename</var>.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.

Is there anything I can do?

 

[wigi]

There was a small typo:

Sub FindExcelFiles()
    Dim FileName As String
    Dim FolderPath As String
    Dim wb As Workbook
    FolderPath = "C:\Documents\"
    FileName = Dir(FolderPath & "*.xls")
    Do While FileName <> ""
        Set wb = Workbooks.Open(FileName)
        wb.Sheets(1).Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = Left(Left(FileName, InStrRev(FileName, ".")), 32)
        FileName = Dir()
    Loop
End Sub

 

[bdepizzol]

That "\" after C:\Documents I had corrected on my own.
The error is still there.

My Excel is in portuguese. Does it make any difference?

 

[wigi]

Oops, typo here:

Set wb = Workbooks.Open(FolderPath & FileName)

It's always a bit difficult since Dir() returns the file name it found, NOT the full path and filename. So we need to insert the folder path again when opening the returned file.

 

[bdepizzol]

It ran beautifully!

Would be possible to close the source files after the import?

Thank you!

 

[wigi]

Squeeze in this line of code:

wb.Close SaveChanges:=False

 

[bdepizzol]

Perfect!
Thank you so much for your help!!!