Error#424
 

I have the code below and for the last few days have been trying to get it to
work
From a workbook I am trying to open/create a file, it may exist, may be open
or may need to be created if this button has not been used before. If this
will not work please direct me along the right path.

Thanks

Simon Brewster

Private Sub CommandButton3_Click()
Dim wb As Workbook
Dim wbOpen As Workbook

On Error GoTo EndMacro
Let Path = ThisWorkbook.Path & "\"
Set wb = ThisWorkbook
Set wbOpen = savedatawbt.xls

If bIsBookOpen(Path & "savedataWBT.xls") Then 'Make active
Windows(wb).Activate
Else
If BookExists(Path & "savedataWBT.xls") Then 'Open Existing File
Workbooks.Open wbOpen
Else 'Make new File
Workbooks.Add
ActiveWorkbook.SaveAs Path & wbOpen
End If
End If

'do something here with both files
EndMacro:
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
End Sub

In MODULE1
Function BookExistsName(wb As Name)
BookExistsName = Len(Dir(wb)) 0
End Function

Function BookExists(wb As String)
BookExists = Len(Dir(wb)) 0
End Function

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Error#424
 

How about something like this? (There are comments intermingled)

Option Explicit
Private Sub CommandButton3_Click()

Dim wbOpenName As String
Dim WbOpen As Workbook
'I wouldn't use Path as a variable name
'so I changed it to myPath
Dim myPath As String

myPath = ThisWorkbook.Path & "\"
wbOpenName = "savedatawbt.xls" 'it's a string here

'check to see if there is an existing file with that name
If BookExists(myPath & "savedataWBT.xls") = False Then
'nope, no file by that name
Set WbOpen = Workbooks.Add
WbOpen.SaveAs myPath & wbOpenName
Else
'check to see if it's already open
'don't pass the path--just the name
If bIsBookOpen(wbOpenName) Then
'already open, just set a variable
Set WbOpen = Workbooks(wbOpenName)
Else
'not open, so open it
'include the path and filename
Set WbOpen = Workbooks.Open(Filename:=myPath & wbOpenName)
End If
End If

'activate that workbook
WbOpen.Activate

End Sub

======
I did remove the error checking. And that may not be a good thing.

If this workbook is in a folder that the user only has read access to--and can't
save that "savedatewbt.xls" workbook (or even can't open that file because of a
password???), then the user will get an error.



Excel 2003 - SPB wrote:

I have the code below and for the last few days have been trying to get it to
work
From a workbook I am trying to open/create a file, it may exist, may be open
or may need to be created if this button has not been used before. If this
will not work please direct me along the right path.

Thanks

Simon Brewster

Private Sub CommandButton3_Click()
Dim wb As Workbook
Dim wbOpen As Workbook

On Error GoTo EndMacro
Let Path = ThisWorkbook.Path & "\"
Set wb = ThisWorkbook
Set wbOpen = savedatawbt.xls

If bIsBookOpen(Path & "savedataWBT.xls") Then 'Make active
Windows(wb).Activate
Else
If BookExists(Path & "savedataWBT.xls") Then 'Open Existing File
Workbooks.Open wbOpen
Else 'Make new File
Workbooks.Add
ActiveWorkbook.SaveAs Path & wbOpen
End If
End If

'do something here with both files
EndMacro:
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
End Sub

In MODULE1
Function BookExistsName(wb As Name)
BookExistsName = Len(Dir(wb)) 0
End Function

Function BookExists(wb As String)
BookExists = Len(Dir(wb)) 0
End Function

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

--

Dave Peterson

Error#424
 

Thanks,
good comments, i think i understand it all a little better.
SPB

"Dave Peterson" wrote:

How about something like this? (There are comments intermingled)

Option Explicit
Private Sub CommandButton3_Click()

Dim wbOpenName As String
Dim WbOpen As Workbook
'I wouldn't use Path as a variable name
'so I changed it to myPath
Dim myPath As String

myPath = ThisWorkbook.Path & "\"
wbOpenName = "savedatawbt.xls" 'it's a string here

'check to see if there is an existing file with that name
If BookExists(myPath & "savedataWBT.xls") = False Then
'nope, no file by that name
Set WbOpen = Workbooks.Add
WbOpen.SaveAs myPath & wbOpenName
Else
'check to see if it's already open
'don't pass the path--just the name
If bIsBookOpen(wbOpenName) Then
'already open, just set a variable
Set WbOpen = Workbooks(wbOpenName)
Else
'not open, so open it
'include the path and filename
Set WbOpen = Workbooks.Open(Filename:=myPath & wbOpenName)
End If
End If

'activate that workbook
WbOpen.Activate

End Sub

======
I did remove the error checking. And that may not be a good thing.

If this workbook is in a folder that the user only has read access to--and can't
save that "savedatewbt.xls" workbook (or even can't open that file because of a
password???), then the user will get an error.



Excel 2003 - SPB wrote:

I have the code below and for the last few days have been trying to get it to
work
From a workbook I am trying to open/create a file, it may exist, may be open
or may need to be created if this button has not been used before. If this
will not work please direct me along the right path.

Thanks

Simon Brewster

Private Sub CommandButton3_Click()
Dim wb As Workbook
Dim wbOpen As Workbook

On Error GoTo EndMacro
Let Path = ThisWorkbook.Path & "\"
Set wb = ThisWorkbook
Set wbOpen = savedatawbt.xls

If bIsBookOpen(Path & "savedataWBT.xls") Then 'Make active
Windows(wb).Activate
Else
If BookExists(Path & "savedataWBT.xls") Then 'Open Existing File
Workbooks.Open wbOpen
Else 'Make new File
Workbooks.Add
ActiveWorkbook.SaveAs Path & wbOpen
End If
End If

'do something here with both files
EndMacro:
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
End Sub

In MODULE1
Function BookExistsName(wb As Name)
BookExistsName = Len(Dir(wb)) 0
End Function

Function BookExists(wb As String)
BookExists = Len(Dir(wb)) 0
End Function

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

--

Dave Peterson