Help Please: Retrieving the first populated cell value from a specific excel spreadsheet column
Troy Balmer
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.
Pegasus [MVP]
"Troy Balmer" <troy....@epa.vic.gov.au> schrieb im Newsbeitrag
news:hg454l$6p8$1...@news-01.bur.connect.com.au...
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
Pegasus [MVP]
"Troy Balmer" <troy....@epa.vic.gov.au> schrieb im Newsbeitrag
news:hg454l$6p8$1...@news-01.bur.connect.com.au...
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.
Troy Balmer
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]" <ne...@microsoft.com> wrote in message
news:%23UFJWgO...@TK2MSFTNGP06.phx.gbl...
Pegasus [MVP]
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....@epa.vic.gov.au> screv in
news:hg6dtu$c0d$1...@news-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]" <ne...@microsoft.com> wrote in message
> news:%23UFJWgO...@TK2MSFTNGP06.phx.gbl...
>>
>>
>> "Troy Balmer" <troy....@epa.vic.gov.au> screv in
Reventlov
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)
--