0 votes
in VBA by Expert (849 points)

Hello everyone

I have the following working code that downloads and inserts a picture into the worksheet

Sub Test()
Dim src         As String
Dim lfn         As String

src = "https://2ecffd01e1ab3e9383f0-07db7b9624bbdf022e3b5395236d5cf8.ssl.cf4.rackcdn.com/Product-190x190/0e72ef05-691d-4b3b-b978-a1bb9929e372.jpg"
lfn = ThisWorkbook.Path & "\Output.jpg"

If RequestDownload(src, lfn) Then
    Cells(1).Select
    ActiveSheet.Pictures.Insert lfn
End If
End Sub

Function RequestDownload(URL$, FILE$) As Boolean
Dim b()         As Byte
Dim f           As Integer

With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "GET", URL, False
    .setRequestHeader "DNT", "1"
    On Error GoTo Fin
    .send

    If .Status = 200 Then
        b = .responseBody
        f = FreeFile(1)
        Open FILE For Binary As #f
        Put #f, , b
        Close #f
        RequestDownload = True
    End If
Fin:
End With
End Function

I would like to insert directly the picture to the worksheet but when trying such a code

Sub InsertPicture()
    Dim link As String
    link = "https://2ecffd01e1ab3e9383f0-07db7b9624bbdf022e3b5395236d5cf8.ssl.cf4.rackcdn.com/Product-190x190/0e72ef05-691d-4b3b-b978-a1bb9929e372.jpg"
    ActiveSheet.Pictures.Insert (link)
End Sub

I encountered Credentials Window. It works also if I clicked 'Cancel' .. How to suppress such a window?

Is it possible to use API to make the code cancel the window and go on without interruption

Regards

by Skilled (360 points)
Are you using Excel 2016? If so, it may be a bug:

 

https://social.technet.microsoft.com/Forums/en-US/f033dbfb-a663-4f54-9edf-a836a42533aa/excel-2016-now-asking-for-windows-security-credentials-when-opening-a-csv-file-from-a-http-or-ftp

 

One interesting approach the OP tried was to try 12 times and then the problem went away. Of course, that is unacceptable, as it treats the symptoms and not the cause.

Cheers,

 

Mitch
by Expert (849 points)
Thanks a lot for reply Mitch. I am using 2019 version and I have a look at the link but couldn't each how to solve that.
by Skilled (360 points)
+1
I was going to suggest MousetoMacro, but my copy crashed Excel.

@Ryan, is there an update to MousetoMacro?
by Expert (849 points)
Are you experienced with using API ..? I need to lean about it but found it difficult for me
by Super Expert (2.9k points)
+1

What version of Mouse to Macro are you running, Mitch? And what version of Excel? 1.1.2 is the latest release.

I'm getting the same Credentials window using Office 365; that's very odd. I see this is an extension of the post here. If someone posts a solution there, please let us know Yasser!

 

by Expert (849 points)
Thanks a lot Ryan. Yes this post is related and sorry for not posting the link...

The solution I offered is OK but I am searching for alternatives
by Super Expert (2.9k points)
+1
It's no problem at all! I really just posted the link so I could find it later in case someone posts a solution:) This question perplexes me.
by Expert (849 points)
Thanks a lot for your interest and I hope to find a solution. The problem is that it may be downloaded without this interruption but it is interrupted when inserting directly.
by Skilled (360 points)
+1
Nope! I just copy, paste, pray and debug.
by Skilled (360 points)
Ryan, I am using version 1.1.2 as well. Perhaps the security window had some residual effect, since I didn't close and re-open before re-enabling MousetoMacro.

Please log in or register to answer this question.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...