Excel VBA: Designate Variable as Folderspec -
i'm having trouble this, minor issue , cant seem figure out how solve it.
i have vba script works in merging multiple workbook/worksheets 1. however, path of folder static path. i'd make variable can defined filedialog function.
here existing code file merger:
sub projectmerger() dim booklist workbook dim mergeobj object, dirobj object, filesobj object, everyobj object application.screenupdating = false set mergeobj = createobject("scripting.filesystemobject") 'change folder path of excel files here set dirobj = mergeobj.getfolder("c:\users\testuser\desktop\foldertest") set filesobj = dirobj.files each everyobj in filesobj set booklist = workbooks.open(everyobj)
here script use filedialog:
function getfolder() string dim fldr filedialog dim sitem string set fldr = application.filedialog(msofiledialogfolderpicker) fldr .title = "select folder" .allowmultiselect = false .initialfilename = application.defaultfilepath if .show <> -1 goto nextcode sitem = .selecteditems(1) end nextcode: getfolder = sitem set fldr = nothing end function
i think easy enough replace folder path string sitem work, doing results in errors. have designate sitem else in order use path? i'm not familiar how functions work vs regular subs.
you need capture selected path returned function & deal possibility of being empty if user cancelled dialog, e.g.
dim path string path = getfolder() if (path <> "") set dirobj = mergeobj.getfolder(path) .... .... else '// user cancelled end if
Comments
Post a Comment