VBA: Problem with local save

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.

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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Just a quick look

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
.
.
.
.
If NextNetworkSave = Empty Then
    ' Create new save point
    SaveTimer = Time
    NextNetworkSave = SaveTimer + "00:15:00"
.
.
.
1) NextNetworkSave = Empty
Empty is only used for Variant type variable, so it should be 0? or check it with a msgbox for yourself.

2) What do you expect from SaveClick?
It is always 0, unless make it global.
 
Upvote 0

Forum statistics

Threads
1,214,952
Messages
6,122,458
Members
449,085
Latest member
ExcelError

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top