Macro Saving to Wrong Locations

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
253
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,541
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Dec 20, 2010
Messages
253
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,541
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Dec 20, 2010
Messages
253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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-known Member
Joined
Feb 7, 2012
Messages
12,541
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Dec 20, 2010
Messages
253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 20, 2010
Messages
253
Office Version
  1. 365
Platform
  1. Windows
So you're saying add
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Maybe it can't find the specified directory.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,513
Messages
5,548,493
Members
410,840
Latest member
Kar3ousse
Top