Download a File from the Internet

 To download a file from the Internet, one option is to use the URLDownloadToFile API, which looks something like this.

#If VBA7 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
                                (ByVal pCaller As Long, _
                                 ByVal szURL As String, _
                                 ByVal szFileName As String, _
                                 ByVal dwReserved As Long, _
                                 ByVal lpfnCB As Long) As Long
#Else
  Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
                        (ByVal pCaller As Long, _
                         ByVal szURL As String, _
                         ByVal szFileName As String, _
                         ByVal dwReserved As Long, _
                         ByVal lpfnCB As Long) As Long
#End If

You could then use the above in a function like this.

lngResult = URLDownloadFile(0,URL,Filename,0,0) 

However, if you're like me and don't want to use API calls, you can also achieve the above goal by using a HTTPRequest class. 

Here's an example VBA subroutine to download a file from the Internet.

Public Sub DownloadFile(URL As String)
'thedbguy@gmail.com
'3/5/2021
'used to download a file from the Internet
'assumes filename is at the end of the URL
'file is saved in the current project folder
'usage: DownloadFile "http://www.accessmvp.com/thedbguy/img/shrek.jpg"

Dim objHTTP As Object
Dim FileByte() As Byte
Dim strFile As String
Dim intFile As Integer

intFile = FreeFile()
strFile = Mid(URL, InStrRev(URL, "/") + 1)

Set objHTTP = CreateObject("Microsoft.XMLHTTP")

With objHTTP
    .Open "GET", URL, False
    .Send
    If .Status = 200 Then
        FileByte = .responseBody
    End If
End With

Open CurrentProject.Path & "\" & strFile For Binary Lock Read Write As #intFile
    Put #intFile, , FileByte
Close #intFile

Set objHTTP = Nothing

End Sub

The above code does not include any error handlers, so you will have to add that part yourself. Also, the above code was not fully tested on all possible file types. It worked well for me when downloading image and PDF files though. Please let me know if you find a file type that this code was not able to handle.

As usual, thank you for reading and please let me know if you find this information useful.

Good luck with your project.

List Files in a Folder and its Subfolders

While trying to answer a question in the MSDN Forums, I realized although there are already a lot of examples on how to list all the files in a folder or directory, I thought this was challenging enough for me to give it a try.

The original poster's request was to allow the user to search the folder for files matching a keyword and a list of specific file extensions. Also, the search should include all files inside subfolders.

Many of the examples I have seen use a loop to go through the folder structure and list all the files in them. Since I am a bit fascinated with recursion, I thought I would give it a try using a different approach. As a result of my attempt, I have created this demo.

Click on the above image to download the demo.

To use this demo, the user selects a starting folder path and enter an optional search keyword or list of file extensions to search. The search keyword is used to match the file names, while the list of file extensions will return only the files with matching file extensions.

The demo presents the result of the search using a Listbox. Double-clicking on an item in the Listbox will attempt to open the file for viewing. This version of the demo (v1.0) currently uses a Value List in the Row Source of the Listbox, which limits the number of files allowed to be displayed. The demo can be modified to store the file names in a table to allow the Listbox to display more items. I might do this in future versions of the demo.

In the meantime, I hope you find this demo useful, or at least, find the approach a little interesting.

As usual, I would appreciate any comments or feedback regarding this demo.

Cheers!

How to check if ACCDE?

There was a question at MSDN Forums the other day on how to determine if an Access database file was compiled to an ACCDE. I found this question somewhat interesting, because I never had a need to determine whether a database was a true ACCDE file or not.

When I deploy a compiled database, I often rename the file extension to ACCDR to force Access into Runtime Mode. I use the following code to check if the database is running in Runtime Mode and exit the application if it is not.

SysCmd(acSysCmdRuntime)

However, I could not find an equivalent SysCmd constant to check for ACCDE, and I am not sure if there is a property for it as well. So, I came up with the following function, which seems to do the job.

Public Function IsACCDE() As Boolean
On Error GoTo errHandler

Application.VBE.CodePanes(1).Show
IsACCDE = False

errExit:
    Exit Function

errHandler:
    IsACCDE = True
    Resume errExit
    
End Function

I am not sure if it is the best way to perform the ACCDE check, but I just thought I would share it and see if you can tell me if there is a better way. Thank you!

How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents of a text file into your database. This task may seem challenging at first because it involves an external file, but it doesn't have to be hard.

The OPEN Statement

There are several methods to read a text file into an Access database. VBA has a built-in OPEN statement, which enables input or output access to an external file. Here's an example VBA code to initialize a text file for input:

Open "C:\FolderName\FileName.txt" For Input As #1

The above code creates an object (#1) with a reference to the text file, which then allows its contents to be read in code. For example, the following code reads a line of text from the file into a variable:

Line Input #1, VariableName

After we are done reading its contents, we need to close the connection to the file with the following:

Close #1

Here's an example code snippet to read an entire text file into memory:

Dim intFile As Integer
Dim strFile As String
Dim strText As String
Dim strInput As String

intFile = FreeFile()

strFile = "C:\FolderName\FileName.txt"

Open strFile For Input As #intFile

Do While Not EOF(#intFile)
     Line Input #intfile, strInput
     strText = strText & strInput
Loop

Close #intFile

Please notice the use of the FreeFile() function to retrieve the next available file number rather than assigning a specific file number with the OPEN statement. It avoids possible mistakes of referring to the wrong file in case you are trying to manipulate multiple files at the same time.

The File System Object

My preferred method to read a text file into Access is to use FSO (File System Object). It adds an external reference (overhead) to the application, but I think the process will run a bit faster, and the code will also be a bit cleaner.


For instance, compare the following function, using FSO, to the one above, using the OPEN statement:

Public Function ReadTextFile(strFile As String) As String
'3/10/2016
'http://accessmvp.com/thedbguy

Dim fso As Object
Dim objFile As Object

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(strFile) Then
     Set objFile = fso.OpenTextFile(strFile, 1) '1=ForReading, 8=ForAppending
     ReadTextFile = objFile.ReadAll
End If

Set objFile = Nothing
Set fso = Nothing

End Function

The above function uses "late binding," so a reference to the File System Object is not necessary. However, you can use "early binding" if you prefer.


I hope you find the above methods useful. Please do not hesitate to let me know which method you prefer and why (or why not the other). Cheers!