1

I'm trying to access a .csv file stored in my company's sharepoint from a VBA function in an Excel file stored on my laptop locally:

FilePath = "https://[company].sharepoint.com/Shared%20Documents/[folder]/testCSV.csv"
Open FilePath For Input As #1

the function i've written works fine if the .csv file is saved locally in my laptop but gives this error when trying through SharePoint :

Run-Time Error '52':
Bad file name or number

The funny thing is that I can actually open the same .csv file as an Excel workbook with this code :

Dim FilePath As String
FilePath = "https://[company].sharepoint.com/Shared%20Documents/[folder]/testCSV.csv"
Dim wb As Workbook
Set wb = Workbooks.Open(FilePath, , , 2)

I've tryied as well to open a .txt file with this code :

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("https://[company].sharepoint.com/Shared%20Documents/[folder]/testfile.txt", ForAppending)
f.Write "Hello world!"
f.Close

and again works fine if the file is saved on my computer but not if it's saved on sharepoint

Any ideas on how to solve the issue ?

Thanks in advance

Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
cafu23
  • 17
  • 5
  • If it works it's because it's downloaded: FileSystemObject works with files that are within the system, if it works like your code (which I haven't personally been able to work with) you may need to [download the file first](https://stackoverflow.com/questions/42419486/how-to-download-a-file-from-sharepoint-with-vba). If you don't need it afterwards, just [kill it](https://analystcave.com/vba-reference-functions/vba-file-functions/vba-kill-function/) – Sgdva Jan 13 '22 at 16:25
  • Workbook objects can open files with a URL but Filesystem object can't . A solution is [here](https://stackoverflow.com/questions/46346567/thisworkbook-fullname-returns-a-url-after-syncing-with-onedrive-i-want-the-file/67582367#67582367) which works by accessing the registry keys. – CDP1802 Jan 13 '22 at 16:40
  • 1
    Thank you for your help - downloading the file first did actually solved my problem. I still have an opening .db (sqlite) file problem but that will be for another post. Thanks both for your time ! @Sgdva – cafu23 Jan 20 '22 at 10:02
  • 1
    Hi Cafu23, We don't add solved in title on SO instead please add an answer with your solution and mark it (accept it) as your solution see [tour] for more info. – Petter Friberg Jan 20 '22 at 10:07

1 Answers1

0

Downloading the file first did solve that particular error :

'functions for downloading
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Function DownloadFileFromWeb(strURL As String, strSavePath As String) As Long 'strSavePath includes filename
DownloadFileFromWeb = URLDownloadToFile(0, strURL, strSavePath, 0, 0)
End Function


DownloadFileFromWeb(webPath, localPath)

Hope this can be helpful

cafu23
  • 17
  • 5