CODEWITHSUNDEEP
vbaexcel
Smits
Smits

Reputation: 55

Automatically update macro when changing filename

I have the following macro to filter specific data out of my directory with employee hours files and place it into my zmaster file. However, I need various master documents for various projects (EG. change name to: "project 300000"). When I change my master file name from zmaster to anything else, my macro cannot find the appropriate file, obviously.

Is there a way to change my macro in such a way that zmaster.xlsm is automatically replaced in my macro by the current file name?

Option Explicit

Sub CopyToMasterFile()

    Dim MasterWB As Workbook
    Dim MasterSht As Worksheet
    Dim MasterWBShtLstRw As Long
    Dim FolderPath As String
    Dim TempFile
    Dim CurrentWB As Workbook
    Dim CurrentWBSht As Worksheet
    Dim CurrentShtLstRw As Long
    Dim CurrentShtRowRef As Long
    Dim CopyRange As Range
    Dim ProjectNumber As String


    FolderPath = "C:\test\"
    TempFile = Dir(FolderPath)

    Dim WkBk As Workbook
    Dim WkBkIsOpen As Boolean

    'Check if zmaster is open already
    For Each WkBk In Workbooks
        If WkBk.Name = "zmaster.xlsm" Then WkBkIsOpen = True
    Next WkBk

    If WkBkIsOpen Then
        Set MasterWB = Workbooks("zmaster.xlsm")
        Set MasterSht = MasterWB.Sheets("Sheet1")
    Else
        Set MasterWB = Workbooks.Open(FolderPath & "zmaster.xlsm")
        Set MasterSht = MasterWB.Sheets("Sheet1")
    End If

    ProjectNumber = MasterSht.Cells(1, 1).Value



    Do While Len(TempFile) > 0

        'Checking that the file is not the master and that it is a xlsx
        If Not TempFile = "zmaster.xlsm" And InStr(1, TempFile, "xlsx", vbTextCompare) Then

            Set CopyRange = Nothing

            'Note this is the last used Row, next empty row will be this plus 1
            With MasterSht
                MasterWBShtLstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
            End With

            Set CurrentWB = Workbooks.Open(FolderPath & TempFile)
            Set CurrentWBSht = CurrentWB.Sheets("Sheet1")

            With CurrentWBSht
                CurrentShtLstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
            End With

            For CurrentShtRowRef = 1 To CurrentShtLstRw

             If CurrentWBSht.Cells(CurrentShtRowRef, "A").Value = ProjectNumber Then

               'This is set to copy from Column A to Column L as per the question

               If CopyRange Is Nothing Then
                 'If there is nothing in Copy range then union wont work
                 'so first row of the work sheet needs to set the initial copyrange
                  Set CopyRange = CurrentWBSht.Range("A" & CurrentShtRowRef & _
                                                ":L" & CurrentShtRowRef)
                Else
                  'Union is quicker to be able to copy from the sheet once
                  Set CopyRange = Union(CopyRange, _
                                        CurrentWBSht.Range("A" & CurrentShtRowRef & _
                                                            ":L" & CurrentShtRowRef))
               End If  ' ending   If CopyRange Is Nothing ....
             End If ' ending  If CurrentWBSht.Cells....

            Next CurrentShtRowRef

            CopyRange.Select

            'add 1 to the master file last row to be the next open row
            CopyRange.Copy MasterSht.Cells(MasterWBShtLstRw + 1, 1)

            CurrentWB.Close savechanges:=False

        End If     'ending            If Not TempFile = "zmaster.xlsx" And ....

        TempFile = Dir

    Loop

ActiveSheet.Range("A1:L200").RemoveDuplicates Columns:=Array(1, 2, 4, 8, 9, 10, 11, 12), Header:=xlYes

End Sub

Upvotes: 2

Views: 1716

Answers (1)

codtex
codtex

Reputation: 6548

One way to escape from hard coded workbook names is to use ActiveWorkbook or ThisWorkbook objects - they both return instance of Workbook object.

ThisWorkbook

Returns a Workbook object that represents the workbook where the current macro code is running. Read-only.

ActiveWorkbook

Returns a Workbook object that represents the workbook in the active window (the window on top). Read-only. Returns Nothing if there are no windows open or if either the Info window or the Clipboard window is the active window.

Then you can get the name of the workbook with Name property of the returned Workbook object.

Another way could be if you pass such a data as parameter to your functions. For example:

Sub CopyToMasterFile(wbName as String, sheetName as String)

In this variant if you call your Sub from another macro code, you can pass whatever you want to use - this ways you can escape the hard coded stuff in your functions.

This is also valid for the Worksheet objects - have a look on the ActiveSheet

Upvotes: 1

Related Questions