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!
Bookmarks