Copy data from the file regardless of any characters in the file name after the first 4 characters

[APML]

Hi all, I have the following VBA which I've been using and works well. We have changed the way in which we label the file called abcd.xlsm
We now add a date to the name, so today's file is called abcd_20221017.xlsm (so we have added an underscore and the current date to the end of the file name. so yesterday's file was called abcd_20221016.xlsm.
I'm hoping to adjust the following code so it works with any file named abcd regardless of what comes after it. The file type will always be .xlsm

Would appreciate any help


Sub Copy_PasteSpecial_Method()

If Workbook_Is_Opened("abcd.xlsx") = False Then
MsgBox "Please open File ABCD.xlsm for this operation to occur.", vbCritical, "Try Again After Opening Workbook"
Exit Sub
End If

If Workbook_Is_Opened("Journal_7111_MM.xlsm") = False Then
MsgBox "Please open [Journal_7111_MM] for this operation to occur.", vbCritical, "Try Again After Opening Workbook"
Exit Sub
End If

Dim copyRange As Range
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet1").Range("A1:IZ2121")

Dim pasteRange As Range
Set pasteRange = Workbooks("Journal_7111_MM.xlsm").Worksheets("engine").Range("A1:IZ2121")


Application.Calculation = xlCalculationManual
pasteRange.Value = copyRange.Value
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet2").Range("A1:AXG2110")
Set pasteRange = Workbooks("Journal_7111_MM.xlsm").Worksheets("ASX_Data").Range("H12").Resize(copyRange.Rows.Count, copyRange.Columns.Count)


pasteRange.Value = copyRange.Value


Application.Calculation = xlCalculationAutomatic
Workbooks("abcd.xlsx").Close SaveChanges:=False
Range("A1").Select
MsgBox ("Data has been Updated and ABCD.xlsm has been Closed." & vbCrLf & vbCrLf & "Pls delete (or rename)it." & vbCrLf & vbCrLf & "If you don't delete (or rename), it may cause a problem with tomorrows download")

End Sub

 

[Micron]

So today I get the email and the message tells me what file I need to have open: abcd10172022.xls for example.
Tomorrow the message tells me I need to open abcd10182022.xls. Is that right?
And your code is in the Journal_7111_MM.xlsm workbook?

The problem I see with your request then is that just checking that I have a file open whose name starts with abcd doesn't seem safe or reliable. If by accident I open the wrong one and run your code, it will do whatever it does to the wrong file, yes? That is because yesterday's filename starts with abcd also.

TBH I think you should have the code get the exact name from somewhere and right now I can only see that exists in the email message. There is no way I would attempt to get it from there. Unless you have some kind of date related constraint (e.g. the name of the file to be opened on any given day contains that day's date) then I can't see how you can fix this using your present method.

There are probably better solutions, such as when in "Journal_7111_MM.xlsm they have to open a file dialog and choose the file as per the email. For that, perhaps check out msoFileDialogFilePicker. Again, if the file name must match today's date you can halt the code if the chosen file name date portion doesn't match.

 

[APML]

So today I get the email and the message tells me what file I need to have open: abcd10172022.xls for example.
Tomorrow the message tells me I need to open abcd10182022.xls. Is that right?
And your code is in the Journal_7111_MM.xlsm workbook?

The problem I see with your request then is that just checking that I have a file open whose name starts with abcd doesn't seem safe or reliable. If by accident I open the wrong one and run your code, it will do whatever it does to the wrong file, yes? That is because yesterday's filename starts with abcd also.

TBH I think you should have the code get the exact name from somewhere and right now I can only see that exists in the email message. There is no way I would attempt to get it from there. Unless you have some kind of date related constraint (e.g. the name of the file to be opened on any given day contains that day's date) then I can't see how you can fix this using your present method.

There are probably better solutions, such as when in "Journal_7111_MM.xlsm they have to open a file dialog and choose the file as per the email. For that, perhaps check out msoFileDialogFilePicker. Again, if the file name must match today's date you can halt the code if the chosen file name date portion doesn't match.

So what happens (at the moment) is that you get an email with an excel file attached called abcd.xlsx, the purpose of this is to update some of the data in the workbook called Journal_711_MM.xlsm
And this works fine, there are no issues with mixing up files, etc because in the update file (abcd.xlsx) there is data that contains the latest date. (so we have that covered).

So the only thing I'm trying to achieve is to be able to have a workbook Journal_711_MM.xlsm copy the already specified ranges from abcd_20221017.xlsx or whatever date maybe at the end of abcd.

At the moment it works well with the file name abcd.xlsx
The only change I need is to be able to copy from any open files called abcd*.xlsx. I hope this makes more sense

 

[Micron]

I'm left with wondering why you don't open the abcd file and push the data into Journal workbook. Then it doesn't matter what its name is. Other than that, I have no idea unless you do something spectacular like code it Outlook (assuming that's the email app) and grab the file name from the attachment. Then have Outlook open whichever file makes sense.