Results 1 to 10 of 10

Thread: Why won't Excel process end!? ** RESOLVED **

Threaded View

  1. #1

    Thread Starter
    Fanatic Member Armbruster's Avatar
    Join Date
    Sep 2002
    Location
    Maryland Heights, MO
    Posts
    857

    Angry Why won't Excel process end!? ** RESOLVED **

    I wrote a program to check if a SSN is valid based on high-group listings from the Social Security Administration. I am too lazy to create a needed database file from the info manually, so I created a program to download the information for me and create the file.

    The problem is that Excel is still running as a process when the code execution stops. The Excel process doesn't stop until I close the program. What am I doing wrong!!

    VB Code:
    1. Option Explicit
    2.  
    3. Dim WordApp As Word.Application
    4. Dim ExcelApp As Excel.Application
    5.  
    6. Private Sub Form_Load()
    7.     lstStatus.Clear
    8.     Inet1.Protocol = icHTTP
    9. End Sub
    10. Private Sub cmdGet_Click()
    11.     ProcessFile
    12. End Sub
    13. Private Sub ProcessFile()
    14.     cmdGet.Enabled = False
    15.     DoEvents
    16.     Dim strURL As String
    17.     Dim bData() As Byte      ' Data variable
    18.     Dim intFile As Integer   ' FreeFile variable
    19.     strURL = "http://www.ssa.gov/foia/highgroupdownloads/HG0903.doc"
    20.     intFile = FreeFile()
    21.    
    22.     ' The result of the OpenURL method goes into the Byte
    23.     ' array, and the Byte array is then saved to disk.
    24.     lstStatus.AddItem "Opening Social Security Website . . ."
    25.     bData() = Inet1.OpenURL(strURL, icByteArray)
    26.     lstStatus.AddItem "Downloading SSN document . . ."
    27.     Open App.Path & "\ssn.doc" For Binary Access Write As #intFile
    28.     Put #intFile, , bData()
    29.     Close #intFile
    30.    
    31.     lstStatus.AddItem "Converting SSN document to text file . . ."
    32.     Set WordApp = New Word.Application
    33.     With WordApp
    34.         .WindowState = wdWindowStateMinimize
    35.         .Visible = False
    36.         .DisplayAlerts = wdAlertsNone       'hide word alerts msgboxes
    37.         .Documents.Open App.Path & "\SSN.doc"
    38.         .ActiveDocument.SaveAs FileName:=App.Path & "\SSN.txt", _
    39.             FileFormat:=wdFormatText, _
    40.             LockComments:=False, _
    41.             Password:="", _
    42.             AddToRecentFiles:=False, _
    43.             WritePassword:="", _
    44.             ReadOnlyRecommended:=False, _
    45.             EmbedTrueTypeFonts:=False, _
    46.             SaveNativePictureFormat:=False, _
    47.             SaveFormsData:=False, _
    48.             SaveAsAOCELetter:=False
    49.         .Documents.Close False
    50.     End With
    51.     WordApp.Application.Quit False
    52.     Set WordApp = Nothing
    53.    
    54.     lstStatus.AddItem "Reading SSN text file into array . . ."
    55.     Dim strData As String
    56.     Dim intData(1000, 1) As Integer
    57.     Dim i As Integer
    58.  
    59.     intFile = FreeFile()
    60.     i = 0
    61.  
    62.     Open App.Path & "\SSN.txt" For Input As #intFile
    63.     Do While Not EOF(intFile)
    64.         'get group
    65.         Input #intFile, strData
    66.         intData(i, 0) = Val(strData)
    67.         'exit loop if we run out of data but the file still
    68.         '  has "stuff" in it
    69.         If intData(i, 0) = 0 Then Exit Do
    70.         'get area
    71.         Input #intFile, strData
    72.         intData(i, 1) = Val(strData)
    73.         'increment counter
    74.         i = i + 1
    75.     Loop
    76.     Close #intFile
    77.    
    78.     lstStatus.AddItem "Saving SSN text file as CSV file . . ."
    79.     intFile = FreeFile()
    80.  
    81.     Open App.Path & "\SSN.txt" For Output As #intFile
    82.     Dim j As Integer
    83.     For j = 0 To i - 1
    84.         Write #intFile, intData(j, 0), intData(j, 1)
    85.     Next j
    86.     Close #intFile
    87.    
    88.     lstStatus.AddItem "Reading SSN CSV file into Excel . . ."
    89.     Set ExcelApp = New Excel.Application
    90.     With ExcelApp
    91.         .WindowState = xlMinimized
    92.         .Visible = False
    93.         'hide excel alerts msgboxes
    94.         .DisplayAlerts = False
    95.         .Workbooks.OpenText FileName:=App.Path & "\SSN.txt", _
    96.             Origin:=xlWindows, _
    97.             StartRow:=1, _
    98.             DataType:=xlDelimited, _
    99.             TextQualifier:=xlDoubleQuote, _
    100.             ConsecutiveDelimiter:=False, _
    101.             Tab:=False, _
    102.             Semicolon:=False, _
    103.             Comma:=True, _
    104.             Space:=False, _
    105.             Other:=False, _
    106.             FieldInfo:=Array(Array(1, 1), Array(2, 1))
    107.     lstStatus.AddItem "Creating new HG.dbf file . . ."
    108.         .ActiveSheet.Range("A1:B1050").Select
    109.         .Selection.Sort Key1:=Range("A1"), _
    110.             Order1:=xlAscending, _
    111.             Header:=xlGuess, _
    112.             OrderCustom:=1, _
    113.             MatchCase:=False, _
    114.             Orientation:=xlTopToBottom
    115.         .ActiveSheet.Range("A1").Select
    116.         .Selection.EntireRow.Insert
    117.         .ActiveCell.FormulaR1C1 = "Area"
    118.         .ActiveSheet.Range("B1").Select
    119.         .ActiveCell.FormulaR1C1 = "Group"
    120.         .ActiveSheet.Range("A1").Select
    121.         .ActiveWorkbook.SaveAs FileName:=App.Path & "\HG.dbf", _
    122.             FileFormat:=xlDBF4, _
    123.             CreateBackup:=False
    124.         .ActiveWorkbook.Saved = True
    125.         .ActiveWorkbook.Close
    126.         .Workbooks.Close
    127.     End With
    128.     ExcelApp.Application.Quit
    129.     Set ExcelApp = Nothing
    130.     lstStatus.AddItem "Process complete"
    131. End Sub
    Last edited by Armbruster; Oct 10th, 2003 at 01:14 PM.
    "Look! Up in the sky! It's a bird! It's a plane! It's Diaper-Head Boy! (there by my name!) Yes, Diaper-Head Boy, who disguised as my son, Seth, fights a never-ending battle for truth, justice and terrorizing my house!

    Resistance is futile, you will be compiled . . . Please!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width