Thanks for looking at this! I'm an intermediate beginner in VBA. I am writing in Microsoft 2010 OS and Excel 365. I have written a script to process a 150K row file when it is executed. Currently it opens a source file located at a sharepoint drive. that path looks like this:
"\(server name)\SQL Files\2022\06.2022\June2022Name.xlsx"
The file I would like VBA to open automatically is the one that matches the current month, as there are files for each month over the past year and a new folder is created as the months progress, ie, there is currently not a folder for July but it will be written as 2022\07.2022\July2022Name.xlsx
I've looked at several name selection scripts and am unable to direct VBA to do so. I am missing steps I believe. This is what I have tried so far
'Find Workbook Filename <br>
Dim dt as String, dt2 as string
Filename as String, strMonth as string<br>
strMth = Monthname(Month(Now()), True<br>
if Month(Now()) = 1 Then<br>
strPriorMth = MonthName(12,True)<br>
Else st PriorMth = Monthname(Month(Now())-1, True<br>
dt2 = StrMonth(mm).YYYY
dt = StrMonth(mmm)YYYY
<br>
filename = "\\(server name)\SQL Files\2022\"dt2"\"dt & "SqlViews.xlsx"<br>
I apologize for having trouble with something so simple. I'm unsure how to cast the dt2 as a number string and dt as what I think is a nvarchar string. Any assistance would be invaluable!
Not sure the exact output you want. But here is an example.
Sub test()
Dim dt As String, dt2 As String
Dim filename As String, strMonth As String
strMth = MonthName(Month(Now()))
If Month(Now()) = 1 Then
mth = "12"
strPriorMth = MonthName(12, True)
Else
mth = Month(Now() - 1)
strPriorMth = MonthName(Month(Now()) - 1, True)
End If
dt = Format(mth, "mm")
dt2 = Format(mth, "mmm")
filename = "\\(server name)\SQL Files\2022\" & dt2 & "\" & dt & "SqlViews.xlsx"
Debug.Print filename
End Sub