Can't import files, should be simple code

[Obfuscated]

I'm having trouble importing files. Here is just the import portion that I'm trying to build (that I can't make work).

Sub Import_Files()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim path1 As String
Set ws = Worksheets("Main")
Set ws1 = Worksheets("Lists")
Set xl = CreateObject("Excel.Sheet")
path1 = ws1.Range("B2").Value ' will be my development directory until put in use
Application.ScreenUpdating = False

CurrentFileName = Dir(path1 & "\*.xls")

'H:\zz - working on it\CutSheet ' my development directory

Do
    xl.Application.Workbooks.Open Dir(path1 & CurrentFileName)
    
    Sheets().Move after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    CurrentFileName = Dir()
Loop While CurrentFileName <> ""


End Sub

I have all the files in place in the "dev" folder, but keep getting an error that the filename is not found.

The file names that will be imported are:
W14X99992.xls
W14X99588.xls
W14X99AHA.xls
and so on...

There may be as few as 2 files or as many as 20 files to include in this new workbook. They are not static names or I could simple name all the options, so I need a wildcard entry.

Many thanks.

[AlphaFrog]

Try something like this...

xl.Application.Workbooks.Open path1 & "\" & CurrentFileName

[Obfuscated]

I've tried that also. Keep getting the same error.
When I debug the lines, the path and file names are correct but I get the "File not found" error, check spelling, etc. messagebox.

I've attached the workbook I've started. The files trying to be opened are single sheet books with no macros.

[AlphaFrog]

Did you use exactly this?
xl.Application.Workbooks.Open path1 & "\" & CurrentFileName


Because that is not the same as this (from your code in the attached file) which uses the Dir function
xl.Application.Workbooks.Open Dir(path1 & "\" & CurrentFileName)

The Dir function only returns the file name which you already have in CurrentFileName

[Obfuscated]

Thanks guys for the help. You were right AlphaFrog, I did forget to change the "DIR" to "PATH1". It works great. Problem solved, onward to the next one.

[tigeravatar]

Obfuscated,

Give this a try:

Sub tgr()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lCalc As XlCalculation
    Dim strFolderPath As String
    Dim strFileName As String
    
    Set wb = ActiveWorkbook
    strFolderPath = wb.Sheets("Lists").Range("B2").Text
    If Right(strFolderPath, 1) <> Application.PathSeparator Then strFolderPath = strFolderPath & Application.PathSeparator
    strFileName = Dir(strFolderPath & "*.xls")
    
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    On Error GoTo CleanExit
    
    Do
        With Workbooks.Open(strFolderPath & strFileName)
            For Each ws In .Sheets
                ws.Copy After:=wb.Sheets(wb.Sheets.Count)
            Next ws
            .Close False
        End With
    Loop While Len(strFileName) > 0
    
CleanExit:
    With Application
        .Calculation = lCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    If Err.Number <> 0 Then
        MsgBox Err.Description, , "Error: " & Err.Number
        Err.Clear
    End If
    
    Set wb = Nothing
    Set ws = Nothing
    
End Sub