wageslave101
Board Regular
- Joined
- Jul 18, 2007
- Messages
- 154
I'm trying to get a workbook to save locally if a counter is below a threshold and save to the network share if it is over.
The problem I'm having is that it doesn't appear to switch the directory to the local share when required, instead just saving it to the network share.
Yet when I step through it appears to do it - could someone shed some light on this please.
The problem I'm having is that it doesn't appear to switch the directory to the local share when required, instead just saving it to the network share.
Yet when I step through it appears to do it - could someone shed some light on this please.
Code:
Sub TestSaveLocal()
Dim wbk As Workbook
Dim HomePath As String
Dim TeamPath As String
Dim SaveClick As Integer
Dim NextNetworkSave As Date
Dim SaveTimer As Date
Set wbk = ThisWorkbook
' This is grabbed from the workbook they are using, each team has their own
TeamNumber = ThisWorkbook.Worksheets("Daily").Range("H49").Value
If TeamNumber < 9 Then
TeamNumber = "0" & TeamNumber
If TeamNumber > 10 Then
' Or if NewbieTeam
TeamNumber = "Newbies\NewbieTeam" & TeamNumber
End If
Else
'Fine as it is
End If
' set the home path - all users have one locally in the format C:\Users Local Data\ & Application.UserName
HomePath = "C:\Users Local Data\" & Application.UserName
TeamPath = "\\XXXX\XXXX$\Teams\Team" & TeamNumber
' Start the Save Timer
If SaveClick = 0 Then
SaveClick = 1
Else
SaveClick = SaveClick + 1
End If
'Check if the NextNetworkSave has been set yet, if not set it.
If NextNetworkSave = Empty Then
' Create new save point
SaveTimer = Time
NextNetworkSave = SaveTimer + "00:15:00"
Else
' timer already set
End If
If SaveTimer <> 0 Then
If Now < NextNetworkSave Then
'Time to Save to Network Share
ChDir TeamPath
wbk.Save
'Once saved return to the Local Share
ChDir HomePath
'Clear Timers
SaveTimer = ""
NextNetworkSave = ""
Else
'Just save it locally, unless...
If SaveClick > 5 Then
'Save over network
ChDir TeamPath
wbk.Save
ChDir HomePath
SaveTimer = ""
NextNetworkSave = ""
Else
' Save locally
ChDir HomePath
wbk.Save
End If
End If
Else
ChDir HomePath
wbk.Save
End If
End Sub