I want to open Excel xlsx file without writing path by using variables. I din't know why but it is not working. I have a folder with the main workbook and another one that I want to open that is xlsx. I want to name it UnionWB.
Private Sub cmdStartMonth_Click()
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
'Analyze month by selecting
Dim myPath As String
Dim myFile As String
Dim UnionWB As Workbook
Dim MonthName As String
MonthName = ListMonth.Value
myExtension = "*.xlsx*"
Set UnionWB = Workbooks.Open(ThisWorkbook.Path & myExtension)
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Set UnionWB = Workbooks.Open(ThisWorkbook.Path & myExtension)
*.xlsx
and not a specific file name.myExtension
isn't declared andThisWorkbook.Path
won't have a file separator on the the end - it should beThisWorkbook.Path & "\" & myExtension
. You'll still need to sort out which file it's trying to open though -*.xlsx
doesn't refer to a single file.MonthName
isn't used anywhere andScreenUpdating
&EnableEvents
isn't needed. – Darren Bartrup-CookThisWorkbook.Path
is a pretty static path - it's wherever the workbook containing the code is saved, so the workbook you're trying to open must be in the same folder as the original file. – Darren Bartrup-Cook