help with CHDIR please

[Emjaye]

I have several workbooks all on the same network drive but in different folders.
When they are first opened in the new year, the workbook is supposed to archive its old data and create a new blank file ready for use in the new year
When I tested the code (below) a few month bac it worked fine, but used in reality the ChDir command doesn't appear to do so - and as the active workbook looks in its own location to check for the archived version (which) it didn't find, it all went wrong!

or, in summary: why the hell doesn't

chdir thisworkbook.path

do so?

main code below...

' Date & Archive (auto)
ChDir ThisWorkbook.Path ' changes to active workbook directory
If FileExists((Year(Now) - 1) & " " & ThisWorkbook.Name) Then GoTo byend ' checks if archive exists & goes to byend if true
If Month(Now) = 1 Then    ' checks month (ie jan(01)
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
SaveCopyAs (Year(Now) - 1) & " " & ThisWorkbook.Name
ThisWorkbook.ChangeFileAccess Mode:=xlReadWrite
' clear data bit
For Each sht In ActiveWorkbook.Worksheets
    sht.Activate
    Range("A5:i159").Select ' need to re-define area for each DIFFERENT workbook routine is used in
    Selection.clearcontents
    Next sht
    Application.ScreenUpdating = False
    Sheets("january").Select
' closing message
MsgBox "Happy New Year!" & Chr(13) & Chr(13) & "This file is ready for the current years' data input" & Chr(13) & Chr(13) & Chr(13) & _
"Last years' data has been archived to " _
& ThisWorkbook.Path & " : " & (Year(Now) - 1) & " " & ThisWorkbook.Name, vbInformation, title:="Data Archive"
Else: '
End If

thanks, Mike

 

[dk]

Hi,

Chdir will only change the current folder if the current folder is on the same drive as the one you're trying to change it to (I know you said that the files are on the same drive but that doesn't stop the current folder from being on a different drive). Anyway, does this work?

Sub ChangeFolder()
Dim oFS As Object

Set oFS = CreateObject("Scripting.FileSystemObject")

ChDrive oFS.GetDriveName(ThisWorkbook.Path)
ChDir ThisWorkbook.Path

Set oFS = Nothing
End Sub

A further question I'd have is why would you need to change the current folder? Why don't use just include the path of ThisWorkbook when you call the FileExists function e.g.

If FileExists(ThisWorkbook.Path & "\" & (Year(Now) - 1) & " " & ThisWorkbook.Name) Then GoTo byend ' checks if archive exists & goes to byend if true

 

[Richie(UK)]

Hi Mike,

Try changing the drive as well as the directory (ChDir works on the current drive). Something like this:

Sub AllChange()
    
    With ThisWorkbook
        ChDrive .Path
        ChDir .Path
    End With
    
End Sub

HTH

 

[Ivan F Moala]

Unless you are using UNC Convention....then you must use
an API to change Dir/Drive.