Excel VBA - Reading excel file from OneDrive

Question

Dear all,

I have an issue with the below procedure as the file is placed on my OneDrive path:

Public Function FromSheetToRecordSet(SheetName As String, MyType As Byte, SQL As String) As ADODB.Recordset
    Dim MySql As String
    Dim SearchInField As String
    Dim SearchedValue As String

    Set Rst = New ADODB.Recordset
    Set Cnx = New ADODB.Connection
    Set Cmd = New ADODB.Command
    With Cnx
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With
    Set Cmd.ActiveConnection = Cnx
    Cmd.CommandType = adCmdText
    Select Case MyType
        Case 0
            MySql = "SELECT TOP 1 [MARKET_SOURCE] FROM [" & SheetName & "$]" & _
                " WHERE ((Len([MARKET_SOURCE])=0))"
        Case 1
            MySql = "SELECT [MARKET_SOURCE] FROM [" & SheetName & "$]" & _
                " WHERE ((Len([MARKET_SOURCE])>0)" & SQL
        Case 2
            MySql = "SELECT [File Name] FROM [" & SheetName & "$]" & _
                " WHERE [File Name] = '" & SQL & "'"
    End Select
    Cmd.CommandText = MySql
    Rst.CursorLocation = adUseClient
    Rst.CursorType = adOpenDynamic
    Rst.LockType = adLockOptimistic
    Rst.Open Cmd
    Set FromSheetToRecordSet = Rst
End Function

In this scenario here you find the below variables:

ThisWorkbook.Path = https://agency-my.sharepoint.com/personal/abc\_cde\_agency\_com/Documents

ThisWorkbook.Name = Calculator.xlsm

I think the issue is ThisWorkbook.Path value which is not recognized by Excel

Any idea to fix the issue?

Thanks

Answer 1

Thanks for your reply and for sharing your workaround attempt.

You're absolutely on the right track in identifying the issue with ThisWorkbook.Path returning a SharePoint URL.

However, it's important to note that: ThisWorkbook.Path is a read-only property you cannot assign a new value to it directly in VBA.

Instead of trying to overwrite it, you can work around this by manually constructing the correct local path in your code. For example:

Dim LocalPath As String

If InStr(ThisWorkbook.Path, "https://") > 0 Then

    LocalPath = "C:\Users\nsurname\OneDrive - Agency"

Else

    LocalPath = ThisWorkbook.Path

End If

.ConnectionString = "Data Source=" & LocalPath & "\" & ThisWorkbook.Name & ";" & _

                              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

This way, your code uses the correct local path when the workbook is opened from the cloud, and defaults to ThisWorkbook.Path when opened locally.

Let me know if you're having any issues integrating this into your full function

Best wishes,

Peter | Microsoft Community Support

Answer 2

Hi Luca,

Thanks for posting in the Microsoft community.

I understand you're encountering a "Not a valid file name" error when running your VBA function in Excel, and the file is stored on OneDrive.

This issue is often caused by how the file path is being interpreted by the VBA code, especially when working with cloud-based storage like OneDrive or SharePoint.

Here are two important steps to help you identify and resolve the issue:

1. Confirm the actual path being used by your VBA code

This step helps verify whether the file path is compatible with the OLEDB provider used in your VBA connection string.

In your code, you're using ThisWorkbook.Path to build the connection string. However, when the file is stored on OneDrive or SharePoint, ThisWorkbook.Path may return a web URL (e.g., https://agency-my.sharepoint.com/...) instead of a local file system path. This type of path is not supported by the Microsoft.ACE.OLEDB.12.0 provider.

To confirm this, add the following line to your code before opening the connection:

MsgBox "Workbook Path: " & ThisWorkbook.Path

If the message box shows a URL starting with https://, then the file is being accessed from the cloud and not from a local path. This is the reason for the "Not a valid file name" error.

2. Open the file from the local OneDrive sync folder

This ensures that ThisWorkbook.Path returns a valid local file system path that the OLEDB provider can recognize.

To resolve the issue, make sure you're opening the Excel file from your locally synced OneDrive folder. This is typically located at a path like:

C:\Users\

When you open the file from this location, ThisWorkbook.Path will return a proper local path (e.g., C:\Users\abc\OneDrive - Agency\Documents) that works with your connection string.

If you're unsure where your OneDrive files are syncing locally, you can:

• Right-click the OneDrive icon in the system tray.
• Select Settings > Account > Choose folders to see the sync location.
• Or open File Explorer and navigate to the OneDrive - [Your Organization] folder.

Once the file is opened from the local path, your VBA code should be able to connect without triggering the error.

Best wishes,

Peter | Microsoft Community Support

Answer 3

Thanks for your useful clarification on this topic.

I have placed my excel into my OneDrive directory below: C:\Users\nsurname\OneDrive - Agency

but when I open the excel file the value of ThisWorkbook.Path is still https://agency-my.sharepoint.com/personal/name\_surname\_agency\_com/Documents

Do I have to edit my above code with something like below?

If ThisWorkbook.Path = "https://agency-my.sharepoint.com/personal/name_surname_agency_com/Documents" Then

    ThisWorkbook.Path = "C:\Users\nsurname\OneDrive - Agency"

End If

Thanks