Option Explicit
Dim WordApp As Word.Application
Dim ExcelApp As Excel.Application
Private Sub Form_Load()
lstStatus.Clear
Inet1.Protocol = icHTTP
End Sub
Private Sub cmdGet_Click()
ProcessFile
End Sub
Private Sub ProcessFile()
cmdGet.Enabled = False
DoEvents
Dim strURL As String
Dim bData() As Byte ' Data variable
Dim intFile As Integer ' FreeFile variable
strURL = "http://www.ssa.gov/foia/highgroupdownloads/HG0903.doc"
intFile = FreeFile()
' The result of the OpenURL method goes into the Byte
' array, and the Byte array is then saved to disk.
lstStatus.AddItem "Opening Social Security Website . . ."
bData() = Inet1.OpenURL(strURL, icByteArray)
lstStatus.AddItem "Downloading SSN document . . ."
Open App.Path & "\ssn.doc" For Binary Access Write As #intFile
Put #intFile, , bData()
Close #intFile
lstStatus.AddItem "Converting SSN document to text file . . ."
Set WordApp = New Word.Application
With WordApp
.WindowState = wdWindowStateMinimize
.Visible = False
.DisplayAlerts = wdAlertsNone 'hide word alerts msgboxes
.Documents.Open App.Path & "\SSN.doc"
.ActiveDocument.SaveAs FileName:=App.Path & "\SSN.txt", _
FileFormat:=wdFormatText, _
LockComments:=False, _
Password:="", _
AddToRecentFiles:=False, _
WritePassword:="", _
ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, _
SaveFormsData:=False, _
SaveAsAOCELetter:=False
.Documents.Close False
End With
WordApp.Application.Quit False
Set WordApp = Nothing
lstStatus.AddItem "Reading SSN text file into array . . ."
Dim strData As String
Dim intData(1000, 1) As Integer
Dim i As Integer
intFile = FreeFile()
i = 0
Open App.Path & "\SSN.txt" For Input As #intFile
Do While Not EOF(intFile)
'get group
Input #intFile, strData
intData(i, 0) = Val(strData)
'exit loop if we run out of data but the file still
' has "stuff" in it
If intData(i, 0) = 0 Then Exit Do
'get area
Input #intFile, strData
intData(i, 1) = Val(strData)
'increment counter
i = i + 1
Loop
Close #intFile
lstStatus.AddItem "Saving SSN text file as CSV file . . ."
intFile = FreeFile()
Open App.Path & "\SSN.txt" For Output As #intFile
Dim j As Integer
For j = 0 To i - 1
Write #intFile, intData(j, 0), intData(j, 1)
Next j
Close #intFile
lstStatus.AddItem "Reading SSN CSV file into Excel . . ."
Set ExcelApp = New Excel.Application
With ExcelApp
.WindowState = xlMinimized
.Visible = False
'hide excel alerts msgboxes
.DisplayAlerts = False
.Workbooks.OpenText FileName:=App.Path & "\SSN.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1))
lstStatus.AddItem "Creating new HG.dbf file . . ."
.ActiveSheet.Range("A1:B1050").Select
.Selection.Sort Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.ActiveSheet.Range("A1").Select
.Selection.EntireRow.Insert
.ActiveCell.FormulaR1C1 = "Area"
.ActiveSheet.Range("B1").Select
.ActiveCell.FormulaR1C1 = "Group"
.ActiveSheet.Range("A1").Select
.ActiveWorkbook.SaveAs FileName:=App.Path & "\HG.dbf", _
FileFormat:=xlDBF4, _
CreateBackup:=False
.ActiveWorkbook.Saved = True
.ActiveWorkbook.Close
.Workbooks.Close
End With
ExcelApp.Application.Quit
Set ExcelApp = Nothing
lstStatus.AddItem "Process complete"
End Sub