Category Archives: Excel

excel VBA: Download URL file to local drive

Leave a reply

VBA code to download a file from URL address:

Sub DownloadUrlFile()
 
' Create an array to hold the response data.
Dim arrDownloadedBytes() As Byte
Dim WinHttpReq As WinHttpRequest
Dim strURL, strLocalPath, strLocalFileName As String
 
strURL = "https://www.xxxx.com/documents/FilenameToDownload"
 
strLocalPath = "C:\User\documents\"
strLocalFileName = "FilenameDownloaded"
 
Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
WinHttpReq.SetAutoLogonPolicy (0)
 
'In case you have V5.0 instead of v5.1 - use this line
'Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.0")
 
' create the HTTP Request
WinHttpReq.Open "GET", strURL, False
 
' Send the request
WinHttpReq.Send
 
' copy the response body to a local file
Open strLocalPath & strLocalFileName For Binary As #1
arrDownloadedBytes() = WinHttpReq.ResponseBody
Put #1, 1, arrDownloadedBytes()
Close
 
End Sub
 
 

Excel Tip: Convert formulas to values with a single drag

Leave a reply

Sometimes we need to convert data driven by formulas to values only. Below is a tip that you can do it with a single drag.

Step 1: Select the data range that you want to convert;
Step 2: Press CONTROL key and move your mouse to the frame of the data range and then press right key of your mouse;
Step 3: Move the data range to the destination and then release the right mouse key;
Step 4: Select ‘Copy Here as Values Only’ from the pop-up menu.

If you want to convert the data in the same location, you just need to move the data range to any new location and move back to the same location before releasing the right mouse key in Step 3.