VBA to open workbook

[SAT-65]

I'm trying to use a code to open a saved workbook. In a worksheet (Drop Page) A1 is the Month, A2 is the Day, and A3 is the year. When I change these values it is to change the name of the workbook I'm trying to open. For some reason it can't find the saved workbook. I can open the workbook manually with no issues. This is the popup I'm getting. Any help would be GREAT.

Sub FindSheet_and_open()

Dim wb As Workbook
Dim ws As Worksheet
Dim MainPath As String
Dim Month As String
Dim Day As String
Dim Year As String
Dim FullPath As String


Set wb = ThisWorkbook
Set ws = wb.Worksheets("Drop Sheet")

MainPth = "R:\Production\Production Reports\"
Month = ws.Range("B1")
Day = ws.Range("B2")
Year = ws.Range("B3")

FullPath = MainPath & Month & "-" & Day & "-" & Year & ".xlsm"

Workbooks.Open (FullPath)


End Sub

 

[Joe4]

I am not sure if it is your only problem or not, but you should NEVER use reserved words (words of existing functions, properties, methods, objects, etc) as the names of your user defined variables.
Month, Day, and Year are all names of pre-existing functions. Using them can cause unexpected results and errors.
Try choosing different variable names.

 

[SAT-65]

Thanks for the reply. I changed them and still having an issue running this code.

 

[Joe4]

After your FullPath calculation, add a message box to see what the exact value is:

MsgBox FullPath

Then verify that a file with that exact name exists in the exact file path.
And make sure that the extension matches too.

 

[SAT-65]

I added the MsgBox and it is showing the correct file name.

 

[Joe4]

It should be showing the file path too.
Where is that?

 

[rlv01]

You have a misspelled variable name that is causing problems.

MainPth = "R:\Production\Production Reports\"

Should be

MainPath = "R:\Production\Production Reports\"

To avoid that kind of error in the future, put an Option Explicit statement at the top of your code module.

 

[Joe4]

You have a misspelled variable name that is causing problems.

MainPth = "R:\Production\Production Reports\"

Should be

MainPath = "R:\Production\Production Reports\"

To avoid that kind of error in the future, put an Option Explicit statement at the top of your code module.

Good catch!

 

[SAT-65]

That was a good catch.

That was the issue. Thank you

 

[rlv01]