Please help: having problem with working macro on different machines

[This_Guy]

Hello everyone,

I'm a long time lurker of the site and hoping to receive a little bit of assistance...

The problem: The macro below, works just fine on my home PC but errors out on the computers at work. I have tested it on two different work machines. Both the home PC and one of the work computers are running Windows 7, SP1, while the other work computer is XP, SP3. All PCs are running Excel 2007.

Bambling Background: This hasn't been a big issue over the past year or so, because when the work computers errored out, I simply inputted the excellent line of "On Error Resume Next" in the macro's code. I would then run a DataLoad macro that would manually combine the text files between two instances of Notepad++. I also utilized a batch script that combined all text files in a directory as well. The three outputs would then be compared in a spreadsheet (and there have never been any variances between the three). However, we can't be doing this any longer, as this process is related to compliance and is moving to someone else with average computer skills (for both reasons, we have to be on the super safe side). As a result, I really need to figure out how to make this macro work correctly without the "On Error Resume Next" line of code.

The macro and some of my misc notes follows:

Sub A_Import_all_text_files_in_a_dir()
'Added 6/24/12
Dim myDir As String, fn As String, ff As Integer, txt As String, a()
Dim x, i As Long, n As Long, b(), t As Long
Dim Directory
Dim Prompt
Dim FileFormat
Dim FlFrmt As String
 
On Error Resume Next
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Prompt = MsgBox("Run the import macro?", Buttons:=vbOKCancel)
'Prompt = vbOK
If Prompt = vbOK Then
    Range("A2").Select
    Directory = GetFolder()
    myDir = Directory
    
    'FlFrmt = "txt"
    FileFormat = Application.InputBox("Files' format?" & vbNewLine & "(i.e. TXT, CSV, etc?)", "Input File Type", "txt", Type:=2)
    FlFrmt = FileFormat
      
    fn = Dir(myDir & "\*." & FlFrmt)
    Do While fn <> ""
        ff = FreeFile
        Open myDir & "\" & fn For Input As #ff
        Do While Not EOF(ff)
            Line Input #ff, txt
            x = Split(txt, Chr(9)) 'Note: this delimits the data at a tab character
            'x = Split(x, "~") 'Note: this makes the text squiggly mark delimted
            'x = Split(x, ",") 'Note: this makes the text comma delimited
            n = n + 1
            ReDim Preserve a(1 To n)
            a(n) = x
        Loop
        Close #ff
        'With ThisWorkbook.Sheets(1)
        With ActiveCell
        'With ActiveSheet
            'You can use either the Cells or the Offset methods, Cells can be used with ActiveCell or ActiveSheet
            .Cells(t + 1, 1).Value = fn 'this setting puts the titles in column A and inserts a line before the title
            '.Cells(t + 1, 2).Value = fn 'this setting puts the titles in column B
                'Note: offset can only be used with ActiveCell, (DNU with ActiveSheet)
                    '.Offset(t, 0).Value = fn 'This setting puts the titles in column A
                    '.Offset(t, 1).Value = fn 'This setting puts the titles in column B
            For i = 1 To n
            'You can use either the Cells or the Offset methods, Cells can be used with ActiveCell or ActiveSheet
'''THIS IS THE PROBLEM LINE OF CODE:
            .Cells(i + t + 1, 1).Resize(, UBound(a(i)) + 1).Value = a(i) 'this setting puts the content in column A and inserts a line after the content
            '.Cells(i + t + 1, 2).Resize(, UBound(a(i)) + 1).Value = a(i) 'this setting puts the content in column B
                'Note: offset can only be used with ActiveCell, (DNU with ActiveSheet)
                    '.Offset(i + t, 0).Resize(, UBound(a(i)) + 1).Value = a(i) 'this setting puts the content in column A
                    '.Offset(i + t, 1).Resize(, UBound(a(i)) + 1).Value = a(i) 'this setting puts the content in column B
            Next
        End With
        Erase a: t = t + n + 2: n = 0
        fn = Dir()
    Loop
End If
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 
End Sub
Function GetFolder() As String
'Keep this it works - 6/24/12
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = "%USERPROFILE%\Desktop\"
        '.InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

When I run the above code on either one of the machines at work, the macro errors out. When I click on debug, Excel opens the VB debugger with the following line highlighted:

 .Cells(i + t + 1, 1).Resize(, UBound(a(i)) + 1).Value = a(i) 'this  setting puts the content in column A and inserts a line after the  content

I do not understand how two of the three computers with basically the same setup, error out while the other one doesn't...

If you have any thoughts, please drop me a line. Any and all feedback is greatly appreciated.

Thanks,
Cark

 

[This_Guy]

JLGWhiz,

Thanks a million for your insights.