Excel VBA Button for creating hosts file to input via ZonedOut

I wrote about using ZonedOut to update Restricted Zone in Maximum Internet Safety...

Here's an Excel macro I developed which you can embed into a button in a worksheet and run it to import entries from the hosts file from MVPS.org. The imported entries will be saved into a text file which can then be imported via ZonedOut into IE's Restricted Sites zone.

The text file will be named as hostsyyyymmdd.txt and stored in your My Documents folder.

Private Sub CommandButton1_Click()
'get My Documents path
Dim usrpath As String
Dim filechk As String
Dim destfile As String
Dim datename As String
Dim k As Date
Dim a As String
Dim b As Long
Dim n As Single
Application.StatusBar = ""usrpath = Environ("USERPROFILE")filechk = Dir(usrpath & "\My Documents\hosts.txt")
If filechk = "" Then
MsgBox "Hosts file for importing not found. Please copy hosts.txt into My Documents"
GoTo stopproc
Else
Application.StatusBar = "Processing records from Hosts.txt..."
n = 0
Set Newbook = Workbooks.Add
Open usrpath & "\My Documents\hosts.txt" For Input As #1
Dim lineentry As String

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, lineentry ' Read line into variable.
If lineentry = "" Then GoTo skipline
If Left(lineentry, 1) = "#" Then GoTo skipline 'all other lines to be imported
a = Trim(Mid(lineentry, 10))
If Left(a, 9) = "localhost" Then GoTo skipline

b = InStr(a, "#")

If b = 0 Then
a = a
Else
a = Trim(Left(a, b - 1))
End If

'write a to workbook

n = n + 1
Newbook.Worksheets(1).Cells(n, 1).Value = a

skipline:

Loop

With Newbook

k = Date
datename = CStr(Year(k)) & CStr(Month(k)) & CStr(Day(k))
destfile = usrpath & "\My Documents\hosts" & datename & ".txt"
.SaveAs FileFormat:=xlTextWindows
.SaveAs Filename:=destfile
.Close

End With

Application.StatusBar = CStr(n) & " host entries created in " & destfile

Close #1

End If

stopproc:

End Sub

category:c4e1_scripts