excel - Macro will only create hyperlink in debug mode -


this macro search cells in worksheets in of workbooks contained in single directory. works advertised except add hyperlink method, work if repeatedly mash f8.

how can edit macro hyperlink portion works?

'search workbooks in folder string sub searchworkbooks() dim fso object dim fld object dim strsearch string dim strpath string dim strfile string dim wout worksheet dim wbk workbook dim wks worksheet dim lrow long dim rfound range dim strfirstaddress string  on error goto errhandler application.screenupdating = false  strsearch = "capacitor" strpath = "c:\!source"  set wout = worksheets.add lrow = 1 wout     .name = "results"     .cells(lrow, 1) = "workbook"     .cells(lrow, 2) = "worksheet"     .cells(lrow, 3) = "cell"     .cells(lrow, 4) = "text in cell"     .cells(lrow, 5) = "link"     set fso = createobject("scripting.filesystemobject")     set fld = fso.getfolder(strpath)      strfile = dir(strpath & "\*.xls*")     while strfile <> ""         set wbk = workbooks.open _           (filename:=strpath & "\" & strfile, _           updatelinks:=0, _           readonly:=true, _           addtomru:=false)          each wks in wbk.worksheets             set rfound = wks.usedrange.find(strsearch)             if not rfound nothing                 strfirstaddress = rfound.address             end if                             if rfound nothing                     exit                 else                     lrow = lrow + 1                     .cells(lrow, 1) = wbk.name                     .cells(lrow, 2) = wks.name                     .cells(lrow, 3) = rfound.address                     .cells(lrow, 4) = rfound.value                      'this line not work                     'well works in debug mode not in real time                     wks.hyperlinks.add anchor:=cells(lrow, 5), address:=wbk.fullname, subaddress:= _                                     wks.name & "!" & rfound.address, texttodisplay:="link"                  end if                 set rfound = wks.cells.findnext(after:=rfound)             loop while strfirstaddress <> rfound.address         next          wbk.close (false)         strfile = dir     loop     .columns("a:d").entirecolumn.autofit end 'msgbox "done"      exithandler: set wout = nothing set wks = nothing set wbk = nothing set fld = nothing set fso = nothing application.screenupdating = true exit sub  errhandler: msgbox err.description, vbexclamation resume exithandler end sub

try adding worksheet reference cells() call out wks.cells(......)


Comments

Post a Comment

Popular posts from this blog

java - Spring Data JPA: Why findOne(id) executing delete query internally? -

i have 1 application using @manytomany annotation link 2 different entities 1 parent entity. not sure if have implemented properly. when execute .findone() on parent entity's repository find it, @ first fetch data along executing delete queries internally delete record mapping table (a table jpa creates after adding @manytomany annotation.). following generated sql queries , code snippet: java class @entity @table(name = "inventory") public class inventory { @id long id; // many other properties @manytomany(fetch=fetchtype.lazy) private list<stream> streams; @manytomany(fetch=fetchtype.lazy) private list<subject> subjects; } there no other annotation i've put in stream , subject entity class link inventory class. when run app, create following tables: inventory stream subject inventory_stream inventory_subject (inventory_stream , inventory_subject, 2 tables created automatically, don't k...
Read more

python - Mongodb How to add addtional information when aggregating? -

i beginner , wrote line pipeline works, wanna add other information output, screen name , or number of tweets.i tried add under $group gave me syntax error everytime here pipeline: def make_pipeline(): # complete aggregation pipeline pipeline = [ { '$match': { "user.statuses_count": {"$gt":99 }, "user.time_zone": "brasilia" } }, { "$group": { "_id": "$user.id", "followers": { "$max": "$user.followers_count" } } }, { "$sort": { "followers": -1 } }, { "$limit" : 1 } ]; i using on example : { "_id" : objectid("5304e2e3cc9e684aa98bef97"), "text" : "first week of school on :p", "in_reply_to_status_id" : null, "retweet_count" : null, ...
Read more

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more