Roger Govier said:
Hi Kevin
So why are you moving the Master file?
If the code within it is changing folder before listing the files, it does
not matter at all where the master file exists.
Hi Roger,
I've copied my code below if that helps. I'm not sure how it will read
contents of native folder if hardcoded ChDir ?
Cheers,
Kevin.
-------------
Sub Aut
pen()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This procedure will list, in alphabetical order, all the txt
' files that are in the same folder as the Active Workbook.
' If the active workbook has never been saved (Path = vbNullString)
' the user will be prompted to save the workbook before
' listing the files. The workbook should be saved to the same
' folder in which the text files to list reside.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim DestRng As Range ' starting point to list files
Dim FolderName As String ' the folder containing the text files
Dim FileName As Variant ' will contain the name of each text file
Dim SaveDir As String ' we'll save the default drive and folder
' to restore at the end of the procedure
Dim FirstCell As Range ' saves the first cell reference
Dim LastCell As Range ' the last cell in the file list
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Change the destination range variable DestRng to the
' cell in which you want the file list to start.
Set DestRng = ActiveWorkbook.Worksheets("MAIN SETUP PAGE").Range("AB7")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' save the first cell to use in sorting later
Set FirstCell = DestRng
' save the current drive and folder to restore at end of procedure.
SaveDir = CurDir
' if ActiveWorkbook.Path = vbNullString, the Active Workbook
' has never been saved. Prompt the user to save the workbook.
If ActiveWorkbook.Path = vbNullString Then
FileName = Application.GetSaveAsFilename( _
filefilter:="Excel Files (*.xls),*.xls", _
Title:="Save the workbook to the same folder" & _
"that contains the text files to list.")
If FileName = False Then
' user clicked cancel and didn't select a SaveAs filename.
' get out now.
Exit Sub
Else
' user selected a SaveAs Filename. Turn off DisplayAlerts
' so the user won't get a "Do you want to replace..." message.
Application.DisplayAlerts = False
' save the workbook
ActiveWorkbook.SaveAs FileName:=FileName
' turn alerts back on
Application.DisplayAlerts = True
End If
End If
' get the folder name of the active workbook
FolderName = ActiveWorkbook.Path
' change the default drive and directory to that named by FolderName
ChDrive FolderName
ChDir FolderName
' get the first text file name
FileName = Dir("*.*")
' loop until FileName = vbNullString. The Dir() command, with nothing
' within the parentheses) will return the next text file name or
' vbNullString when there are no more text files. You cannot rely
' on Dir() to return the file names in any particular order. We'll
' sort the filenames at the end of the procedure.
Do Until FileName = vbNullString
' put the full filename in the destination range
DestRng.Value = FolderName & Application.PathSeparator & FileName
' move the destination range down one row
Set DestRng = DestRng(2, 1)
' get the next text file name from the Dir() function. If there
' are no more text files, Dir() will return vbNullString
FileName = Dir()
Loop
' restore the saved default drive and path info
ChDrive SaveDir
ChDir SaveDir
' DestRng will refer to the blank cell after the end of the file list.
' Set LastCell one row above DestRng, so LastCell points to the end
' of the file list.
Set LastCell = DestRng(0, 1)
' now, sort the filenames, from FirstCell to DestRng. Use DestRng.Parent
' to ensure we're pointing to the proper worksheet.
DestRng.Parent.Range(FirstCell, LastCell).Sort key1:=FirstCell, _
order1:=xlAscending, header:=xlNo
End Sub
