VBA code not running in a different pc

[masud8956]

Hi everyone!

I am trying to import a large array of data from multiple files located in a folder named "Aircrew_Flying_Hour" in D:\ using VBA.

The code runs alright in my pc. But it does not run in other pc. I need to be able to run it from a different pc. Do I need any modification of the code or it has something to do with the settings of the pc in which I am trying to run it?

Thanks in advance!

The VBA I am trying is below:

Sub test()    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Sheets("DATA").Cells.ClearContents
    Dim lastRow As Long
    Dim firstRow As Long
    Dim myDir As String, fn As String, n As Long, t As Long, Cell As String
    Const wsName As String = "Summary of the Year"
    Const myRng As String = "G77:U796"
    myDir = "D:\Aircrew_Flying_Hour"
    fn = Dir(myDir & "\*.xlsx")
    If fn = "" Then MsgBox "No files in the folder": Exit Sub
    With Range(myRng)
        n = .Rows.Count: t = .Columns.Count
        Cell = .Cells(1).Address(0, 0)
    End With
    Do While fn <> ""
        With Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(n, t)
            .Formula = "=if('" & myDir & "\[" & fn & "]" & wsName & "'!" & Cell & "<>""""," & _
                       "'" & myDir & "\[" & fn & "]" & wsName & "'!" & Cell & ","""")"
            .Value = .Value
        End With
        fn = Dir
    Loop
    firstRow = Sheets("DATA").Range("A1:A" & Sheets("DATA").Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
    lastRow = Sheets("DATA").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("DATA").Range("A" & firstRow & ":A" & lastRow).AutoFilter Field:=1, Criteria1:="="
    Sheets("DATA").Range("A" & firstRow & ":A" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    If Sheets("DATA").AutoFilterMode Then Sheets("DATA").AutoFilterMode = False
    Sheets("DATA COPY").Cells.ClearContents
    Sheets("DATA").Range("A1:O" & Sheets("DATA").Range("A" & Sheets("DATA").Rows.Count).End(xlUp).Row).Copy Sheets("DATA COPY").Range("A1")
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[mole999]

both pcs ? one isn't a mac is it
both using a similar excel version
both have the same reference addins
both have d: drives
macros enabled on both, these are not macro enabled
fn = Dir(myDir & "\*.xlsx") would be xlsb or xlsm

 

[masud8956]

Thanks for the quick reply!

Actually, I need it to work in any other pc. The file where I intend to import the data is .xlsm and I want all the employees of my office to use it in their personal computers. Some of them will use it in macbook as well.

Users will use different excel versions (I do not have control on that)

I do not have (or want to have) any control over the add ins of their pc

All pc have D:

The source files in the folder "Aircrew_Flying_Hour" located in D:\ are in .xlsx format

I am using Excel 2016 in my pc

 

[mole999]

as a minimum all need to have macros enabled, if they are disabled without warning then that would cause issues

 

[masud8956]

Yes.

I am trying in different pcs with macro enabled. The code seems to run but no data imported and no warning message as well. Does this have anything to do with the naming of D:\ in different pcs (people name D:\ like "Document", "Soft", "Personal" etc)?

 

[mole999]

d should be d, but the sub folder has to be right

maybe this might help when shortened > https://stackoverflow.com/questions/34094261/check-if-directory-exists-in-excel-2010-vba

a local D and network D may be different though (I'm not sure)