Hi again... I know how to check to see if the workbook exists and I found a routine to check to see if the workbook is currently open but instead of exiting the sub early, I would like to allow the user to either have the opportunity to close the open workbook prior to proceeding or even better by making use of the open workbook if it is indeed open.
I currently use the set wb1 & wb2 to open specific workbooks if they exist and are not open... can I use error handling to set an already open workbook and continue with my code?
Thus using:
Set wb2 = Workbooks.Open(wPath & w1stFolder & wFolder & wFileName2) 'If the workbook is not open
Set wb2 = the open workbook 'If workbook is already open... not sure how that would be coded!
I have been bad at using error handling but decided that I should start... I always try to code to cover all situations but I now realize that if other users are using, they will find new ways of creating errors. You can't cover everything and if you try... you will have a lot of extra un-necessary code (which is what I have now!)
Thanks
Craig
Public Sub save_current_data() 'Save verification and backup file on exit
Dim wPath As String
Dim w1stFolder As String
Dim wFolder As String
Dim wFileName1 As String
Dim wFileName2 As String
Dim x As Integer
Dim y As Long
Dim currentWB As Workbook
Dim iStr As String
Dim oldDate As Date
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim sht As Worksheet
Dim lastRow As Long
wPath = Sheets("data").Range("D2").Value 'MyDocuments Path
w1stFolder = Sheets("data").Range("D9").Value '1St Level Folder in MyDocuments "LabourForecastTool"
wFolder = Sheets("data").Range("D4").Value 'Data Folder in MyDocuments/LabourForecastTool "LabourForecastData"
wFileName1 = Sheets("data").Range("D6").Value 'Data Backup File in MyDocuments/LabourForecastTool "LabourForecastData" for Saving all data
wFileName2 = Sheets("data").Range("D7").Value 'Verification File in MyDocuments/LabourForecastTool "LabourForecastData" for Saving verification data
oldDate = Sheets("data").Cells(Sheets("data").Range("E11").Value + 11, 4).Value 'Get current working date for comparison
iStr = "Backup" & Sheets("data").Range("E11").Value 'Get current working date for comparison
Sheets("data").Cells(Sheets("data").Range("E11").Value + 11, 5).Value = "Yes" 'Mark that there is backup data on this program worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
If Dir(wPath & w1stFolder & wFolder & wFileName1) = "" Or Dir(wPath & w1stFolder & wFolder & wFileName2) = "" Then
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "One of the files is missing, unable to backup your data. The program will close without saving.", vbCritical, "CraigsWorld Error"
Exit Sub
End If
If IsFileOpen(wPath & w1stFolder & wFolder & wFileName1) = True Or IsFileOpen(wPath & w1stFolder & wFolder & wFileName2) = True Then
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Either the backup file or Verification file is currently running," & vbNewLine & _
"we are unable to save your data before exiting!", vbCritical, "CraigsWorld Error"
Exit Sub
End If
Set currentWB = ActiveWorkbook 'Track current workbook name for future use
'Save Verification Data to File
Set wb2 = Workbooks.Open(wPath & w1stFolder & wFolder & wFileName2) 'Verification File to populate current program
wb2.Unprotect Password:=currentWB.Sheets("data").Range("D10").Value 'Unprotect Workbook in our verification file
For Each sht In wb2.Worksheets
sht.Unprotect Password:=currentWB.Sheets("data").Range("D10").Value 'Unprotect all worksheets in our verification file
Next
'Start of Code to copy all info from the verification file
'verification code here
'End of Code to copy all info from the verification file
wb2.Application.Calculation = xlCalculationAutomatic
wb2.Application.Calculate
For Each sht In wb2.Worksheets
sht.Protect Password:=currentWB.Sheets("data").Range("D10").Value 'Protect all worksheets in our verification file before close it
Next
wb2.Protect Structure:=True, Windows:=False, Password:=currentWB.Sheets("data").Range("D10").Value 'Protect Workbook in our verification file before close
wb2.Close savechanges:=True 'Close our data file after saving verification data
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Saving Data Backup to File
Set wb1 = Workbooks.Open(wPath & w1stFolder & wFolder & wFileName1) 'Data File to populate current program
wb1.Unprotect Password:=currentWB.Sheets("data").Range("D10").Value 'Unprotect Workbook in our data file
For Each sht In wb1.Worksheets
sht.Unprotect Password:=currentWB.Sheets("data").Range("D10").Value 'Unprotect all worksheets in our data file
Next
'Start of Code to copy all info from the backup data file
'code here
'End of Code to copy all info from the backup data file
wb1.Application.Calculate
wb1.Application.Calculation = xlCalculationAutomatic
For Each sht In wb1.Worksheets
sht.Protect Password:=currentWB.Sheets("data").Range("D10").Value 'Protect all worksheets in our data file before close it
Next
wb1.Protect Structure:=True, Windows:=False, Password:=currentWB.Sheets("data").Range("D10").Value 'Protect Workbook in our data file before close
wb1.Close savechanges:=True 'Close our data file after importing all data
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Bookmarks