vba - Concatenating in Excel For Loop to make batch file of commands -

vba - Concatenating in Excel For Loop to make batch file of commands -

consider scenario: have client has 2 network shares on server. each share contains identical list of folders representing each project (one current data, , other share archived data). each project folder contains matching set of folders , subfolders need individual permissions assigned. of course of study projects added , removed time time.

i'd maintain list of projects in excel along list of permissions need set each project folder subfolders. thought lay out commands in excel 1 per row each row representing subfolder. utilize loops duplicate these commands adding on shared folder , project folder directory path. output batch file run time permissions needed changed or updated.

edit: i've never used vba in excel, , after searching can't seem find how utilize variables in loop alter cells in sheet referenced. it's been while since wrote program, when programming in c remember beingness able utilize variables loops reference cells in list/table. loop went through iterations variables count changing cell in list referencing. set in excel terms want variable each loop row number, , statically assign column letter since that's not changing each iteration.

each time loop runs want concatenate series of cells text string output new line of text file. concatenation tie cells remain same during each loop, , other cells changing variables incremented. illustration want concatenate cells a1, b1, ci, dj, e1, fk i, j, , k represent numeric value of integer used count iterations of each loop.

can tell me proper syntax accomplish concatenation variable cell assignments? here's excerpt code.

for = 2 numberofsharedfolders 'loops every subfolder (project folder) in shared folder needs permissions set j = 2 numberofsubfolders1 'loops every entry of permissions on final folders k = 2 numberofsubfolders2 concatenateddatastring = activesheet.range("a1") & activesheet.range("b1") & activesheet.range("c"i) & activesheet.range("d"j) write #1, concatenateddatastring next k next j next

.b3 not method or property of activesheet object.

i suspect intend activesheet.range("b3")

there may farther errors, , in fact next 2 lines can expected raise same error.

there may more beyond that, instance note typo in statement raise 424 object required error (because appleication not object, treated undefined/undeclared variable empty value):

filepath = appleication.defaultfilepath & "icacls commands.bat"

you can utilize option explicit @ top of each module help regulate against typos (this raise compile error undefined variable, , typo interpreted undefined variable.

for remainder of questions (really asking 6 broad questions) seek reply in brief, not entertain protracted q&a or give-and-take in comments here. if have specific questions, each should asked (after have done due diligence in searching/troubleshooting/etc.) individually.

how supposed concatenate in loop?

no thought mean. concatenate in loop appending value existing string. utilize & operator instead of + operator, because latter can confused non-string info (i.e., "hello" & 1 not raise error, "hello" +1 raise mismatch).

dim integer dim s string s = "some words" = 1 10 s = s & some_other_variable next

in fact, code not concatenating string it's adding integers:

concatenateddatasting = + j + k

i, j, , k integer data. not creating string (or if is, coerced/implied string representing numeric sum of operation).

is there improve way set variables for loops? have cell totaling size of each column using countif(d:d,"*"), , assigning variable.

what variable? don't see reference either column d or countif function in code above.

what best way reference info in sheet? range()? currentsheet.range()?

this depends on mean "data in sheet". unclear, broad answer.

how execute program? clicking play in developer console?

from developer ribbon, macros menu, take macro thisworkbook (or whichever book resides in). run. or console/vbe, press f5 or "run" button.

how know text file going end up? default location documents?

it end here, 1 time right typo:

application.defaultfilepath

you can see is: msgbox application.defaultfilepath

excel vba excel-vba batch-file icacls