Excel’s fullname property with OneDrive

by

I found a thread online which contained enough information to put something simple together to solve this. I actually implemented the solution in Ruby, but this is the VBA version:

Option Explicit

Private Function Local_Workbook_Name(ByRef wb As Workbook) As String

  Dim Ctr As Long
  Dim objShell As Object
  Dim UserProfilePath As String

  'Check if it looks like a OneDrive location
  If InStr(1, wb.FullName, "https://", vbTextCompare) > 0 Then

    'Replace forward slashes with back slashes
    Local_Workbook_Name = Replace(wb.FullName, "https://stackoverflow.com/", "\")

    'Get environment path using vbscript
    Set objShell = CreateObject("WScript.Shell")
    UserProfilePath = objShell.ExpandEnvironmentStrings("%UserProfile%")

      'Trim OneDrive designators
    For Ctr = 1 To 4
       Local_Workbook_Name = Mid(Local_Workbook_Name, InStr(Local_Workbook_Name, "\") + 1)
    Next

      'Construct the name
    Local_Workbook_Name = UserProfilePath & "\OneDrive\" & Local_Workbook_Name

  Else

    Local_Workbook_Name = wb.FullName

  End If

End Function

Private Sub testy()

  MsgBox ActiveWorkbook.FullName & vbCrLf & Local_Workbook_Name(ActiveWorkbook)

End Sub

More Related Contents:

  1. ThisWorkbook.FullName returns a URL after syncing with OneDrive. I want the file path on disk
  2. Save excel file multiple times with file names in another workbook.
  3. Read/Parse text file line by line in VBA
  4. How do I find the last column with data?
  5. Excel VBA: Answer gets “stuck”
  6. How to insert a picture into Excel at a specified cell position with VBA
  7. Save multiple sheets to .pdf
  8. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  9. Excel VBA autofilter all but three
  10. How can I create a calendar input in VBA Excel?
  11. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  12. Using Google Chrome in Selenium VBA (Installation Steps)
  13. Getting the actual usedrange
  14. Delete a row in Excel VBA
  15. Concatenate cell data into another data if values matches
  16. Iterating through populated rows
  17. How can I run VBA code each time a cell gets its value changed by a formula?
  18. How to convert 32 bit VBA code into 64 bit VBA code
  19. Using VBA to detect which decimal sign the computer is using
  20. Loop through each row of a range in Excel
  21. Outputting Excel rows to a series of text files
  22. How to create a range from two ranges in VBA?
  23. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  24. If string contains word from list, return value adjacent to list value
  25. every possible combination of the contents of 2 columns in excel
  26. excel vba freeze pane without select
  27. How to delete rows in Excel based on criteria using VBA?
  28. Converting a string representation of a constant into a constant?
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment