Error in opening workbook
I am using this bit of code to open a workbook where the path and
workbook name is in cell Y8 Workbooks.Open Range("Y8").Value ActiveWorkbook.RunAutoMacros xlAutoOpen However I want to do 2 things I need that if the workbook is already open the selected workbook is just activated, I must not have the option to open the workbook again and overwrite the changes made to the open workbook. If the file is not found, I get an error message and the macro must then end. The debug option must not appear. Help will be appreciated |
Error in opening workbook
Dim wb As Workbook
Dim wbShortName As String wbShortName = Right(Range("Y8"),Len(Range("Y8")) - _ InStrRev(Range("Y8"),Application.PathSeparator)) On Error Resume Next Set wb = Workbooks(wbShortName) If wb is Nothing Then If Len(Dir(Range("Y8"))) 0 Then Set wb = Workbooks.Open(Range("Y8") Else Msgbox "File Does Not Exist, Program will now Abort" End If Else wb.Activate 'Not sure if this is what you asked for or not 'What workbook did you mean by "Selected" End If Let me know what else you want this to do. -- Charles Chickering "A good example is twice the value of good advice." " wrote: I am using this bit of code to open a workbook where the path and workbook name is in cell Y8 Workbooks.Open Range("Y8").Value ActiveWorkbook.RunAutoMacros xlAutoOpen However I want to do 2 things I need that if the workbook is already open the selected workbook is just activated, I must not have the option to open the workbook again and overwrite the changes made to the open workbook. If the file is not found, I get an error message and the macro must then end. The debug option must not appear. Help will be appreciated |
Error in opening workbook
Brian,
"InStrRev" was available starting XL XP I think (or XL 2000) so if you have earlier, you'll have to find the "\" before the filename some other way. Ideally, you'll create separate functions that have the logic of determining whether a file exists, or is open. Sub test() Dim wb_name As String Dim wb_full_name As String Dim test_wb As Workbook wb_full_name = Range("Y8") wb_name = Mid$(wb_full_name, InStrRev(wb_full_name, "\") + 1) If Dir(wb_full_name) = "" Then MsgBox "File doesn't exist" Exit Sub End If On Error Resume Next Workbooks(wb_name).Activate If Err.Description < "" Then Workbooks.Open wb_full_name End If On Error GoTo 0 If ActiveWorkbook.Name = wb_name Then ActiveWorkbook.RunAutoMacros xlAutoOpen Else 'just in case I didn't think of everything above MsgBox "Something went wrong, but I'm not sure what!" End If End Sub hth, Doug wrote in message oups.com... I am using this bit of code to open a workbook where the path and workbook name is in cell Y8 Workbooks.Open Range("Y8").Value ActiveWorkbook.RunAutoMacros xlAutoOpen However I want to do 2 things I need that if the workbook is already open the selected workbook is just activated, I must not have the option to open the workbook again and overwrite the changes made to the open workbook. If the file is not found, I get an error message and the macro must then end. The debug option must not appear. Help will be appreciated |
Error in opening workbook
I have tried Doug Glancy's solution and it works perfectly. Thank you to all who replied. |
Error in opening workbook
I am using this code on XL 2000 as well and InStrRev(wb_full_name, "\") + 1) Does not work on XL 2000. Do you have an alternative? |
Error in opening workbook
maybe it's that final close paren that's causing the error.
wrote: I am using this code on XL 2000 as well and InStrRev(wb_full_name, "\") + 1) Does not work on XL 2000. Do you have an alternative? -- Dave Peterson |
Error in opening workbook
Sorry Dave, if you look further up in the topic you will see that this
is part of a routine. The full line is wb_name = Mid$(wb_full_name, InStrRev(wb_full_name, "\") + 1) What I am doing here is finding the name of the workbook in the full path name which appears in cell Y8. I believe the code InStrRev does not work in Excel 2000 and this is causing my problem. What I need is an alternative to find the workbook name. |
Error in opening workbook
InStrRev was added in xl2k. It won't work on xl97 (or earlier).
Maybe adding some message boxes (or debug.prints) to see what is in wb_full_name would help you debug the problem. Dim wb_name As String Dim wb_Full_Name As String wb_Full_Name = ThisWorkbook.FullName Debug.Print "Full name --" & wb_Full_Name wb_name = Mid$(wb_Full_Name, InStrRev(wb_Full_Name, "\") + 1) Debug.Print "name --" & wb_name Returned: Full name --C:\My Documents\excel\Book1.xls name --Book1.xls wrote: Sorry Dave, if you look further up in the topic you will see that this is part of a routine. The full line is wb_name = Mid$(wb_full_name, InStrRev(wb_full_name, "\") + 1) What I am doing here is finding the name of the workbook in the full path name which appears in cell Y8. I believe the code InStrRev does not work in Excel 2000 and this is causing my problem. What I need is an alternative to find the workbook name. -- Dave Peterson |
All times are GMT +1. The time now is 04:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com