c# - Text replace in VBA code of Excel files -

-

we have several tens of macro enabled excel files, each of contains few vba modules , in each of modules there sql server names , userid/passwords of sql login.

i wonder if write kind of c# utility loads files 1 one , either .net-office interop. or other approach replace strings else... because have repoint vba macros server name , use sql login name , password... wouldn't replacement hand :( :( :(

thanks!

to begin with

sorry taking time in posting creating ui not helps else comes looking same functionality.

you need first enable trust access vba project object model

open excel , click on file tab | options | trust center | trust center settings | macro settings

enable macro , click on trust access visual basic projects

enter image description here

next in vba editor

click on tool | options , under "editor" tab select checkbox require variable declaration

enter image description here

next download sample file here , press run button in sheet1 launch userform shown below.

simple select folder has only excel files. enter relevant info , click on start replace , done :)

enter image description here

code used

sheet1 code area

option explicit  private sub commandbutton1_click()     userform1.show end sub

userform code area

option explicit  private sub commandbutton1_click()     dim ret     ret = browseforfolder     if ret = false exit sub     textbox1.text = ret end sub  private sub commandbutton3_click()     on error goto whoa      dim wb workbook     dim strpath string, strfile string     dim strtoreplacewith string, strtoreplace string     dim long, j long      dim vbe object      strpath = textbox1.text & "\"      strfile = dir(strpath)      while strfile <> ""         set wb = workbooks.open(strpath & strfile)          set vbe = activeworkbook.vbproject          if vbe.vbcomponents.item(1).properties("haspassword").value = false             if vbe.vbcomponents.count > 0                 = 1 vbe.vbcomponents.count                     vbe.vbcomponents.item(i).activate                      if vbe.vbe.codepanes.item(i).codemodule.countoflines > 0                         j = 1 vbe.vbe.codepanes.item(i).codemodule.countoflines                             if instr(1, vbe.vbe.codepanes.item(i).codemodule.lines(j, 1), textbox2.text, vbtextcompare)                                 strtoreplace = vbe.vbe.codepanes.item(i).codemodule.lines(j, 1)                                 strtoreplacewith = replace(strtoreplace, textbox2.text, textbox3.text, 1, 1, vbtextcompare)                                 vbe.vbe.codepanes.item(i).codemodule.replaceline j, strtoreplacewith                             end if                         next                     end if                 next             end if         end if          wb.close true          strfile = dir     wend  letscontinue:     application.screenupdating = true     exit sub whoa:     msgbox err.description     resume letscontinue end sub  '~~> function pop browse folder dialog function browseforfolder(optional openat variant) variant     dim shellapp object      '~~> create file browser window @ default folder     set shellapp = createobject("shell.application"). _     browseforfolder(0, "please choose folder", 0, openat)      '~~> set folder selected.  (on error in case cancelled)     on error resume next     browseforfolder = shellapp.self.path     on error goto 0      '~~> destroy shell application     set shellapp = nothing      select case mid(browseforfolder, 2, 1)     case = ":"         if left(browseforfolder, 1) = ":" goto invalid     case = "\"         if not left(browseforfolder, 1) = "\" goto invalid     case else         goto invalid     end select      exit function  invalid:     browseforfolder = false end function  private sub commandbutton4_click()     unload me end sub

more snapshots

enter image description here

file code needs replaced before macro run

enter image description here

after macro run

enter image description here

edit

alternative file download location

in case above wikisend link dies, file can downloaded here


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more