Hello all, if this question is asked before sorry, but i have quite a problem with it.
I have an excel sheet is use to track progress and status on my projects, with quite some urls to document locations on share point and comments added to cells.
I now need to hand over some work for my x-mass brake using template forms. Well automating boring work that is where winbatch come's in :D But i cannot figer out how to read the data from
hyperlinks and comment's added to cells. I found some white papers how to add them, but not how to read them..........
How can i read the hyperlink Address added to a excel 2010 cell
following code return a value like $58$37
How can i read the comment text added to a excel 2010 cell
following code return a value like 49493458
variable 'worksheet' contains path+filename of workboo file to open.
objxl = ObjectCreate("Excel.Application")
objxl.visible = @FALSE
objwbks = objxl.workbooks
objwbks.open (worksheet)
objawb = objxl.activeworkbook
objsheet = objawb.worksheets(1)
rowcount = objsheet.UsedRange.Rows(objsheet.UsedRange.Rows.Count).Row
For rownum = 1 To rowcount
status=objsheet.cells(rownum, 1).value
if status=="A" || status=="N" || status=="Q" then
IMAC=objsheet.cells(rownum, 2).value
SITE=objsheet.cells(rownum, 3).value
TOWER=objsheet.cells(rownum, 4).value
DESCRIPTION=objsheet.cells(rownum, 5).value
RFQ=objsheet.cells(rownum, 6).value
TYPE=objsheet.cells(rownum, 7).value
CLASS=objsheet.cells(rownum, 8).value
STARTDATE=objsheet.cells(rownum, 11).value
QCD=objsheet.cells(rownum, 12).value
CRD=objsheet.cells(rownum, 13).value
COMMENT=objsheet.cells(rownum, 13).Comment
URL=objsheet.cells(rownum, 16).Address
Maybe try:
COMMENT=objsheet.cells(rownum, 13).Comment.Value
URL=objsheet.cells(rownum, 16).Hyperlinks.Item(1).Address
Thanks for the fast response :D
The Hyperlink solution works fine, super :)
The comment solution ends in a fatal error, object unknown. :(
Okay maybe try:
objComment = objSheet.Cells(rownum, 13).Comment
type = ObjectTypeGet( objComment )
If type != 'NULL'
strComment = objComment.Text
Pause( 'Cell Comment', strComment )
EndIf
Reference: http://social.msdn.microsoft.com/Forums/office/en-US/d6801e00-254f-4b16-8a3b-dac33fba75c7/reading-cell-comments-from-excel-into-access?forum=accessdev