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

 

[dermie_72]

are they all using the same Microsoft Visual Basic for applications version?

Open Excel, Press Alt + F11
Click on Help
About

 

[This_Guy]

Hey dermie,

Thank you for your quick response. I have been able to view both MS VB versions, and they're kind of the same which may or not be a good thing.

All three computers are running the following:
MS VB 6.5
Version 1054

However,
My home computer is running:
Forms3: 14.0.6009.100

Whereas, the work computers are running:
Forms3: 12.0.6514.500

Please let me know if you need any more information or if you have a possible fix in mind.

Thanks again,
Cark

 

[dermie_72]

I was hoping there would be something more glaringly different than just the forms. In my experience, when a code works on one machine, and not on the other, the difference is usually the version.

When you step through the macro, does the variable I and T (both noted in the debug line), have values allocated to them?

 

[This_Guy]

Hey dermie,

Thank you for getting back with me - that is really awesome of you and greatly appreciated.

I'll spare you a long story of my endeavors up until this point (i.e. attempting to recreate the issue on a virtual machine, and then having the macros run correctly even with a previous version of 6.5.1020 and Forms 3: 12.0.4518.101! Unbelievable lol!).

Anywho, as I can't access my work's computer until Tuesday morning (due to Monday being Memorial Day here in the US), I'm afraid that I will have to answer your question with an assumption because I can't tell you for sure one way or another... I would expect that the variables have the values correctly allocated to them when the macro is run on my company's machines. I say this because the work computer's macros run successfully, even though the "On Error Resume Next" text has been added. Therefore, the ouput is still the same.

For instance, the macro still correctly imports the content into the spreadsheet line by line adhering to the following format:

Text File A's File Name.txt

Text file A's content line 1
Text file A's content line 2
Text file A's content line 3

Text File B's File Name.txt

Text file B's content line 1
Text file B's content line 2
Text file B's content line 3

and so on...

With that said, the remaining portion of the troublesome code is the middle section.
So

.Cells(i + t + 1, 1).Resize(, UBound(a(i)) + 1).Value = a(i)

should instead probably be changed to

.Cells(i + t + 1, 1).Value = a(i)

first thing on Tuesday morning and then re-tested from there.

I have just tested this updated code on my home computer and it seems to work just the same even without the extra verbiage. This of course might be good or bad, I don't know. I am somewhat more optimistic now, as the first portion of the macro contains essentially this same line of code to import the filenames, and doesn't error out at that step.

Although, I'm not too familar with the Resize and UBound elements. Do you see any reason why I shouldn't just be able to remove the same text for my work's macro?

Thanks again,
Cark

 

[This_Guy]

Hey Dermie,

A quick update: I just modified the existing macro, to utilize Excel's basic "Import Text" feature instead. That way the macro still imports all of the text files in a directory while achieving the same ends, yet with this new approach I don't have to worry about that Resize and UBound compatibility nonsense.

Thanks again for all of your input throughout this whole process.

Best regards,
Cark

 

[This_Guy]

Okay wow this is ridiculous. I'm sorry moderator, but I can't find a button or link anywhere to close out and mark this thread as having been solved by Mr. Dermie. Please do so, or let me know how to do this.

Thanks,
Cark

 

[dermie_72]

Hey Cark,

That's great news that the matter is now resolved, thank you for the confirmation, its always great to get that, but more importantly, the problem is fixed, and there is a thread for others to follow if they have a similar issue

Dermie

 

[This_Guy]

For anyone who stumbles across this posting, I wanted to follow up to provide completeness.

I ended up not using my new modified and updated macro version. Instead, I merely just removed the

Resize(, UBound(a(i)) + 1)

Verbiage and the code works just fine from here.

Thanks again Dermie.
</pre>

 

[JLGWhiz]

For anyone who stumbles across this posting, I wanted to follow up to provide completeness.

I ended up not using my new modified and updated macro version. Instead, I merely just removed the



Verbiage and the code works just fine from here.

Thanks again Dermie.

</PRE>

UBound(a(i))

This looks like the culprit. You can use UBound(a) which would give you the numerical count for the high end of the array. But a(i) is the specific value from the array at that iteration of the 'For i =' loop, So the compiler goes bananas wondering how to get the high end of a specific value as part of a UBound function. On the other hand, if you want that specific value, then use a(i) without the UBound.