Excel 2010 Open method opens excel but not the specific file (1 Viewer)

Starchildren3317 · Dec 27, 2010

I have been racking my brain and surfing all over the net for an issue I am having.

I have a button control in a worksheet. When it is pressed it saves the current workbook, closes it, and then opens an new version of the workbook.

However it is not opening the file. It opens excel but the workbook doesnot open and I can not figure out why. Here is the code:

Code:

Private Sub CommandButton2_Click()
Dim fname As String

'The excel document is read only so the user must first save their work'
fname = Application.GetSaveAsFilename("C:\Program Files\IH_Workbench\LabResults", "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm,")
ThisWorkbook.SaveAs Filename:=fname, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled

'Closes the open workbook after saving
ThisWorkbook.Close

'Opens a new workbook
Workbooks.Open Filename:="C:\Program Files\IH_Workbench\LabResults.xlsm", UpdateLinks:=xlExcelLinks, ReadOnly:=True
End Sub

As pointed out however, it wont open the file. I get no error. It opens excel but that is it.

Any thoughts or suggestions?

boblarson · Dec 27, 2010

You can't close and open the same workbook that the code is in (even if you are renaming it by using the save as, because until you actually exit the code which means the workbook is closed completely it still is the same workbook). It just will not work. You would have to be running this code from a DIFFERENT workbook.

You could dynamically create a batch file to reopen the workbook after everything closes.

DCrake · Dec 27, 2010

Assumably if you close the workbook then any unread code is not handled so how does it know to open the file.

Excel 2010 Open method opens excel but not the specific file (1 Viewer)

Starchildren3317

Registered User.

boblarson

Smeghead

DCrake

Remembered

Similar threads

Users who are viewing this thread