1

I have a macro-enabled workbook, that has a macro that should save a copy of the file to a new location. I store the name of the original file as a string before saving to the new location, so that I can still reference the original file in the original location. The file still exists there, and the filename string works when I open the file using it, but it doesn't work when I try to do anything else with the string.

The original location is a folder called Source, and the file is copied to the folder called Destination.

The important problem here, is that the folders are located on SharePoint. If the folders are on a local machine it's not an issue.

https://[company].sharepoint.com/sites/[team]/Shared Documents/General/Source/oldVersion.xlsm

https://[company].sharepoint.com/sites/[team]/Shared Documents/General/Destination/newVersion.xlsm

Sub testMove()
    
    FullName = Application.ActiveWorkbook.FullName
    
    ActiveWorkbook.SaveAs "https://[company].sharepoint.com/sites/" _
& "[team]/Shared Documents/General/Destination/newVersion.xlsm", xlOpenXMLWorkbookMacroEnabled
    
    'SetAttr FullName, vbNormal
    Workbooks.Open FullName

End Sub

The above code runs just fine, but when you uncomment the setAttr it gets a file not found error.

The goal is to be able to close and/or delete the file in the original location, so SaveCopyAs doesn't quite work for this purpose.

braX
  • 11,506
  • 5
  • 20
  • 33
Connor Baush
  • 13
  • 6
  • The closing double quotes are in the wrong place. They should be right after `.xlsm`. But I would suggest you have a look at [SaveCopyAs](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.savecopyas). – VBasic2008 Feb 10 '22 at 15:41
  • Good catch, but that doesn't actually solve the problem, unfortunately. – Connor Baush Feb 10 '22 at 15:41
  • You need to explain why *that doesn't actually solve the problem* for us to help you. – VBasic2008 Feb 10 '22 at 15:43
  • Because I fixed it and reran it and the same file not found error occurred. – Connor Baush Feb 10 '22 at 15:43
  • Why don't you try `ActiveWorkbook.SaveCopyAs "C:\Users\[username]\OneDrive\Documents\Destination\newVersion.xlsm"`. You'll end up with your original file still open and you'll have a copy saved. – VBasic2008 Feb 10 '22 at 15:45
  • That does work well, but the goal is to be able to close and/or delete the original file. I'll edit the post to add that detail. – Connor Baush Feb 10 '22 at 15:47
  • Is the code located in a third file, or in the old file? – VBasic2008 Feb 10 '22 at 15:50
  • If the code is in the old file, the `SaveCopyAs` idea is still valid: you open the new (`SaveCopyAs`) file and close the old file. You cannot delete it though. – VBasic2008 Feb 10 '22 at 15:56
  • 1
    The code is in both files. So, when the SaveAs is performed, the code continues running from the new file, leaving the old file behind. Then, from the new file, the code is able to open the old file. I've tried putting the code in a third reference file, but that doesn't change anything. – Connor Baush Feb 10 '22 at 16:16
  • Do you maybe have any non-standard letters (not `A-Za-Z`) in the new file name? – VBasic2008 Feb 10 '22 at 16:19
  • The code exactly as I posted it is exactly what I'm using in my own debugging environment, except it has my actual windows username. I have a folder called "Source" and a folder called "Destination" and the filenames are oldversion and newversion. – Connor Baush Feb 10 '22 at 16:35
  • Why are you using `SetAttr FullName, vbNormal`? – VBasic2008 Feb 10 '22 at 16:37
  • Just for debugging. I could also try Kill FullName, and I get the same error. It shouldn't really matter what I'm trying to do using the filename. The problem is that the only thing that seems to actually work using it is Workbooks.Open. – Connor Baush Feb 10 '22 at 16:48
  • 1
    I noticed that you're working on onedrive folders, it maybe worth to give a read to [this](https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive) – Sgdva Feb 10 '22 at 17:04
  • Yes, I think using OneDrive/SharePoint is ultimately the cause of my issue. – Connor Baush Feb 10 '22 at 17:23
  • Edited the post to accurately reflect that this is on sharepoint. – Connor Baush Feb 10 '22 at 17:52

1 Answers1

0

SaveAs and Delete

  • This works for me. Your feedback is most welcome.
Option Explicit

Sub TestMove()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim sFilePath As String: sFilePath = wb.FullName
    
    Dim dFilePath As String: dFilePath = "C:\Users\[username]\OneDrive\" _
        & "Documents\Destination\newVersion.xlsm"

    Application.DisplayAlerts = False ' overwrite without confirmation
    wb.SaveAs dFilePath, xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True
    'Debug.Print wb.Name ' to prove that it has a new name now
    
    If Len(Dir(sFilePath)) > 0 Then
        SetAttr sFilePath, vbNormal
        Workbooks.Open sFilePath
        'Or:
        'Kill sFilePath
    Else
        MsgBox "File not found.", vbCritical
    End If
    
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • I still get an error with sFilePath. I think it's because of OneDrive/SharePoint. sFilePath ends up being this https://d.docs.live.net/ab4b87c9cbfd69c6/Documents/Source/oldversion.xlsm – Connor Baush Feb 10 '22 at 17:16