EXCEL 2007: macro runs on one PC but not on other

Question

I have written a macro for my boss to open a particular folder which contain approximately 100 workbooks having same format and collate all the data from those workbooks into the host excel where the macro is. Now the problem is, it works absolutely fine on my PC but when I had run it on the boss' PC it runs without executing the code(no data is collated) and displays the success message in the end in a second. Any help is appreciated. Here is the macro code

Sub collate()

Application.ScreenUpdating = False

Dim folderDialog As FileDialog
Dim folderPath As String, filename As String
Dim temp As Variant
Dim folder As Object, file As Object
Dim row As Integer, lastrow As Integer

MsgBox "Please select the folder containing all the input files", vbOKOnly

Set folderDialog = Application.FileDialog(msoFileDialogFolderPicker)
folderDialog.AllowMultiSelect = False
folderDialog.Show

On Error GoTo ext
folderPath = folderDialog.SelectedItems(1)

Set temp = CreateObject("Scripting.FileSystemObject")
Set folder = temp.GetFolder(folderPath)
row = Sheet1.Cells(Rows.Count, 2).End(xlUp).row
If row > 3 Then Sheet1.Range("B4:I" & row).Clear
row = 4

For Each file In folder.Files

    filename = file.Name
    filename = Left(filename, Len(filename) - 5)

    Application.Workbooks.Open (folderPath & "\" & filename)
    lastrow = Workbooks(filename).Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).row
    Workbooks(filename).Worksheets("Sheet1").Range("B4:I" & lastrow).Copy

    Sheet1.Range("B" & row).PasteSpecial xlPasteValues
    Sheet1.Range("B" & row).PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

    row = Sheet1.Cells(Rows.Count, 2).End(xlUp).row + 1
    Application.Workbooks(filename).Close savechanges:=False

Next

ext:
If folderPath = "" Then
MsgBox "Folder not selected!"
Application.ScreenUpdating = True
Exit Sub
End If

Sheet1.Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Data successfully merged!", vbInformation
End Sub

For the moment, remove this line `On Error GoTo ext`, then execute the macro on your boss' computer, then copy-paste us the error message (including the line) for us to help you properly. — Matteo NNZ, Jan 24 '15 at 12:43
I had removed On Error GoTo ext it didn't show any error message but instead only success message. Even if there was some error, it should have at least showed "Folder not selected!" message. — ConfusedSoul, Jan 24 '15 at 17:57
All the success message means is that a folder was selected by the user. Put a breakpoint on the For Each line(F9) and step through the code(F8). This will show you exactly what the code it is doing. — Paul Kelly, Jan 24 '15 at 18:05
I did use the code break on my PC but not on my boss' PC. Not sure if I can even check using his laptop if you can understand what I mean. While using F5 on my PC runs the code fine same doesn't on his — ConfusedSoul, Jan 24 '15 at 18:10

Davesexcel · Answer 1 · 2015-01-24T12:50:19.720

Try this version

Sub LoopThroughFolder()
    Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
    Dim Rws As Long, Rng As Range
    Set Wb = ThisWorkbook
    'change the address to suite
    MyDir = "C:\Test2\"

    MyFile = Dir(MyDir & "*.xlsx")    'change file extension

    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0

    Do While MyFile <> ""
        Workbooks.Open (MyFile)
        With Worksheets("Sheet1")
            MsgBox "your code goes here -" & MyFile
            '            Rws = .Cells(Rows.Count, "B").End(xlUp).Row
            '            Set Rng = Range(.Cells(2, 1), .Cells(Rws, 2))
            '            Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            ActiveWorkbook.Close True
        End With
        Application.DisplayAlerts = 1
        MyFile = Dir()
    Loop

End Sub

I tried this code on my PC. On the first iteration itself 'MyFile' variable is blank and it doesn't even enter the loop — ConfusedSoul, Jan 24 '15 at 18:00

score 0 · Accepted Answer · edited May 23 '17 at 11:52

0

You may need to enable the Microsoft Scripting Runtime library on your boss's computer if you haven't already. In some instances this library needs to be enabled in order to interface with the File System Object.

This library can be accessed from the Visual Basic Editor by pressing Tools > References > Microsoft Scripting Runtime. See the link below for further information.

Microsoft Scripting Runtime Library

edited May 23 '17 at 11:52

Community

1
1

answered Jan 25 '15 at 00:50

Thomas Bescherer

128
4

1

The error cannot depend on this. The asker is saying he gets the code running but not selecting anything, while if this was the problem he could not even run the code because he would get a compile error. – Matteo NNZ Jan 25 '15 at 22:48

score 0 · Answer 3 · answered Jun 05 '15 at 16:44

0

1 scenario where the code finishes in a second is when you have selected an empty folder or selecting a folder containing other than excel files.

Try checking the correct folder to select and execute the code. It should work fine.

answered Jun 05 '15 at 16:44

nirmalraj17

494
7
20

EXCEL 2007: macro runs on one PC but not on other

3 Answers3