Results 1 to 3 of 3

OpenText function in macro hangs unless stepping through code in debug

Threaded View

Part-Time Coder OpenText function in macro... 10-29-2009, 05:51 PM
Part-Time Coder Re: OpenText function in... 11-06-2009, 05:26 PM
shg Re: OpenText function in... 11-06-2009, 05:32 PM
  1. #1
    Registered User
    Join Date
    10-29-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2007
    Posts
    2

    OpenText function in macro hangs unless stepping through code in debug

    We have had a macro running for a few years (Excel 2007 now, but started in 2003) that imports about 35 text files into separate sheets, creates a calculated SUM field for each sheet and copies that value to a title (or summary) sheet. We use this 2 to 4 times per year at inventory time, copying to a new file and deleting the old data before running the macro. The imported files initially create new workbooks, but the data is copied to the initial workbook into a distinct sheets for each file.

    Recently (well, last August) this macro started hanging after importing (Workbooks.OpenText) a number of files, and not necessarily the same file every time (on repeated runs.) While trying to figure out the problem, I have now managed to get it to hang every time on the first file! However, if I am stepping through in the debugger it continues past the OpenText command and on F5:Run/Continue will then continue processing the rest of the files normally. If I delete the first file before running the macro, it then hangs on the second file instead.

    Without debugging, the first file will import, display on-screen, and there it stops. If I put a break-point on the very next instruction after the import, that break-point is never reached.
    THINGS I'VE TRIED:
    I replaced the file contents with random typing (like ten lines of text) in case there were non-standard characters in the text file.
    I tried separating the import into a separate subroutine.
    I used 2-dimensional column arrays instead of the Array of Arrays currently in the code.
    I've importing into a single 80-character column.
    I've turned off screen updating while importing.
    I've googled through at least 25 different relevant threads, and only a few of those were directly relevant (e.g., using two-dimensional column arrays to conserve memory.)

    I'm about ready to open the files as input and write the data into a sheet line by line (probably would have been faster than all of that googling had I only known. )

    Here is the pertinent code:

       
       sPerpetualFolder = "\\TheServer\BranchFiles\Inventory\Reports\Perpetual"
    
       'Loop through branch-number cells (used in text file names.)
       row = ActiveCell.row
       Do While Range("A" & Format(row)).value > 0
          If ImportPerpetual(inventoryWorkbook, row) = CONTINUE Then
             '"Import" function moves focus away from Totals sheet.  Move focus back.
             Workbooks(inventoryWorkbook).Worksheets.Item("Totals").Select
             'Activate next branch-number cell.
             row = row + 1
             ActiveSheet.Range("A" & Format(row)).Activate
          Else
             Exit Do
          End If
       Loop
    
    
    '_______________________________________________
    Function ImportPerpetual(inventoryWorkbook As String, row As Long) As Long
    Dim sMsgTitle As String
    Dim i As Integer
    Dim sSearchFileName As String
    Dim sTextFileName As String
    Dim sTextFileSpec As String
    Dim sProcessedFileSpec As String
    Dim sheet As Integer
    Dim newSheetName As String
    Dim rowStart As Long
    Dim rowEnd As Long
    Dim rowLast As Long
    Dim brnchDescr As String
    Dim brnchNo As String
    Dim cell As Range
    Dim oFso As Object
    Dim oldStatusBar As Boolean
    
       'Get branch-number and -description from columns A and B.
       brnchNo = Range("A" & Format(row)).value
       brnchDescr = Range("B" & Format(row)).value
       sMsgTitle = "Import " & brnchDescr & " Data"
       
       sSearchFileName = sPerpetualFolder & "\Inventory*" & brnchNo & ".TXT"
                        
       sTextFileName = Dir(sSearchFileName)
       sTextFileSpec = sPerpetualFolder & "\" & sTextFileName
        
       Import (sTextFileSpec)
    
       'Execution never gets to this line unless stepping through the code in Debug
       '========================================================
                
       'Get the name of the new sheet so we can access it (Excel automatically uses the file name minus extension.)
       newSheetName = Left$(sTextFileName, Len(sTextFileName) - 4)
       Sheets(newSheetName).Select
       Application.CutCopyMode = False
       sheet = 0
       
       'Loop through sheets in inventory Workbook to see if branch has already been imported.
       'Etc., etc., additional processing of the imported sheet, 
       'moving data to the original workbook that fired off the macro.
    
    End Function
    
    
    '_______________________________________________
    Sub Import(textFile As String)
    
       Workbooks.OpenText Origin:=437, _
                          Filename:=textFile, _
                          DataType:=xlFixedWidth, _
                          StartRow:=1, _
                          FieldInfo:=Array(Array(0, 1), _
                                     Array(4, 1), _
                                     Array(6, 1), _
                                     Array(12, 1), _
                                     Array(29, 1), _
                                     Array(32, 1), _
                                     Array(38, 1), _
                                     Array(42, 1), _
                                     Array(48, 1), _
                                     Array(59, 1), _
                                     Array(70, 1)), _
                          TrailingMinusNumbers:=False
       
    End Sub
    Thanks in advance for any assistance!
    Last edited by Part-Time Coder; 11-06-2009 at 05:13 PM. Reason: Solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1