Solved: Only running MyExcel.xls on named machine?

[Ken Puls]

Hi Simon,

Your code is close. CN and UN are both variant strings in your case, and you're trying to set them as objects. If you want to run your code, you'd dim them as strings and just assign the values with them. No set statements:

Sub CollectNames()
    Dim CN As String, UN As String
    Dim nRow As Long, nRow1 As Long
    nRow = Sheets("Sheet1").Range("A65336").End(xlUp).Row + 1
    nRow1 = Sheets("Sheet1").Range("B65336").End(xlUp).Row + 1
    CN = Environ("ComputerName")
    UN = Environ("UserName")
    Workbooks.Open ("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
    Workbooks("Names Test.xls").Worksheets("Sheet1").Range("A" & nRow) = UN
    Workbooks("Names Test.xls").Worksheets("Sheet1").Range("A" & nRow1) = CN
    Workbook.Save
    Workbooks("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls").Close
End Sub

I didn't test the above, but it shoudl work. Personally, though I'd go with something like this. Same functionality, but looks a little more elegant.

Sub CollectNames()
    Dim wbDB As Workbook
    Set wbDB = Workbooks.Open("C:\Documents and Settings\vrtSzL04\Desktop\Names Test.xls")
    With wbDB
        With .Worksheets("Sheet1")
            With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Value = Environ("ComputerName")
                .Offset(0, 1).Value = Environ("Username")
            End With
        End With
    .Close savechanges:=True
    End With
End Sub

HTH,