Syntax problem for excel object commands

If you're working within Excel, you don't strictly have to create a new instance of Excel as you're doing in your code. However, in case that's what you want, I've included two example procedures, both of which should work OK. I think you're mistaking the Names collection, which is comprised of the defined names within a workbook with the Worksheet Name property.

Mudface,

thanks this gets me closer. I think I do want to use the Names collection. I need to run a script (outside of Excel) that looks a several different workbooks to see if they contain a specific worksheet.

The code you provided does work, but I can't seem to assign the ws.Name value into an array.

Thanks so much for your help.

I'm not sure if you need to assign the worksheet names into an array (although you can do quite easily). Would something like this do the trick?

The last mile

Mudface,

that'll definitely work, but can you help me with the last mile? :D

Now that I can find the sheet and determine if the column name exists in Row 1, I now need to be able to add the column name in row 1 if it does not exist. I'm guessing I have to figure out the last column populated in Row 1, and then insert the new column name in the first open column on row 1, but am struggling (again) with syntax.

Thanks for all the help.

- Phillip

OK, you can reference the last filled column in a row by using

Range("IV1").End(xlToLeft).Offset(, 1)

this looks from cell IV1 until it finds a cell with an entry and then offsets it by one column to reference the first empty cell in that row. Take a look at the End property in the VBA help files for more, as there are various directions in which you could look (from IV1 to the left, A1 to the right etc).

The slightly amended code would look something like this

Getting Closer

Mudface,

again, many thanks. However, I have uncovered part of my problem. I'm actually having to code this in VBScript not VBA. So..I have everything working, except the line that inserts the value. It doesn't error, but it doesn't seem to do anything.

It also doesn't seem to be saving the workbook when I tell it to save.

Here's the current version of my code:

Function WS_Search (Worksheet_Name, Column_Name, Column_Value)
Set OApp = CreateObject("Excel.Application")
'Set ws = OApp.ActiveWorkbook.Worksheets
OApp.Workbooks.Open "c:\myexcel.xls"
'OApp.visible = True
On Error Resume Next

For Each ws In OApp.ActiveWorkbook.Worksheets
if ws.Name = Worksheet_Name then
MsgBox ("Worksheet: " & ws.Name & " was found")
Search_Result = OApp.ActiveWorkbook.Worksheets(Worksheet_Name).Rows(1).Find(Column_Name).Column
If Search_Result = "" Then
msgbox ("column is missing and will be added: " & Column_Name)
'ws.Range("IV1").End(xlToLeft).Offset(, 1).Value = Column_Name
' OApp.ActiveWorkbook.Worksheets(Worksheet_Name).Range("IV1").End(xlToLeft).Offset(, 1).Value = Column_Name
Cell_Val = OApp.ActiveWorkbook.Worksheets(Worksheet_Name).Range("IV1").End(xlToLeft).Value
msgbox (Cell_Val)
else
msgbox ("Column already exists")
end if
end if
Next
Workbook.Save
OApp.Quit
End Function

Column_Value = "No"

Worksheet_Name = "Login"
Column_Name = "New_Col"
WS_Search Worksheet_Name, Column_Name, Column_Value

- Phillip