Access VBA - To get data from Excel file stored in Sharepoint

Question

I have the below code working for me provided the "Model_data.xlsm" file is stored in my hard drive. Is it possible if Access can get the data from "model_data.xlsm" stored in Sharepoint?

Private Sub Update_manu_data_Click()

Dim strXls As String

 strXls = CurrentProject.Path & Chr(92) & "Model_data.xlsm"
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,   "Manufacturing_data", _
 strXls, True, "Combined!"

End Sub

Answer 1

Finally I did find a workaround for this issue.

I Created a private function in access to download the Excel file from SP and then used the Transferspread sheet function to retrieve the data into access table.

Below is the code i used to download the Excel file in SP using access Vba

 Private Sub Command2_Click()
  Dim Ex As Object
  Dim Acc As Object
  Dim strXls As String


    Set Ex = CreateObject("Excel.Application")
    Set Acc = Ex.Workbooks.Open("https://Sharepoint File link")

    Ex.Visible = False

    Acc.SaveAs "C:\Users\.......\test.xlsx"

    Acc.Close
    Ex.Quit

    strXls = CurrentProject.Path & Chr(92) & "C:\Users\.......\test.xlsx"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Tablename", _
    strXls, True, "Sheet(1)!"
 End Sub