Macro Saving to Wrong Locations

[rjbinney]

Over the years, I have built a pretty robust macro on a file to do lots of fun things - including saving some backups.

I just discovered that it isn't doing exactly what I think it should be doing - and it had been, to my knowledge for years. So I need some help testing my logic and maybe tracing the error.

When I start the Macro, here's what I want it to do:

1. Save the file ("Inventory Master.xlsm")
   The file's current directory is c:\users\sjobs\OneDrive\Inventory Docs)
2. Just for giggles, save a copy in the same directory, called "Inventory Backup for Cloud.xlsm"
3. Save a copy ("Inventory Backup.xlsm") on an unsyncable portion of my hard drive
4. Save macro-free copies:
   1. "Inventory for Devices (Macro-Free).xlsx" in my original location
   2. "Inventory for DTG (Macro-Free).xlsx" on my external drive (which isn't always hooked up)
   3. "Inventory for Dropbox.xlsx" to my Dropbox
5. Close the macro-free copies and open my original

Now, here's what it is doing. From timestamps, it looks like it went out of wack in April.

Here's what it's saving:

1. The current "Inventory Master.xlsm" file in the current directory, just fine
2. "Inventory Backup for Cloud.xlsm" in my Dropbox (supposed to be current OneDrive directory)
3. "Inventory Backup.xlsm" in the correct, unsyncable directory
4. The macro-frees are off kilter:
   1. "Inventory for Devices (Macro-Free).xlsx" in its proper (OneDrive) location
   2. "Inventory for DTG (Macro-Free).xlsx" in my current OneDrive directory (it's supposed to save to an external drive)
      (if the external drive is not connected, it does not save the "DTG" copy at all)
   3. "Inventory for Dropbox.xlsx" in my Dropbox, which is correct
5. The copies close and original opens.

In short, the duplicate copy is saving in the Dropbox, and the external version is being pushed into my OneDrive.

Help!

Sub SaveForDevicesFixed()

'Turn Off "Save As" Alerts
 Application.DisplayAlerts = False
 
'Save Current
    ActiveWorkbook.Save
    ActiveWorkbook.SaveAs Filename:="Inventory Backup for Cloud.xlsm", FileFormat:=52

'Save Local Backup
    ChDir "C:\Users\Sjobs\Documents"
    ActiveWorkbook.SaveAs Filename:="Inventory Backup.xlsm", FileFormat:=52

'Strip Button
    ActiveSheet.Buttons.Delete
    
'Save to OneDrive
    ChDir "C:\users\Sjobs\OneDrive\Inventory Docs"
    ActiveWorkbook.SaveAs Filename:="Inventory for Devices (Macro Free).xlsx", FileFormat:=51
    
'Save to Z
    If XExists("Z:\Personal\Docs to Go") Then
        ChDir "Z:\Personal\Docs to Go"
        ActiveWorkbook.SaveAs Filename:="Inventory for DTG.xlsx", FileFormat:=51
    End If

'Save to Dropbox
    ChDir "C:\Users\Sjobs\Dropbox\Music"
    ActiveWorkbook.SaveAs Filename:="Inventory for Dropbox.xlsx", FileFormat:=51
    
      
'Turn Alerts Back On
 Application.DisplayAlerts = True
 
'Close the Copies and Reopen the Master
   Dim ans As String
    ans = "Inventory for Dropbox.xlsx"
    Workbooks.Open Filename:="C:\Users\Sjobs\OneDrive\Inventory Docs\Inventory Master.xlsm"
    Workbooks(ans).Close
      
End Sub
      
        
'Check if Z is hooked up
      Private Function XExists(ByVal Path As String) As Boolean
          On Error Resume Next
             XExists = Dir(Path, vbDirectory) <> ""
      End Function

 

[rjbinney]

Well, it's supposed to be the current directory - it's just a direct "Save As". But it's saving it to my special Dropbox directory - which is only used several commands later (line 4-3 of my description in OP.).

Very confusing!

I commented out the ChDir commands and just added the whole path to the SaveAs line - obviously messier, but it forces it to work. Still ould love to figure out why it's not working the way (I think) it should...

 

[Fluff]

I always specify the full path, that way if it cannot find the path you get an error, rather than the file being saved to the wrong location.
IMO that is not "messier" it's sensible.

 

[rjbinney]

Fair enough. Still vexing!

I have the error messages turned off for this macro - since it's running "Save As", I turned it off so it wouldn't keep asking if I was sure I wanted to write over an existing file...

Appreciate the help,

 

[JLGWhiz]

Like I said, if you use the FullName when doing a save as, it goes to the right drive and directory without having to jump back and forth. If you were going to do something in those other directories, then doing the ChDrive and ChDir would be practical, but just for SaveAs, the Ch functions are added baggage.