Trouble uploading a pdf file using vba

[shahin]

After going through this post https://stackoverflow.com/questions/60944569/unable-to-upload-a-text-file-using-vba, I thought to create a vba script capable of uploading a pdf file in a website. When I try the script below, I could notice that the script does upload the file if I choose yes when a pop up window shows up. I know this is probably not the way I should go on with. However, I could only think of this way at this moment.

This https://filebin.net/qvpwjwl85oonmya2 is where you can download the pdf file if needed.

This is the site link https://www.file.io/ for your consideration.

Sub UploadFile()
    Dim Http As New XMLHTTP60, sPostData$
    Dim wbO As Workbook

    Set wbO = Workbooks.Open("C:\Users\WCS\Desktop\TEST.pdf")
    sPostData = wbO.Sheets(1).Cells.Copy
    wbO.Close SaveChanges:=False
   
    With Http
        .Open "POST", "https://file.io"
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send ("text=" & sPostData)                                  
        Debug.Print .responseText
    End With
End Sub

How can I upload a pdf file using vba?

 

[Marc L]

As there is another thread opened with exactly the same website​

so as yet stated for the moment the best way is to use Curl like explained in this website …​

​

 

[shahin]

Thanks for your suggestion Marc L. The thing is I already got success using the way I tried above. All I wish to do now is get rid of that pop-up so that I don't need to choose the yes button manually for the script to accomplish the upload.

 

[YasserKhalil]

Hello my friend
I am still interested in this topic and here is a link with a solution for text files and images but I am not sure about PDF (I think Content-Type should be changed)

Upload file to file.io using POST method

I have found a link at SO that may make difference at this query Upload a Picture to file.io (HTTP Post) in VBA The code from this link Sub UploadFilesUsingVBAORIGINAL() 'this proc will ...

stackoverflow.com

 

[shahin]

Once again the script I pasted above is doing fine. I would only like to get rid of that pop-up so that I don't need to go for any manual intervention. Thanks.

 

[YasserKhalil]

What popup you are talking about ..? Can you post the whole code that you are talking about to test?

 

[shahin]

Yes, the one you see above is the whole code. Make sure to adjust the location of your file path before giving it a shot. That's it.

 

[shahin]

Here goes a demo https://filebin.net/tv6su6ucois2j4po for the clarity.

 

[YasserKhalil]

The message of success is FAKE (Believe me). To make sure take the URL and copy in the browser. This approach is not right regardless the blinking which can be disabled by disable the screenupdating and disable alerts ...

 

[shahin]

I believed in that success. Aw, what a pity!!!

 

[YasserKhalil]

The only solution after the use of CURL is the solution posted in the link I have put #4

 

[shahin]

Okay, I give up then. I raised this question thinking that the script would be simpler the way it looks when I try using python (only three-liner).

import requests

res = requests.post('https://file.io/',files={'file': open('demo.pdf','rb')})
print(res.content)

However, it seems this is not the case in vba.

 

[YasserKhalil]

Certainly python is easier .. Thanks for sharing
By the way is there a way to execute a python code from excel .. as a workaround?

 

[Chihiro]

By the way is there a way to execute a python code from excel .. as a workaround?

Yes. Use xlWings add-in. Note that you'll need to set up environment path etc and make sure that all dependencies are maintained.
https://www.xlwings.org/
Easiest method on Windows machine is to use Anaconda distribution. But working with Python via Excel is project of it's own.
I wouldn't do it for this purpose alone. It's much easier to work with CURL via VBA.

FYI - Depending on site and/or security level on API requests alone will not work.

Ex: You'll need authentication context etc.
For instance when uploading to SharePoint API.

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.runtime.client_request import ClientRequest
from office365.sharepoint.client_context import ClientContext
from office365.runtime.utilities.request_options import RequestOptions
import settings as st
import os.path as path

""" settings is file where constants are stored for access by other modules """
url = st.SPBASEURL
client_id = st.SPCLIENTID
client_secret = st.SPCLIENTSECRET
ctx_auth = AuthenticationContext(url)

def upload_binary_file(file_path, folder_url):
    if ctx_auth.acquire_token_for_app(client_id, client_secret):
        base_url = st.SPBASEURL
        file_name = path.basename(file_path)
        files_url = "{0}/_api/web/GetFolderByServerRelativeUrl('{1}')/Files/add(url='{2}', overwrite=true)"
        full_url = files_url.format(base_url, folder_url, file_name)
        request = ClientRequest(ctx_auth)
        options = RequestOptions(full_url)
        options.set_header('Accept', 'application/json;odata=verbose')
        options.set_header('content-type', 'application/x-www-urlencoded; charset=UTF-8')
        options.method = 'POST'
        with open(file_path, 'rb') as outfile:
            options.data = outfile
            data = request.execute_request_direct(options)
            if data.status_code == 200:
                base_site = data.json()['d']['Properties']['__deferred']['uri'].split("/sites")[0]
                relative_url = data.json()['d']['ServerRelativeUrl'].replace(' ', '%20')
                return base_site + relative_url
            else:
                return data
    else:
        return ctx_auth.get_last_error()

This is then called via other process...
Ex:

import filewithabovefunction as sp

sp.upload_binary_file(r'C:\download\SF_Data\{0}.csv'.format(dt.date.today().strftime("%Y-%m-%d")),"Shared Documents/SomeFolder")