Thanks for your help Pegasus.
I'm unsure how to include this in a vbscript so that it will open up the
spreadsheet, retrieve the first found value and place it into a string for
use by other actions in the script.
As per my example, the script needs to get '55553' into a string
Whilst the action to search for the first populated cell in a column is
greatly appreciated, I really need it incorperated into a complete script to
obtain this value as I don't have the knowledge to do so.
Many thanks for your response.
"Pegasus [MVP]" <news@newsgroup> wrote in message
> "Troy Balmer" <troy.balmer@newsgroup> schrieb im Newsbeitrag
>> I need help with retrieving the first populated cell value from a
>> specific excel spreadsheet column. This value will then be used for
>> later parts in an broader script to mark the entry as used.
>> The 3rd worksheet contains 3 columns. I need this code to go through all
>> the cells in the 2nd column, in order from top to bottom, and have it
>> return the first value found. In this example, it should return 55553.
>> Phone Free Used
>> 55550 55550
>> 55551 55551
>> 55552 55552
>> 55553 55553
>> 55554 55554
>> 55555 55555
>> Due to the structure of the spreadsheet, this column contains a lot of
>> empty cells.
>> I've gone through a number of different script sites but never found any
>> to actually automate the search and report back the first entry found
>> from the required column.
>> Preferably the code that returns the first cell value should be accessing
>> the spreadsheet as a background process (rather than it actually opening
>> the file). Hopefully it would be similar to the following which I used
>> in another script:
>> Const adOpenStatic = 3
>> Const adLockOptimistic = 3
>> Const adCmdText = &H0001
>> Set objConnection = CreateObject("ADODB.Connection")
>> Set objRecordSet = CreateObject("ADODB.Recordset")
>> strTelExt = 69696 'The extension of a given staff member.
>> IntTelExt = Len(strTelExt) 'Gets the length of the extension -
>> used for prettying up a MsgBox prompt
>> strFirstNum = Left(strTelExt, 1) 'Gets the first number of the
>> extension in a seperate string - used for prettying up a MsgBox prompt
>> strRestNum = Right(strTelExt, IntTelExt - 1) 'Gets the remaining
>> numbers of the extension in a seperate string - used for prettying up a
>> MsgBox prompt
>> strFullTelNum = "3969" & strTelExt 'Is the full telephone number
>> in the format matching the spreadsheet.
>> strFullTelNum2 = "03 969" & strFirstNum & " " & strRestNum 'Re-arranges
>> the number in complete format - used for prettying up a MsgBox prompt
>> objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> "Data Source=\\server\share\phones.xls;" & _
>> "Extended Properties=""Excel 8.0;HDR=Yes;"";"
>> objRecordset.Open "Select * FROM [EXTENSIONS$]", _
>> objConnection, adOpenStatic, adLockOptimistic, adCmdText
>> 'Searches for a specific entry in the worksheet
>> strSearchCriteria = "Phone = " & strFullTelNum
>> objRecordSet.Find strSearchCriteria
>> 'As this script is to mark the extension as free, it puts that number in
>> the "FREE" column.
>> objRecordset("FREE") = strFullTelNum
>> 'As this script is to mark the extension as free, it removes that number
>> from the "USED" column.
>> objRecordset("USED") = ClearValue
>> Many thanks in advance for your help.
> It seems there is a function in Excel to detect a non-blank cell. Here is
> working script sample, this time without errors:
> Function sBlankCell
> Const xlCellTypeBlanks = 2
> sBlankCell = oExcel.ActiveCell.Address
> End Function
> You may have to experiment a little to find out what number to use for
> special cells. 2 stands for the first cell to contain some value (but not
> formula). 11 stands for the last non-blank cell. Other numbers have
> that I do not currently know.