R – Excel sheet doubts in vbscripting

I doubt there is a simple way to do this. From what I understand, you want to try to match a value in one worksheet in 3 possible columns in another worksheet and then output certain data from both worksheets to a new worksheet. I really don't see a clever, easy way to do this.

However, here are some suggestions. And forgive me for stating items you already know, as it seams you do know how to program:

Make use of Enum to find and re-order the copied data

Example:

Public Enum wks1Columns
    Section_Dept = 1
    City
    Building
    SVD_User_Name
    Item_Short_Code
    etc
End Enum

Public Enum wks2Columns
    Hostname = 1
    Management_IP
    Device_Type
    etc
End Enum

Public Sub test()
    Dim wk1 As Excel.Worksheet
    Dim wk2 As Excel.Worksheet
    Set wk1 = ThisWorkbook.Worksheets("WORKSHEET1")
    Set wk2 = ThisWorkbook.Worksheets("WORKSHEET2")

    ' imagine Building is in column 5 in WORKSHEET1 and SVD is in column 7 in WORKSHEET1
    ' but you wanted to put them in columns 1 and 2 in the new worksheet
    Sheet1.Cells(1, 1).Value = wk1.Cells(1, wks1Columns.Building).Value
    Sheet1.Cells(1, 2).Value = wk1.Cells(1, wks1Columns.SVD_User_Name).Value

    ' and you wanted stuff from WORKSHEET2 in the same row
    Sheet1.Cells(1, 3).Value = wk2.Cells(1, wks2Columns.Hostname).Value
End Sub

Using enums, you can store how the columns are already setup in the worksheets, then later use them to EASILY re-order a new sheet using the enum method. The cool thing is, if the WORKSHEET1 column for Section Dept ever moves to column 2 and City to column 1, you just need to re-order the enum and BOOM, you're done modifying your code ;)

Break your function up into smaller tasks

This is definitely a complicated task that you're trying to do and would be way too difficult working with if you put this all in one big Sub. Something like:

Public Sub main()
    Dim wk1 As Excel.Worksheet
    Dim wk2 As Excel.Worksheet
    Dim wkNew As Excel.Worksheet
    Set wk1 = ThisWorkbook.Worksheets("WORKSHEET1")
    Set wk2 = ThisWorkbook.Worksheets("WORKSHEET2")
    Set wkNew = ThisWorkbook.Worksheets.Add

    wkNew.Name = "My New Worksheet"

    Dim newWkRow As Long
    newWkRow = 1

    Dim itemShortCode As String

    ' loop through wk2
    ' for each wk2.Cell found, call checkForMatch()
        ' store checkForMatch() value into variable
        itemShortCode = checkForMatch("my value", wk1)

        ' export to new worksheet
        exportToNewWorksheet wkNew, wk2, currentRowFromLoop, newWkRow, itemShortCode

        newWkRow = newWkRow + 1 ' the only reason for newWkRow is if you want to skip any
                                ' entries from WORKSHEET2. So it's best to keep this count separate
                                ' from your current loop row

    ' next
End Sub

Private Function checkForMatch(ByRef theValue As String, ByRef wk1 As Excel.Worksheet) As String
    ' PLEASE NOTE: wk1 does NOT need to match in the function definition to that of the
    '               variable defined in main()


    ' search for match from wk2 to wk1


    ' if found, return the Item_Short_Code
    ' otherwise return vbNullString

End Function

Private Sub exportToNewWorksheet(ByRef newWs As Excel.Worksheet, _
                                ByRef wk2 As Excel.Worksheet, _
                                ByRef wk2Row As Long, _
                                ByVal newRow As Long, _
                                Optional ByVal Item_Short_Code As String = vbNullString)

    ' put data into new row. be sure to use the Enum to re-order the column as you like
    If (Item_Short_Code <> vbNullString) Then
        ' store data one way
        ' ...
    Else
        ' store data another way
        newWs.Cells(newRow, 1).Value = Item_Short_Code
        newWs.Cells(newRow, 2).Value = wk2.Cells(wk2Row, wks2Columns.Hostname).Value
        ' etc...
    End If
End Sub

I think you might be getting caught up in the syntax of it all. Some tips from what I see in your code:

1. Fully qualify your range objects. The Cell object always refers to the active sheet's cell and it won't help you if you run the code from another sheet that you didn't mean to.
2. Offset(0,0) doesn't do anything. Just use .Value if you want to set a value of a range
3. If your main sheet has the same last row for all columns, you can just store the last row into a variable and use that in subsequent Range sets
4. I think you have programmed before and if so, you would fly right through some VBA tutorials found online. It's worth the effort, even if you have a tight deadline.

Hope this helps

CreateObject creates a new object. If I understand your question correctly you want to attach to already running (orphaned) Excel processes to terminate them. You can do that with GetObject:

On Error Resume Next
Do
  Set xl = GetObject(, "Excel.Application")
  status = Err.Number
  If status = 0 Then
    For Each wb in xl.Workbooks
      wb.Close False  'discard changes in open workbooks
    Next
    xl.Quit
  ElseIf status <> 429 Then
    WScript.Echo Err.Number & ": " & Err.Description
    WScript.Quit 1
  End If
Until status = 429
On Error Goto 0

Note that this will try to close all running Excel instances, discarding all changes in open workbooks. If you want it to save changes in open workbooks change the argument of the Close method to True. If you have Excel instances you want to keep running, you need to add code to exclude them from being closed.

Note also, that this will not forcibly terminate unresponsive instances. You'd need to kill the process for that:

Set wmi = GetObject("winmgmts://root/cimv2")
For Each xl In wmi.ExecQuery("SELECT * FROM Win32_Process WHERE Name = 'excel.exe'")
  xl.Terminate
Next