Avoiding hard coding a path and error handling

Question

I am new to VBA and just can't seem to get my head around this. So I have a source excel file where I copy the necessary data from a selected worksheet and then paste it to a main workbook (everyday use) to the exact worksheet using my macro. What I would like to do is avopid hard coding a path of the source file so that if the source file name changes that it still finds and opens the necessary source file and gets the data that is required. Also I would like to implement error handling to ensure that the source file is present and can be opened before running the macro.

Sub MacroCopy()
Workbooks.Open "C:\Users\xxxx\OneDrive\Desktop\OneDrive_0000-00-00\Project\Sources\sourcefile.xlsx"

ActiveSheet.ListObjects("Sourcesheet").Range.AutoFilter Field:=16, Criteria1:=Array("1", "2", "3", "4", "5"), Operator:=xlFilterValues

Workbooks("sourceworkbook.xlsx").Worksheets("sourceworksheet").Range("B5:EO11332").Copy _
ThisWorkbook.Worksheets("targetworksheet").Range("B5")

Workbooks("sourceworksheet.xlsx").Close SaveChanges:=True
 
End Sub

How do you expect Excel to know the source file has changed? Is it the only file (or only xlsx file) in the Sources folder? — CLR, Jan 27 '23 at 10:35
Unfortunately no it is not the only file, The issue what I am trying to prevent is that if a user changes the name of the source file or the name of the main work book that it still somehow works, I know it sounds crazy but I can sort that out. — Aml_Bob, Jan 27 '23 at 11:54
So, you could have the program look for sourcefile.xlsx, but if that isn't present then it prompts the user to select a file manually, just for that one run..? Would that work for you? — CLR, Jan 27 '23 at 12:03
Yes that would help, in that case he can search for the file — Aml_Bob, Jan 27 '23 at 12:29

CLR · Answer 1 · 2023-01-27T12:28:11.940

A couple of ideas:

This code will attempt to open any file in the folder - which in your comments since, you suggest won't necessarily be correct.

Sub MacroCopy_v2()

    Dim fldr As String, fnm As String, wbSource As Workbook
    fldr = "C:\Users\xxxx\OneDrive\Desktop\OneDrive_0000-00-00\Project\Sources\"

    fnm = Dir(fldr & "*.xlsx")
    
    If fnm = "" Then
        MsgBox "No file found. Aborting."
    Else
        Set wbSource = Workbooks.Open(fldr & fnm)
        wbSource.ActiveSheet.ListObjects("Sourcesheet").Range.AutoFilter Field:=16, Criteria1:=Array("1", "2", "3", "4", "5"), Operator:=xlFilterValues
        wbSource.Worksheets("sourceworksheet").Range("B5:EO11332").Copy _
                            ThisWorkbook.Worksheets("targetworksheet").Range("B5")
        wbSource.Close SaveChanges:=True
    End If
    
End Sub

You might want to specify the sheet name, rather than use ActiveSheet in case there are multiple sheets available.

I'm slightly puzzled also by the SaveChanges:=True - as you're not changing the source workbook (as far as I can tell).

This code looks for a default filename and if that isn't present in the folder then it opens a prompt for the user to manually select a file.

Sub MacroCopy_v3()

    Dim fldr As String, defaultfnm As String, openpath As String, wbSource As Workbook
    fldr = "C:\Users\xxxx\OneDrive\Desktop\OneDrive_0000-00-00\Project\Sources"
    
    defaultfnm = "sourcefile.xlsx"
    
    openpath = fldr & "\" & defaultfnm
    If Dir(openpath) = "" Then
        ChDrive "C:"
        ChDir fldr
        openpath = Application.GetOpenFilename("Source file (*.xlsx),*.xlsx")
    End If
    
    If openpath <> "False" Then
        Set wbSource = Workbooks.Open(openpath)
        wbSource.ActiveSheet.ListObjects("Sourcesheet").Range.AutoFilter Field:=16, Criteria1:=Array("1", "2", "3", "4", "5"), Operator:=xlFilterValues
        wbSource.Worksheets("sourceworksheet").Range("B5:EO11332").Copy _
                            ThisWorkbook.Worksheets("targetworksheet").Range("B5")
        wbSource.Close SaveChanges:=True
    Else
        MsgBox "No file selected. Aborting."
    End If
    
End Sub

Detecting if a worksheet exists - you can find functions to perform that here:

Test or check if sheet exists

You could do something similar to detect if the ListObject exists also.

The SaveChanges:=True was just to avoid possible questions upon exit — Aml_Bob, Jan 27 '23 at 11:56
`SaveChanges:=True` basically performs a save then close without prompt. `SaveChanges:=False` performs just a close, without saving and without a prompt. — CLR, Jan 27 '23 at 12:05
The code runs like clock work, Thank you! Is there a way to check if the target sheet already exists befor it is deleted and can a check be impemented that would ensure that the filter criteria in the source file actually exist befor the data gets copied? — Aml_Bob, Jan 27 '23 at 12:16

Avoiding hard coding a path and error handling

1 Answers1