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
objRecordset.Update

'As this script is to mark the extension as free, it removes that number
from the "USED" column.
objRecordset("USED") = ClearValue
objRecordset.Update

objRecordset.Close
objConnection.Close


Many thanks in advance for your help.

Troy.

"Troy Balmer" <troy.balmer@newsgroup> schrieb im Newsbeitrag
news:hg454l$6p8$1@newsgroup-01.bur.connect.com.au...

> 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
> objRecordset.Update
>
> 'As this script is to mark the extension as free, it removes that number
> from the "USED" column.
> objRecordset("USED") = ClearValue
> objRecordset.Update
>
> objRecordset.Close
> objConnection.Close
>
>
> Many thanks in advance for your help.
>
> Troy.

I'm not aware of a function built into Excel to look for a non-blank cell.
You would therefore have to test each cell manually, e.g. like so:

For i = 2 To iLastRow
if oSheet.cells(i, 1) <> "" then Exit For
Next

Function iLastRow
Const xlCellTypeLastCell = 11
oSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Activate
iLastRow = oExcel.ActiveCell.Row
oSheet.Range("A1:A1").activateEnd
Sub

"Troy Balmer" <troy.balmer@newsgroup> schrieb im Newsbeitrag
news:hg454l$6p8$1@newsgroup-01.bur.connect.com.au...

> 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
> objRecordset.Update
>
> 'As this script is to mark the extension as free, it removes that number
> from the "USED" column.
> objRecordset("USED") = ClearValue
> objRecordset.Update
>
> objRecordset.Close
> objConnection.Close
>
>
> Many thanks in advance for your help.
>
> Troy.
>

It seems there is a function in Excel to detect a non-blank cell. Here is a
working script sample, this time without errors:

Function sBlankCell
Const xlCellTypeBlanks = 2
oSheet.UsedRange.SpecialCells(xlCellTypeblanks).Activate
sBlankCell = oExcel.ActiveCell.Address
End Function

You may have to experiment a little to find out what number to use for your
special cells. 2 stands for the first cell to contain some value (but not a
formula). 11 stands for the last non-blank cell. Other numbers have meanings
that I do not currently know.

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
'strFreeExt'.

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.

Troy.


"Pegasus [MVP]" <news@newsgroup> wrote in message
news:%23UFJWgOfKHA.1592@newsgroup

>
>
> "Troy Balmer" <troy.balmer@newsgroup> schrieb im Newsbeitrag
> news:hg454l$6p8$1@newsgroup-01.bur.connect.com.au...

>> 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
>> objRecordset.Update
>>
>> 'As this script is to mark the extension as free, it removes that number
>> from the "USED" column.
>> objRecordset("USED") = ClearValue
>> objRecordset.Update
>>
>> objRecordset.Close
>> objConnection.Close
>>
>>
>> Many thanks in advance for your help.
>>
>> Troy.
>>

>
> It seems there is a function in Excel to detect a non-blank cell. Here is
> a
> working script sample, this time without errors:
>
> Function sBlankCell
> Const xlCellTypeBlanks = 2
> oSheet.UsedRange.SpecialCells(xlCellTypeblanks).Activate
> sBlankCell = oExcel.ActiveCell.Address
> End Function
>
> You may have to experiment a little to find out what number to use for
> your
> special cells. 2 stands for the first cell to contain some value (but not
> a
> formula). 11 stands for the last non-blank cell. Other numbers have
> meanings
> that I do not currently know.
>
>

In this newsgroup you might find that respondents answer specific questions,
e.g. how to find the first non-blank cell in a spreadsheet, or they answer
general questions, e.g. what is the best approach to tackle a certain
project. If you require a turn-key solution then you probably have to pay
someone to write the complete code for you. Alternatively you could ask your
manager to send you to a VB Script training course. While working at the SEC
(while it still existed!) I attended similar courses.

"Troy Balmer" <troy.balmer@newsgroup> screv in
news:hg6dtu$c0d$1@newsgroup-01.bur.connect.com.au...

> 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
> 'strFreeExt'.
>
> 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.
>
> Troy.
>
>
> "Pegasus [MVP]" <news@newsgroup> wrote in message
> news:%23UFJWgOfKHA.1592@newsgroup

>>
>>
>> "Troy Balmer" <troy.balmer@newsgroup> screv in
>> news:hg454l$6p8$1@newsgroup-01.bur.connect.com.au...

>>> 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
>>> objRecordset.Update
>>>
>>> 'As this script is to mark the extension as free, it removes that number
>>> from the "USED" column.
>>> objRecordset("USED") = ClearValue
>>> objRecordset.Update
>>>
>>> objRecordset.Close
>>> objConnection.Close
>>>
>>>
>>> Many thanks in advance for your help.
>>>
>>> Troy.
>>>

>>
>> It seems there is a function in Excel to detect a non-blank cell. Here is
>> a
>> working script sample, this time without errors:
>>
>> Function sBlankCell
>> Const xlCellTypeBlanks = 2
>> oSheet.UsedRange.SpecialCells(xlCellTypeblanks).Activate
>> sBlankCell = oExcel.ActiveCell.Address
>> End Function
>>
>> You may have to experiment a little to find out what number to use for
>> your
>> special cells. 2 stands for the first cell to contain some value (but not
>> a
>> formula). 11 stands for the last non-blank cell. Other numbers have
>> meanings
>> that I do not currently know.
>>
>>

>
>

Il giorno Tue, 15 Dec 2009 09:23:51 +1100, "Troy Balmer" <troy.balmer@newsgroup> ha
scritto:

>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.

Set objXL = CreateObject("Excel.Application")
objXL.visible=true
objxl.open filename

>> Const xlCellTypeBlanks = 2

objXL.oSheet.UsedRange.SpecialCells(xlCellTypeblanks).Activate

>> sBlankCell = objXL.ActiveCell.Address

--
Giovanni Cenati (Bergamo, Italy)
Write to "Reventlov" at katamail com
http://digilander.libero.it/Cenati (Esempi e programmi in VbScript)
--