I'm running a bunch of reports that come from a couple of different spreadsheets. I'm trying to create a command-button system to allow users to run these reports. I don't want to hard-code the master data sheets' locations or names, because they change over time. I found two macros that appear to do what I want by asking the user to nominate a workbook to use. BUT, I found when I put the two together one after the other in a macro they didn't work. So, I put the first one on a separate command button, meaning the user had to nominate the master data report, then run the new report creation. I call the second one at the start of the report creation macros, to pick up the nominate file. BUT, sometimes it works, sometimes it says "Method 'Open' of object 'Workbooks' failed". Why is this happening 
?
?
Code:
Sub xUseThisFile()
'Prompt user to open file for reporting. Sets the file path needed in xOpenFile (called in each report creation macro).
Dim intChoice As Integer
Dim strPath As String
Dim varCellvalue As String
'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
'get the file path selected by the user
strPath = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)
'print the file path to cell A1
Cells(1, 1) = strPath
End If
End Sub
Sub xOpenFile()
'Opens the file nominated in Cell A1 and goes to the first sheet
varCellvalue = Range("a1").Value
Workbooks.Open varCellvalue
Sheets(1).Select
End Sub
