Get Workbook names from Defined path

[MarkCBB]

Hi there,

I got some code from a friend of mine that loops though a predefined path way, and opens a predefined lint of workbooks.

The challenge I am facing is that all the file names are different names, the is no pattern I can make use of, so I am looking for some code that runs before that code to get the names of the workbooks in the defined path way, and list them in column A2, A3 etc.

 

[GTO]

Are you opening all the workbooks in a given folder, where we could use the file's type to decide whether to open it, or is there some other logic test we could use?

 

[VoG]

Matbe like this

Sub test()
Dim MyFolder As String, MyFile As String
MyFolder = "C:\Mydocs\"
MyFile = Dir(MyFolder & "*.xls")
Do While MyFile <> ""
    Range("A" & Rows.Count).End(xlUp).Offset(1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[MarkCBB]

HI GTO,

The Folder will only ever have Excel workbooks in it, all of them will be .xls. yes, the code that I am using opens all listed files (Listed currently by user). I am not very good at VBA, but here is the code that I am currently usings to open all the workbooks.

Option Base 1

Sub getValues()

    Dim path As String
    Dim fileNames As Range
    Dim missingFile As String
    
    With wsControlPanel
        path = CheckPath(.Range("Path"))
        Set fileNames = .Range("FileNames")
        missingFile = checkMissingFiles(path, fileNames)
        If missingFile <> "" Then
            MsgBox missingFile & " is missing in " & path
            Exit Sub
        End If
        OpenWorkbook path, fileNames
    End With
    'Application.Calculation = xlCalculationAutomatic
    'ActiveWorkbook.Save
End Sub
Sub OpenWorkbook(path As String, fileNames As Range)

    
   With ActiveSheet

        For n = (Z + 1) To fileNames.Count
            If fileNames(n, 1) <> "" Then
                Workbooks.Open path & fileNames(n, 1), False, True
                
                'Call IMPORT_DATA
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("Template_File open.xlsm").Activate
    Sheets("INPUT").Select
    Range("A1").Select
    ActiveSheet.Paste
    ActiveWindow.ActivateNext
    Application.CutCopyMode = False

                ActiveWorkbook.Saved = True
                ActiveWorkbook.Close SaveChanges:=False
                Call IMPORT_DATA
                
            End If
        Next n
    End With

End Sub

Function checkMissingFiles(path As String, fileNames As Range) As String
    Dim fileName As String
    Dim fileFoud As Boolean
    Dim n As Integer
    For n = 1 To fileNames.Rows.Count
        If fileNames(n, 1) <> "" Then
            checkMissingFiles = fileNames(n, 1)
            fileName = Dir(path & fileNames(n, 1), vbNormal)
            If fileName <> fileNames(n, 1) Then Exit Function
        End If
    Next n
    checkMissingFiles = ""
End Function

HI VoG

I am probably doing somthing wrong, when I run your code nothing happens: here is the code that I ammended, maybe you can see if I did somting wrong?

Sub test()
Dim MyFolder As String, MyFile As String
MyFolder = "C:\Users\user\Desktop\TEST"
MyFile = Dir(MyFolder & "*.xls")
Do While MyFile <> ""
    Range("A" & Rows.Count).End(xlUp).Offset(1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[VoG]

Try

MyFolder = "C:\Users\user\Desktop\TEST\"

 

[MarkCBB]

hahaha,.... sorry, I works and its great!! thank you so much! there really should be a paypal account linked to these treads, so someone link me can pay for guys at least a donation of sorts!

Thanks again.