excel - Automate the process of logging PDF files with associated information -

i making database has userform automate process of logging pdf files associated information.

for want have user enter in drawings information, browse folder drawings in, , have drawing files listed in excel information repeated each drawing/pdf entry.

how should code this?

this have (and doesn't work):

sub button2_click()     userform1.show end sub  sub startit()     dim filesystem object     dim hostfolder string     dim xdirect$, xfname$, initialfoldr$      initialfoldr$ = "g:\scanneddwgarchive\transportation , buildingsdrawings\rocky harbour\"     application.filedialog(msofiledialogfolderpicker)         .initialfilename = application.defaultfilepath & "\"         .title = "please select folder list files from"         .initialfilename = initialfoldr$         .show         if .selecteditems.count <> 0             xdirect$ = .selecteditems(1) & "\"         end if     end      set filesystem = createobject("scripting.filesystemobject")      dofolder filesystem.getfolder(initialfoldr$) end sub  sub dofolder(folder)     dim subfolder     each subfolder in folder.subfolders         dofolder subfolder     next      = cells(rows.count, 21).end(xlup).row + 1     dim file     each file in folder.files         activesheet.hyperlinks.add anchor:=cells(i, 21), address:= _         file.path, texttodisplay:=file.name         = + 1         call textboxwrite     next end sub  **sheet 5 page in workbook working on. part of code never works** sub textboxwrite()     sheet5.range(i, 12) = jobnumtextbox.value     sheet5.range(i, 15) = towntextbox.value     sheet5.range(i, 16) = yeartextbox.value     sheet5.range(i, 18) = streettextbox.value     sheet5.range(i, 19) = phasetextbox.value     sheet5.range(i, 20) = cdtextbox.value end sub 

i can open folder, , have files written excel. , it. how can make work?

update: got work after of guidance , new found tutorials. here (not finished, it's functional)

sub button2_click() userform1.show end sub  sub startit()    dim filesystem object   dim hostfolder string dim xdirect$, xfname$, initialfoldr$    initialfoldr$ = "g:\scanneddwgarchive\transportation , buildings drawings\rocky harbour\" application.filedialog(msofiledialogfolderpicker)     .initialfilename = application.defaultfilepath & "\"     .title = "please select folder list files from"     .initialfilename = initialfoldr$     .show         if .selecteditems.count <> 0         xdirect$ = .selecteditems(1) & "\"         end if     end    set filesystem = createobject("scripting.filesystemobject")    dofolder filesystem.getfolder(initialfoldr$)  end sub  sub dofolder(folder)     dim subfolder     each subfolder in folder.subfolders         dofolder subfolder     next      dim integer                             ' declare variable     = cells(rows.count, 21).end(xlup).row + 1     dim file     each file in folder.files         activesheet.hyperlinks.add anchor:=cells(i, 21), address:= _         file.path, texttodisplay:=file.name         = + 1         call textboxwrite(i)                     ' pass argument      next end sub  sub textboxwrite(i integer)                   ' receive argument     worksheets("civil database").activate     dim row integer     row = - 1     activesheet.cells(row, 12) = userform1.jobnumtextbox.value     activesheet.cells(row, 15) = userform1.towntextbox.value     activesheet.cells(row, 16) = userform1.yeartextbox.value     activesheet.cells(row, 17) = userform1.desctextbox.value     activesheet.cells(row, 18) = userform1.streettextbox.value     activesheet.cells(row, 19) = userform1.phasetextbox.value     activesheet.cells(row, 20) = userform1.cdtextbox.value  end sub 

thank helping me!

you need pass i value called subroutine, textboxwrite(). right now, i has no value in scope since not global variable local one:

... sub dofolder(folder)     dim subfolder     each subfolder in folder.subfolders         dofolder subfolder     next      dim integer                             ' declare variable     = cells(rows.count, 21).end(xlup).row + 1     dim file     each file in folder.files         activesheet.hyperlinks.add anchor:=cells(i, 21), address:= _         file.path, texttodisplay:=file.name         = + 1         call textboxwrite(i)                     ' pass argument     next end sub  sub textboxwrite(i integer)                   ' receive argument     sheet5.range(i, 12) = jobnumtextbox.value     sheet5.range(i, 15) = towntextbox.value     sheet5.range(i, 16) = yeartextbox.value     sheet5.range(i, 18) = streettextbox.value     sheet5.range(i, 19) = phasetextbox.value     sheet5.range(i, 20) = cdtextbox.value end sub 

also, best practices in vba, use option explicit on top line outside subroutine or function. forces declare every variable used when compile code (debug \ compile) before macro execution regular practice. additionally, anticipate runtime errors on error handling. if incorporate err.number , err.description receive more detailed information on issue.