Hi,
this is cross posted from here:
https://stackoverflow.com/questions/...xmlhttp-object
Can anyone help?
I am not sure it is possible...
Best,
Jacek
Hi,
this is cross posted from here:
https://stackoverflow.com/questions/...xmlhttp-object
Can anyone help?
I am not sure it is possible...
Best,
Jacek
Hi,
Thanks for showing the cross posting link.
Would you add the request in this forum too.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
Hi,
i added to stackoverflow also information as comment.
anyone can help?
Best,
Jacek
Sorry, I should have made that clearer.
As well as including a link to your cross post you need to post the request in detail on this forum. The idea is that when someone who may have a similar request searches the forum they may find your post.
Hi,
ok.
In order to get one Excel file (xlsx) from one drive shared public address and saved it to local drive. It is possible to loop through one drive shared folder and get all files as a list using VBA? It is possible to do something similar using Microsoft Graph API but from local Excel instance - I can do this?
And what I want to add is that not all from my users have Microsoft Account and have one drive as local drive downloaded. So only they have access to public online shared URL …
Best,Please Login or Register to view this content.
Jacek
Hi,
plese find my public shared onedrive folder with 2 workbooks:
https://1drv.ms/f/s!AsU5T4E6ZO7XgjQQ6A3i9X_7Iw2h
please help how to loop through this files.
I want to loop through them and if specific name is found, download it.
Best,
Jacek
Anyone?
Jacek
Up,
Best Wishes,
Jacek
Please upload the files to this forum so that we can test remotely first.
Hi Richard,
files are in public link to onedrive:
https://onedrive.live.com/?cid=d7ee6...BDoDeL1f_sjDaE
What do you want to do with them once you can loop through them?
Jaryszek - for everyone's convenience, please attach the workbooks directly here.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Ali, I think you have missed what the OP is asking for here. There isn’t a workbook to attach, he’s asking how to loop through files in one drive, that may or may not be excel files. It’s really a question on using the one drive api.
He’s provided a link to the folder to loop through - there’s nothing to attach
hi Kyle123,
agree.
I want to get list of files from one drive.
And choose for example Test2.xlsx, loop through one drive and download the choosen one (so name is a lookup string).
And if i set password to onedrive how to provide it.
I can also add that i have not onedrive on my local computer, only cloud onedrive.
Best,
Jacek
@Kyle - my misunderstanding. Thanks for the clarification.
Here's a starter for 10. You really need to parse the JSON rather than using string manipulation to get what you want - it's not reliable, I'll leave that to you:
Using the api call as described here: https://docs.microsoft.com/en-us/one...g-sharing-urlsPlease Login or Register to view this content.
The crux of it is getting the correct share link, in this case it's: https://onedrive.live.com/redir?resi...BDoDeL1f_sjDaE
Which you can get from following the redirection flow when clicking your link.
Using the guide in the API link, encode the share link as described (base64) and use that to get your data
The function above returns a 2-dimensional array with the first of the second dimensions being the file name, and the second the direct download link for the workbook.
Last edited by Kyle123; 12-31-2018 at 01:08 PM.
To answer your second question, if you want to add a password, you're going to need to do a lot more work. OneDrive uses OAuth2 authentication which you'll have to roll a client for in Excel - to do this, you'll need to understand the protocol and how refresh tokens work - this is not trivial and if it's the first time you've done it, you need to set aside days of reading to understand the process.
Honestly, if you need to secure this, you'd be better off looking for somewhere else to host your files, your own (cheap, shared) webserver with Basic Authentication and SSL would be easiest and trivial to set up.
Did this work for you? I see you’ve been back on the forum asking more questions.
Hi Kyle123,
i will let you know i have to do other macros as priority but i will come back to this topic.
It looks very nice Kyle123, you are master!
Best,
Jacek
hi Kyle123,
your code is awesome!
few questions,
1.how did you get the correct link?
The address is different than in my link.
2. They are using as you said json, not string.
i will set get request using string there and i will response as:
what can i do with this?Please Login or Register to view this content.
How to get file from json response?
Best,
Jacek
Last edited by jaryszek; 01-03-2019 at 02:22 AM.
My post explains how to get the correct link - which bit don’t you understand?
I’ve no idea what the second part of your question is, the Json returned looks nothing like the json you’ve posted. You wanted to loop through the files, my code gives you an array with the file name and direct download link. Just use the direct download link with the code you posted originally to download it
Your code is working like a charm, thank you!, but you are using string approach, not json.
Your code and getting downloaded links is great. But...
I will change my question, why to get json response?
What is the purpose of it with shareitem?
I was using json to get information only, not links but it seems that guys from microsoft are getting json and using it somehow to get links to download?
Best,
Jacek
My code still uses JSON, JSON is just a string - it’s also a way of organising data. The difference is, I’m using string manipulation to get the bit of data we want, based on where it is located in the string. This is unrealiable as the location may change. The easiest way of you parsing the data in VBA is probably using the code here: https://github.com/VBA-tools/VBA-JSO...ster/README.md
You then access the information you want through a hierarchy of dictionaries - it’s much more resilient.
Step through my code (or visit the link the code uses) and you’ll see the string/json that’s returned
Last edited by Kyle123; 01-03-2019 at 03:29 AM.
thank you Kyle123, good advice.
i know this parser from link, i was using it in my one of previous projects.
Very nice.
Best,
Jacek
hi Kyle123,
can you please help me to upload excel file to google drive using vba,
I am new with API.
thanking you.
Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks