Get File Name

[MarkCBB]

Hi there,

Every time I change the file name I have to update this part of the Code.

is there a way to make it dynamic and get the file name?

Windows("Template_File open.v5.1.xlsm").Activate

 

[Andrew Poulsom]

Is your VBA code in that workbook?

ThisWorkbook.Activate

If not please post the rest of your code.

 

[MarkCBB]

Hi Andrew,

Yes its in that workbook, but it loops though other workbooks, not sure if that will make a differance? here is the rest of the code:

Option Base 1

Sub GetValues()

    Dim path As String
    Dim fileNames As Range
    Dim missingFile As String
    If Range("C1").Value = "" Then
    MsgBox ("Please fill in the file path")
    Exit Sub
    Else
 GetFileNames
    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 If
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
'My code
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("Template_File open.v5.1.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

 

[MarkCBB]

I didn it! yay

Sub OpenWorkbook(path As String, fileNames As Range)
Dim WKB As Workbook
Set WKB = ThisWorkbook

   With ActiveSheet

        For n = (Z + 1) To fileNames.Count
            If fileNames(n, 1) <> "" Then
                Workbooks.Open path & fileNames(n, 1), False, True
'My code
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    
    WKB.Activate