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]

This is a udf (user defined function)? Workbook_Is_Opened
If so it is probably necessary to see that as well. However, when posting code please do so within code tags (use vba button on toolbar) to maintain indentation and readability. The main thing would be to perform a logical IF test on the Left 4 characters of the file name looking for abcd and right function looking for xlsm, but I have no idea where at the moment.

Why not just open the workbook in code instead of prompting someone to do that?

 

[APML]

This is a udf (user defined function)? Workbook_Is_Opened
If so it is probably necessary to see that as well. However, when posting code please do so within code tags (use vba button on toolbar) to maintain indentation and readability. The main thing would be to perform a logical IF test on the Left 4 characters of the file name looking for abcd and right function looking for xlsm, but I have no idea where at the moment.

Why not just open the workbook in code instead of prompting someone to do that?

Hi, so the workbook is open, I just need the code to work if the workbook is called abcd_20221017 or anything after abcd, like abcd_20221018 (which would be today's date

 

[APML]

Hi, so the workbook is open, I just need the code to work if the workbook is called abcd_20221017 or anything after abcd, like abcd_20221018 (which would be today's date

Also sorry but I'm not so good at VBA. when you said
However, when posting code please do so within code tags (use vba button on toolbar) to maintain indentation and readability.
Is this what you meant?


Sub Copy_PasteSpecial_Method()

'First check if needed workbooks are opened. If not, notify the user and exit.
If Workbook_Is_Opened("abcd.xlsx") = False Then
MsgBox "Please open File ASX.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

'Unlike other variable types in VBA, ranges need to be "Set".
'So unlike if we assign x = 8, we have to Set rangeName = Range("A1"), for example.

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")

'Turn off calculations will assigning values to cells to save a lot of time!
Application.Calculation = xlCalculationManual

'.PasteSpecial Paste:=xlPasteValues is just the values, so we can do
'And here, we are assigning the value (a property of the range, not the range itself . . . hence just the = and not Set =).
pasteRange.Value = copyRange.Value


'But your second copy/paste is a little different.
'The table you are copying starts in cell A1, but you want it to paste in Cell I12 of the other sheet.
'The copy range is the same as before:
Set copyRange = Workbooks("abcd.xlsx").Worksheets("Sheet2").Range("A1:AXG2110")

'But we have to "resize" the PASTE range to be the same size as the copy range.
Set pasteRange = Workbooks("Journal_7111_MM.xlsm").Worksheets("ASX_Data").Range("H12").Resize(copyRange.Rows.Count, copyRange.Columns.Count)

'Again, .PasteSpecial Paste:=xlPasteValues is just the values, so we can do
pasteRange.Value = copyRange.Value

'Turn back on formula calculations (so that formulas will work as normal).
Application.Calculation = xlCalculationAutomatic

Workbooks("abcd.xlsx").Close SaveChanges:=False
Range("A1").Select
MsgBox ("Data has been Updated and ASX.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

Function Workbook_Is_Opened(workbookName As String)
Workbook_Is_Opened = False
Dim wb As Workbook
For Each wb In Application.Workbooks
If wb.Name = workbookName Then
Workbook_Is_Opened = True
Exit Function
End If
Next wb

End Function

 

[Micron]

No, that is not what I meant in my post where I explained where the button is on the toolbar but I should have said forum posting toolbar I guess.

I just need the code to work if the workbook is called abcd_20221017

Then perhaps

For Each wb In Application.Workbooks
  If Left(wb.Name, 4) = "abcd" Then

 

[APML]

No, that is not what I meant in my post where I explained where the button is on the toolbar but I should have said forum posting toolbar I guess.

Then perhaps

For Each wb In Application.Workbooks
  If Left(wb.Name, 4) = "abcd" Then

I'm a little embarrassed about how little I know about VBA (but I'm slowly picking it up), so I hope this question doesn't sound
stupid but where do I actually put that :

For Each wb In Application.Workbooks
  If Left(wb.Name, 4) = "abcd" Then

 

[Micron]

I'm not sure I get exactly what you need to do. You were looking for a specific file(s) to be opened in the application and get the user to open if not - no problem.

Now you want to open every file in a folder that starts with "abcd"?

Or you expect a specific file(s) to be open but aren't going to worry about its exact name? In that case ANY open abcd file would satisfy the requirement regardless of the appended date.

Or you want to loop over the open application files and if none start with "abcd" prompt the user?

I'm afraid I don't understand the requirement fully so can't really say where to put what. However, the first line of the suggestion was from your code - so the second line would go after that. Not sure it will solve your issue though.

 

[APML]

I'm not sure I get exactly what you need to do. You were looking for a specific file(s) to be opened in the application and get the user to open if not - no problem.

Now you want to open every file in a folder that starts with "abcd"?

Or you expect a specific file(s) to be open but aren't going to worry about its exact name? In that case ANY open abcd file would satisfy the requirement regardless of the appended date.

Or you want to loop over the open application files and if none start with "abcd" prompt the user?

I'm afraid I don't understand the requirement fully so can't really say where to put what. However, the first line of the suggestion was from your code - so the second line would go after that. Not sure it will solve your issue though.

So a while back someone in here helped me create the initial code.
The idea was if the file abcd.xlsx was opened and I hit the macro (in the workbook called Journal_7111_MM) then it would copy and special paste the ranges A1:IZ2121 and A1:AXG2110 into the workbook named Journal_7111_MM.xlsm

If the workbook abcd.xlsx wasn't open then a popup message
"Please open File ASX.xlsm for this operation to occur. Try Again After Opening Workbook" will tell the user to open
the workbook called abcd (the message say ASX.xlsm only because I haven't gotten around to changing the sentence from ASX.xlsm to abcd).
So now I have added a date to the end of the workbook abcd and this is why I need to change the VBA.

 

[Micron]

I understand what you just wrote but it doesn't answer any of the questions I posed. You're not revealing how it is determined which files you'd want to check for being open. Are they listed in a sheet somewhere?

 

[APML]

I understand what you just wrote but it doesn't answer any of the questions I posed. You're not revealing how it is determined which files you'd want to check for being open. Are they listed in a sheet somewhere?

So the people using "Journal_7111_MM.xlsm" has been opening a file (which I email daily to them) called "abcd.xlsx"
If they hit the macro in Journal_7111_MM.xlsm and they haven't got abcd.xlsx opened then the message comes up to remind them to open abcd.xlsx.
So to answer your question they don't choose from a list, they would have already needed to have opened file abcd.xlsx

I have been finding that using the same file name daily (the emailed file called abcd.xlsx) was confusing people, so now I've added a date to the end of the file name.

So I just want to copy the specified ranges from the opened file starting with abcd.

Hope this makes sense, and I do appreciate the help.