INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

Excel find/add

Excel find/add

(OP)
Hi Guys,

I have the following script that looks in 1 excel sheet, then looks in another sheet for a matching server name, then will append certain information if found.

I think it's working fine, but wondered if there was a quicker way to check as it runs quite slowly?
Perhaps there's a better way of checking in excel?

Code:

Set objExcel = CreateObject("Excel.Application")
Set objExcel2 = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
objExcel2.Visible = False
objExcel2.DisplayAlerts = False

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

strNewPath = "blah.xlsx"

Set objWorkbook1 = objExcel.Workbooks.Open("blah.xlsx")
Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objWorkbook2 = objExcel2.Workbooks.Open("blah.xlsx")
Set objWorksheet2 = objWorkbook2.Worksheets(1)

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

iRow = 2
introw = 2
Do Until objWorksheet1.Cells(intRow, 1).Value = ""
strValue = LCase(objWorksheet1.Cells(intRow, 1).Value)
strApp = objWorksheet1.Cells(intRow,4).Value
strOwner = objWorksheet1.Cells(intRow,8).Value
Wscript.Echo strValue & vbtab & strApp & vbtab & strOwner
Call CheckSecond(strValue, strApp, strOwner)
introw = introw + 1
Loop

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

objWorkBook1.Close
objExcel.Quit
set objExcel=nothing
objWorkbook2.SaveAs strNewPath
objWorkBook2.Close
objExcel2.Quit
set objExcel2=nothing

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Function CheckSecond(strValue, strApp, strOwner)
iRow = 2
Wscript.Echo VbTab & "Searching For: " & strValue
Do Until iRow = 982
Val = LCase(strValue)
celltocheck = objWorksheet2.Cells(iRow, 1).Value
iRow = iRow + 1
If Instr(celltocheck,Val) Then
Wscript.Echo VbTab & "Found: " & celltocheck & " Row (" & iRow & ")"
objWorksheet2.Cells(iRow,11).Value = strApp
objWorksheet2.Cells(iRow,12).Value = strOwner
iRow = iRow + 1
End If
Loop
End Function


RE: Excel find/add

I'd assign one and only one Excel Application Object.

The rest of you blah stuff is not making sense as it seems you've got the same workbook/worksheet open.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel find/add

(OP)
Sorry, let me clarify:

strNewPath = "DestinationWorksheet.xlsx"

Set objWorkbook1 = objExcel.Workbooks.Open("TargetWorksheet.xlsx")
Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objWorkbook2 = objExcel2.Workbooks.Open(strNewPath)
Set objWorksheet2 = objWorkbook2.Worksheets(1)

RE: Excel find/add

I'd assign one and only one Excel Application Object.

CODE

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

strNewPath = "DestinationWorksheet.xlsx"

Set objWorkbook1 = objExcel.Workbooks.Open("TargetWorksheet.xlsx")
Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objWorkbook2 = objExcel.Workbooks.Open(strNewPath)
Set objWorksheet2 = objWorkbook2.Worksheets(1) 

Are there more than one values to match in worksheet2? If not, then why continue the loop in the called function. In that case EXIT

CODE

'
Function CheckSecond(strValue, strApp, strOwner)
    iRow = 2
    Wscript.Echo vbTab & "Searching For: " & strValue
    Do Until iRow = 982
        Val = LCase(strValue)
        celltocheck = objWorksheet2.Cells(iRow, 1).Value
        iRow = iRow + 1
        If InStr(celltocheck, Val) Then
            Wscript.Echo vbTab & "Found: " & celltocheck & " Row (" & iRow & ")"
            objWorksheet2.Cells(iRow, 11).Value = strApp
            objWorksheet2.Cells(iRow, 12).Value = strOwner
            Exit Loop
        End If
    Loop
End Function 


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close