0

I have a workbook that references (with vba, no formulas) another workbook that is stored remotely on a server.

I hope no problems opening the workbook, using my references and closing the workbook all without the user knowing apart from one major flaw. When I use Workbooks.Open(MyPath) it shows a "Downloading" box which reveals the location of this file. I do not want the users to see this.

1

Is there a way to suppress this box?

I have tried application.screenupdating = False, application.displayalerts = False

Can't gather much from looking round the net, similar questions on forums go unanswered, but hours an hours of work hinges on this really as if the prompt can't be suppressed I need to scrap the whole thing.

Community
  • 1
  • 1
AOI
  • 1
  • If you need to open the remote workbook for reading only, and the data in that worbook isn't volatile, then you may just make a local copy of the file using FSO, and then open it. – omegastripes May 04 '18 at 16:55

1 Answers1

2

Here are some possibilities for you to consider and/or try.

The fourth option may be easiest/best:

  1. You could get the window handle for the Downloading dialog using a Windows API, perhaps with the GetWindow or GetTopWindow function ad then hide or otherwise manipulate the window as necessary. There are lots of samples online including this post about using Windows API functions with Office.

  2. It may be possible to programmatically map a drive letter (ie., X:) to the URL and open the file using the drive letter. More info here.

  3. Maybe you can hotlink via a local file by creating a "dummy" page that basically just redirects to the file on the web.

    Here is an example using a random downloadable PDF cookbook online. Copy the following into a Notepad and save it as test.html:

    <html>
<body>
<iframe 
  src="https://thestonesoup.com/blog/images/free_stonesoup_ecookbook.pdf" 
  frameborder="0" width="800" height="2500" scrolling="no">
</iframe>
</body>
</html>

    Double-clicking the file will open it in your browser, and will automatically download (or prompt to download) the PDF, without giving any indication where the file is actually located. This may or may not work with your case. More info here.

  4. The only true way to prevent a dialog produced by the Workbooks.Open method is to not use that method. Perhaps it would be best to download the file programmatically and then open the copy locally (which is what Excel's already doing for you anyhow).

    You could use a WinHTTP File Download procedure like this:

     Sub downloadFile(fileURL As String, saveFilePath As String)
    Dim WinHttpReq As Object, oStream
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", fileURL, False
    WinHttpReq.send
    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write WinHttpReq.responseBody
        oStream.SaveToFile saveFilePath, 1 ' 1 = no overwrite, 2 = overwrite
        oStream.Close
        Debug.Print "Saved: " & saveFilePath
    Else
        Debug.Print "Couldn't download: " & fileURL
    End If
End Sub

Example:

This downloads the PDF to drive c:\:

    downloadFile "https://thestonesoup.com/blog/images/free_stonesoup_ecookbook.pdf", "c:\temp.pdf"

...the use Shell to open the file, something like:

    Shell ("excel.exe c:\yourFile.xlsx")

An important note about securing information within Excel:

You can't.

There is no guaranteed way of securing data or code within an Office document. Even Excel's VBA Project password protection and encryption can be defeated fairly easily by even a novice user, using only Google and a little bit of determination (potentially in under 2 minutes).

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Thanks for the detailed answer, my remote file is on a server in our network so method 4 seems to fail. The path is like `\\ServerName\Folder\File.xlsm` rather than a HTTP address. – AOI May 08 '18 at 11:17
  • So you're not downloading the file per se, you're simply "opening it". – ashleedawg Jun 14 '18 at 07:47