truncate file name in VBA

[schroederdj]

Hi everyone. I have a procedure to copy a worksheet from from a daily file into a separate monthly combined file. each additional day gets copied to the monthly file. The issue is that the daily file I get has a different file name each day. ("AYS Reconciliation 2023-06-08-08-16-19.xlsx") The sheets in the daily file have the same name each day. I was wondering if there was a way when selecting the file "AYS Reconciliation 2023-06-08-08-16-19.xlsx" it could be truncated to equal "AYS Reconciliation". Then I could source the same file name each day to copy into the monthly workbook. (I change the sheet name each day in the monthly file so there would not be a duplicate sheet name in the monthly file). Both files would be open on my desktop when running the macro so the macro does not need to do an open or save. Thanks for your help

Windows("AYS Reconciliation 2023-06-08-08-16-19.xlsx").Activate
Sheets("Division 27 Current Month").Select
Sheets("Division 27 Current Month").Copy Before:=Workbooks( _
"June 23 Factory.xlsm").Sheets(1)

 

[Micron]

When you ask a question like that it's a good idea to provide more than one example, or at least indicate the consistency of any such pattern. By that I mean if it's always the same text then it's easy. If it's always the first (e.g.) 18 characters then still simple. If it's everything before the first number, still fairly simple, but getting more difficult. If the name might contain a number, then what - everything before the current year? Or perhaps when this is done in January, it's not the current year, but the year value in the date string in the name. Perhaps there are even more possibilities, but I think you get the picture.

Pretty sure you don't need to either activate or select a sheet in order to copy it.

 

[jolivanes]

The best way would be to be consistent.
Fist sheet (left most sheet) in one workbook to another workbook as last sheet. Or last to first. Or first to first. Or last to last!!!!!

 

[jolivanes]

If you only have the two workbooks ("AYS Reconciliation 2023-06-08-08-16-19.xlsx") and Workbooks("June 23 Factory.xlsm") open:
If Workbooks("June 23 Factory.xlsm") is the ActiveWorkbook and it obviously has the code in it then

Sub Copy_Sheet()
Dim i As Long, wb2 As Workbook
Application.ScreenUpdating = False
For i = 1 To Workbooks.Count
    If Workbooks(i).Name <> "PERSONAL.XLSB" And Workbooks(i).Name <> ThisWorkbook.Name Then Set wb2 = Workbooks(i): Exit For
Next i
wb2.Sheets("Division 27 Current Month").Copy Before:=ThisWorkbook.Sheets(1)
Application.ScreenUpdating = True
End Sub

might give you what you need.

 

[schroederdj]

When you ask a question like that it's a good idea to provide more than one example, or at least indicate the consistency of any such pattern. By that I mean if it's always the same text then it's easy. If it's always the first (e.g.) 18 characters then still simple. If it's everything before the first number, still fairly simple, but getting more difficult. If the name might contain a number, then what - everything before the current year? Or perhaps when this is done in January, it's not the current year, but the year value in the date string in the name. Perhaps there are even more possibilities, but I think you get the picture.

Pretty sure you don't need to either activate or select a sheet in order to copy it.

I'm sorry I didn't include the length of the file to truncate. I meant to include it but didn't proof read my question before I sent it. The file would always need to be truncated to "AYS Reconciliation" the date and year and other characters after would always be different each day but "AYS Reconciliation" would be consistent. One of the others that responded provided the code if only the two files were open. (I haven't tried the code yet). However, I can't guarantee the user would only have the two files open. I'm not sure if the other file (the copy to file) name would matter as it is the file with the code and the copy to destination. This file would be saved, including the macro, to a new file name each month.

 

[schroederdj]

If you only have the two workbooks ("AYS Reconciliation 2023-06-08-08-16-19.xlsx") and Workbooks("June 23 Factory.xlsm") open:
If Workbooks("June 23 Factory.xlsm") is the ActiveWorkbook and it obviously has the code in it then

Sub Copy_Sheet()
Dim i As Long, wb2 As Workbook
Application.ScreenUpdating = False
For i = 1 To Workbooks.Count
    If Workbooks(i).Name <> "PERSONAL.XLSB" And Workbooks(i).Name <> ThisWorkbook.Name Then Set wb2 = Workbooks(i): Exit For
Next i
wb2.Sheets("Division 27 Current Month").Copy Before:=ThisWorkbook.Sheets(1)
Application.ScreenUpdating = True
End Sub

might give you what you need.

Thanks for the code. I have not tried it yet. The problem I may have is that I can't guarantee the user would only have the two files open. That is why I was hoping the macro could source a common file name (the truncated file name "AYS Reconciliation".

 

[schroederdj]

The best way would be to be consistent.
Fist sheet (left most sheet) in one workbook to another workbook as last sheet. Or last to first. Or first to first. Or last to last!!!!!

I agree I would like it to be consistent. That is why I'm trying to truncate the source file name to "AYS Reconciliation". Then I would consistently access the same file each day even though the actual file name includes date and other characters.

 

[jolivanes]

Try so.

Sub Copy_Sheet()
Dim i As Long, wb2 As Workbook
Application.ScreenUpdating = False
For i = 1 To Workbooks.Count
    If Workbooks(i).Name <> "PERSONAL.XLSB" And Workbooks(i).Name <> ThisWorkbook.Name And Left(Workbooks(i).Name, 18) = "AYS Reconciliation" Then Set wb2 = Workbooks(i): Exit For
Next i
wb2.Sheets("Division 27 Current Month").Copy Before:=ThisWorkbook.Sheets(1)
Application.ScreenUpdating = True
End Sub

Let us know if this helps.

Please don't quote if not absolutely required. It clutters things up.
Use Post #'s and members names instead. A lot neater.

 

[schroederdj]

Thank you both sets of code you provided work correctly depending on the number of files opened.

FYI, I'm not sure what you mean by: Please don't quote if not absolutely required. It clutters things up.
Use Post #'s and members names instead. A lot neater. Can you please let me know what you mean so I don't continue to make the same mistake?

 

[jolivanes]

Thanks for letting us know and good luck

If you visualize yourself trying to help someone and instead of going back and forth between this Post and your spreadsheet it is easier to copy the data into your spreadsheet.
However, all the quotes makes that difficult also.
IMHO, it is a lot tidier to say "in post#22 by Joe Blow it is mentioned that....." than having 26 lines of repeated data. Multiply that by multiple posts as is the case here, you'll end up with a tremendous amount of garbage (excuse my French).
But to each his own. I have noticed that quite a few helpers are against using Quote, for whatever reason, and there are people that have been helping for a very long time and are very good at it and quote at every chance they have.