G
Guest
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
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