Write on Workbooks after opening a new File

Question

I can't write in the workbooks after I open one file, even if I'm using the same code that worked on other occasions. I added a line to write a value before the new file is opened (which works, it writes a 5), and then the same line with a different value (10) after the file is opened, and it doesn't work, in either of the workbooks

The files where located in a OneDrive folder, and to discard any issue with that I moved them into a non synch folder, but nothing changed.

Option Explicit

Sub Generate()

    Dim WB_Main As Workbook, WB_Week As Workbook
    Dim ProgramPath As Variant
    Dim NumberofWeeks As Variant, WeekNumber As Variant, Period As Variant, Week_Ending As Variant
    Dim Daytype As Variant, DayDetails As Variant
    Dim FileName As String

    Set WB_Main = ThisWorkbook
    
    WB_Main.Sheets("Master").Range("AW1").Value = 5

    ProgramPath = Left(LocalFullName(ActiveWorkbook.FullName), InStrRev(LocalFullName(ActiveWorkbook.FullName), "\"))

    NumberofWeeks = WB_Main.Sheets("MASTER").Cells(5, 46).Value

'Test Single one

    Application.Calculation = xlManual
    ScreenAndAlertsOff False
    Application.EnableEvents = False

    WeekNumber = WB_Main.Sheets("MASTER").Cells(8, 49).Value
    FileName = Year(WB_Main.Sheets("MASTER").Cells(11, 49).Value) & "_" & Month(WB_Main.Sheets("MASTER").Cells(11, 49).Value) & "_" & WeekNumber & ".xls"

    FileCopy ProgramPath & "CALENDAR\Week_00.xls", ProgramPath & FileName

    Set WB_Week = Workbooks.Open(ProgramPath & FileName)

    WB_Main.Sheets("Master").Range("AW1").Value = 10
    WB_Week.Worksheets("F2808").Cells(8, 22).Value = 10 
    'WB_Main.Worksheets("MASTER").Cells(8, 49).Value

    Application.Calculation = xlAutomatic
    Application.EnableEvents = True
    ScreenAndAlertsOff False

End Sub

Private Function LocalFullName$(ByVal fullPath$)
    'Finds local path for a OneDrive file URL, using environment variables of OneDrive
    'Reference https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive
    'Authors: Philip Swannell 2019-01-14, MatChrupczalski 2019-05-19, Horoman 2020-03-29, P.G.Schild 2020-04-02

    Dim ii&
    Dim iPos&
    Dim oneDrivePath$
    Dim endFilePath$

    If Left(fullPath, 8) = "https://" Then 'Possibly a OneDrive URL
        If InStr(1, fullPath, "my.sharepoint.com") <> 0 Then 'Commercial OneDrive
            'For commercial OneDrive, path looks like "https://companyName-my.sharepoint.com/personal/userName_domain_com/Documents" & file.FullName)
            'Find "/Documents" in string and replace everything before the end with OneDrive local path
            iPos = InStr(1, fullPath, "/Documents") + Len("/Documents") 'find "/Documents" position in file URL
            endFilePath = Mid(fullPath, iPos) 'Get the ending file path without pointer in OneDrive. Include leading "/"
        Else 'Personal OneDrive
            'For personal OneDrive, path looks like "https://d.docs.live.net/d7bbaa#######1/" & file.FullName
            'We can get local file path by replacing "https.." up to the 4th slash, with the OneDrive local path obtained from registry
            iPos = 8 'Last slash in https://
            For ii = 1 To 2
                iPos = InStr(iPos + 1, fullPath, "/") 'find 4th slash
            Next ii
            endFilePath = Mid(fullPath, iPos) 'Get the ending file path without OneDrive root. Include leading "/"
        End If
        endFilePath = Replace(endFilePath, "/", Application.PathSeparator) 'Replace forward slashes with back slashes (URL type to Windows type)
        For ii = 1 To 3 'Loop to see if the tentative LocalWorkbookName is the name of a file that actually exists, if so return the name
            oneDrivePath = Environ(Choose(ii, "OneDriveCommercial", "OneDriveConsumer", "OneDrive")) 'Check possible local paths. "OneDrive" should be the last one
            If 0 < Len(oneDrivePath) Then
                LocalFullName = oneDrivePath & endFilePath
                Exit Function 'Success (i.e. found the correct Environ parameter)
            End If
        Next ii
        'Possibly raise an error here when attempt to convert to a local file name fails - e.g. for "shared with me" files
        LocalFullName = vbNullString
    Else
        LocalFullName = fullPath
    End If
End Function

"doesn't work" - what does it do instead of work? Are you getting an error or? — Tim Williams, Oct 04 '21 at 21:35
It was not writing the values. Then sometimes some where written, other not, sometimes all... And no error not anything. I end up figuring out that the One Drive synch process was affecting it. — Lander Garro, Oct 04 '21 at 23:38

score 0 · Answer 1 · answered Oct 04 '21 at 23:47

The issue was related to the OneDrive synch process. While doing background synch of some of the files of the project, the vba was not writing values, and it was affecting other functions like the pdf export. Solution been to wait till the synch process was finished or to disable it while running the code.

The biggest issue however was the lack of any error message or any indication, as it was simply getting stuck at some point of the code.

Write on Workbooks after opening a new File

1 Answers1