VB Script Help - Automatically run script on file activatation

[steve2115]

Hi,
Need help modifying the below code.
1. I would like the script to run automatically when the file is opened.
2. Instead of browsing & selecting the file location, I need it to automatically use file location "Z:\DEPTS\SAP\SupplierRequirements"
3. When file is closed I data should be cleared from sheet

Option Explicit
 
Sub GetFileNames()
     
    Dim xRow As Long
    Dim xDirect$, xFname$, InitialFoldr$
     
    InitialFoldr$ = "C:\" '<<< Startup folder to begin searching from
    Range("C3").Select ' Where to start the list of names
    With Application.FileDialog(msoFileDialogFolderPicker) 'User input for folder to look at
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count <> 0 Then ' If there are files in selected folder
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
                ActiveCell.Offset(xRow) = xFname$
                xRow = xRow + 1
                xFname$ = Dir
            Loop
        End If
    End With
End Sub

 

[JeanKim]

Private Sub Auto_Open()
    GetFileNames
End Sub

 

[steve2115]

Unfortunately this will not work. I need to modify the code so that I do not have to browse for the file location. I need to code to run when file is opened and retrieve the file names from the specified file location.

 

[JeanKim]

for #2

InitialFoldr$ = "Z:\DEPTS\SAP\SupplierRequirements"

 

[JeanKim]

for #3
Sub Auto_Close()
'what ever execution you want here
End Sub

 

[steve2115]

for #2 - getting closer...That worked. It now starts up in correct folder location however I still need to manually select it.