How to read a file from Sharepoint?

[m3user]

I have a text file on Sharepoint which I need to read using FileRead command. When I copy the link to file I get this path

https: //share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt?csf=1&web=1&e=eGALjF

Is there a way to transform the path so it can be opened by script? I tried replacing %20 with spaces and removing the part after ?csf at the end but it doesn't work.

[mikeyww]

The URL has a space after https:?

The following generally works.

Code: Select all

#Requires AutoHotkey v1.1.33
url := "http://mysite.com/test/test%20this.html"
Run % url

Your script might not have access to your directory or Web site.

[m3user]

I tried

Code: Select all

Path:=“https://share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt?csf=1&web=1&e=eGALjF”
FileRead, string, %Path%

and it doesn’t work. As said I tried to replace spaces and deleted the last part. Any idea?

[mikeyww]

I would use my script and just insert your URL to see whether you can open the page in your Web browser. Replace what is in quotation marks there.

[m3user]

This works (downloads the file)

Code: Select all

#Requires AutoHotkey v1.1.33
url := "https://share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt"
Run % url

This does not work (errorlevel=1, string is blank)

Code: Select all

url := "https://share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt"
FileRead, string, %url%
msgbox % errorlevel "`n`n" string

What might be the issue?

[mikeyww]

ErrorLevel is set to 0 if the load was successful. It is set to 1 if a problem occurred such as: 1) file does not exist; 2) file is locked or inaccessible; 3) the system lacks sufficient memory to load the file.

Others here may know more. I do not know whether FileRead works with a URL. As I mentioned, your script might also not have direct access to that location.

If you can download the file, then you can read the local copy after downloading.

You might want to search the forum for "Sharepoint" to see whether others have discussed the issue.

If you can map your Sharepoint drive to a Windows drive letter, you might be able to read the file.

[flyingDman]

This .pdf opens for me in my default .pdf app:

Code: Select all

run "https://xxxx.sharepoint.com/:b:/r/yyyy zzzz 101623/Reference Data/10. aaaaa 08.31.2023.pdf"

Like @m3user suggested, replace the %20 with a space and delete everything after the extension.
When run after only replacing each %20 with a space, it will open in my default browser.
For reference, I can open Sharepoint spreadsheets using:

Code: Select all

xl := ComObjActive("excel.application")
wrkbk := xl.Workbooks.Open("https://xxxx.sharepoint.com/folder1/filename.xlsx)

[m3user]

Thanks both.

I tried with a clean path (replaced %20 with spaces, deleted part after the extension) and it still doesn't work (I have proper access rights).

Code: Select all

url := "https://share.company.net/:u:/r/sites/MyFolder/Shared Documents/General/my file.txt"
FileRead, string, %url%
msgbox % errorlevel "`n`n" string

I also tried FileOpen() without success.

I see it can be done to read Excel, any other idea how to read a simple text file from Sharepoint, please?

[boiler]

You might as well cut out any trials with spaces. URLs never contain spaces, unlike Windows file names, which can contain them. If it starts with https://, then it won’t contain a space. And I don’t think FileRead works with URLs anyway, which is why you can Run the file, which can operate on a URL, yet the same URL doesn’t work with FileRead.

Use UrlDownloadToFile, then FileRead from the file you downloaded:

Code: Select all

#Requires AutoHotkey v1.1.33
url := "https://share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt"
string := UrlDownloadToVar(url)
MsgBox, % string

Or download it directly to a variable using a function like this:

Code: Select all

#Requires AutoHotkey v1.1.33
url := "https://share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt"
UrlDownloadToFile, % url, MyFile.txt
FileRead, string, MyFile.txt
MsgBox, % string

UrlDownloadToVar(url){
	hObject := ComObjCreate("WinHttp.WinHttpRequest.5.1")
	hObject.Open("GET", url)
	hObject.Send()
	return hObject.ResponseText
}

[flyingDman]

Code: Select all

run "https://xxxx.sharepoint.com/:b:/r/yyyy zzzz 101623/Reference Data/10. aaaaa 08.31.2023.pdf"

This code works, spaces and all....

I used the Excel code (with several spaces) on about 200 spreadsheets!

[boiler]

Well, officially, URLs do not contain spaces. Some browsers, and apparently the Run command (probably because it's just having the default browser open it), internally replace the spaces with %20. I would suspect that would not work for someone whose default browser did not do that behind-the-scenes replacing.

[m3user]

Thanks!

Using UrlDownloadToFile produces 403 FORBIDDEN error. Do I need to change some SharePoint settings?

Code: Select all

url := "https://share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt"  ;same results with spaces
UrlDownloadToFile, % url, MyFile.txt
FileRead, string, MyFile.txt
MsgBox, % string

Using UrlDownloadToVar function shows some html code instead of the file contents. What am I doing wrong?

Code: Select all

url := "https://share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt"  ;same results with spaces
string := UrlDownloadToVar(url)
MsgBox, % string

UrlDownloadToVar(url){
	hObject := ComObjCreate("WinHttp.WinHttpRequest.5.1")
	hObject.Open("GET", url)
	hObject.Send()
	return hObject.ResponseText
}

I also tried Word Com and it works!!!

Code: Select all

url := "https://share.company.net/:u:/r/sites/MyFolder/Shared%20Documents/General/my%20file.txt"  ;same results with spaces
w := ComObjCreate( "Word.Application" )
w.Documents.Open(url)      
msgbox % w.ActiveDocument.Range.Text

However the Com code is VERY slow. Is there a way to speed it up or use another Com for text files?
Any other idea?

[mikeyww]

The download to var worked here for a simple text file. You could check the actual file contents. I tested on a Web site but not Sharepoint.

[m3user]

It turns out that UrlDownloadToFile and UrlDownloadToVar don't work because of the permission issue. I'm not sure to set the Sharepoint Library to allow this...

As mentioned, I was able to read the file using Word COM. However it is very slow and requires Word to be installed.
Is there another COM available which could be used to read a simple text file (Explorer, something else?). Any idea is welcome, thanks!

[flyingDman]

This seems to work but looks "clunky". It might give someone else an idea how to do it right:

Code: Select all

url := "https://xxxx.sharepoint.com/:b:/r/yyyy zzzz 101623/Reference Data/test.txt"
Gui, Add, ActiveX, vWB, % url
while WB.busy
   Sleep, 50
msgbox % WB.Document.body.innerText

edit:
this might be what I was looking for:

Code: Select all

st := A_TickCount
url := "https://xxxx.sharepoint.com/:t:/r/yyyy zzzz 101623/Reference Data/test.txt"
wb := ComObjCreate("InternetExplorer.Application")
wb.Navigate(url)
while wb.busy
   Sleep, 50
msgbox % wb.Document.body.innerText "`n" A_TickCount - st
wb.quit()

[mikeyww]

It worked here with a slight adjustment.

Code: Select all

#Requires AutoHotkey v1.1.33
st  := A_TickCount
url := "https://xxxx.sharepoint.com/:t:/r/yyyy zzzz 101623/Reference Data/test.txt"
url := "http://mysite.com/test/test%20this.txt"
wb  := ComObjCreate("InternetExplorer.Application")
wb.Navigate(url)
While (wb.ReadyState != 4 || wb.Busy)
 Sleep 25
MsgBox % wb.Document.Body.InnerText "`n" A_TickCount - st
wb.Quit()

[flyingDman]

Are you telling me that it did not work w/o wb.ReadyState != 4 ? on a Sharepoint file ?

[mikeyww]

I did not test with Sharepoint, but I did see an error message on the second run otherwise.

[flyingDman]

What error message?

[mikeyww]

When I ran it again, there was no error, so perhaps I had done something wrong at my end.