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

 

[JLGWhiz]

You probably need to go to the Excel Options and check your default path for saving of each workbook. The CgDir would be ignored if your default path is different than the Directory you change to. In the worksheet window File>Options>Save>Default File Location. Or you could just include the path in the SaveAs statement for each type file instead of going through the directory change bit.

 

[rjbinney]

The default path is just \OneDrive

I wondered about that - and was just going to add the path into the macro. But it's so weird that it saves the file in the Dropbox location before any Dropbox commands, and it saves the Z:\drive file back in the original's locations - neither of which are the default path!

 

[JLGWhiz]

I have had problems with files saving to the default path when I thought they would save to another directory. If the file is opened in a directory it will SAVE to that directory, but if you use SAVEAS you need to specify the directory path or it will save to your default path.

 

[rjbinney]

Not to sound ungrateful, but I feel like you're not reading the issue:

• It is not saving the first copy in either the default directory, or the directory that the original file was opened from
• It is saving the external drive copy in the directory that the original file was opened from, and not the directory specified in the "Chdir" command

 

[JLGWhiz]

Well, the issue I saw was that the files were not being saved where you intended them to be saved, and since you were using the SaveAs method, the way to assure that they are saved where you want them is to use the FullName for the file, which would include the path. You're right, I did not want to get tangled up in the details for the different external drives because they were irrelevant to the root problem and resolution.

 

[rjbinney]

Just to throw this back into the universe...

Why would "ChDir" on the line before "ActiveWorkbook.SaveAs" not actually save the file in the intended directory?

 

[Fluff]

ChDir does not change the drive, so if the active directory is on the Z drive, then trying to change the directory to somewhere on the C drive won't work.
You need to change the drive & then the directory.

 

[rjbinney]

So you're saying add

ChDrive "Z"

above the "ChDir" line in that one subroutine?

That helps.

Doesn't explain why Item #2 is saving to the wrong directory?

 

[Fluff]

Maybe it can't find the specified directory.