Excel Password and carving out Tab name -

i'm in process of setting macro open files in directory , copy tab each combined file (merge them in 1 workbook). have 2 problems. firstly files password protected - when use line opens file.

set gwkbinputdata = workbooks.open(grnct_file_loc & gsinputfilename) 

however when it's password protected fails. added following end still fails.

set gwkbinputdata = workbooks.open(grnct_file_loc & gsinputfilename),password = "openfile" 

2nd issue - when copy tabs (sheets) in want name them file name took them from. file name long - want take name first space (e.g. "test file may 13" = sheet name "test"). how code work.

any appreciated.

full code below:

* grnct_file_loc = directory location. * gsinputfilename = file name.

code date:  sub pulldata()  application.screenupdating = false application.displayalerts = false  set grwksconfigeration = sheets(gcsconfigsheetname) grnct_file_loc = grwksconfigeration.range(ct_file_loc) grnct_tab_search = grwksconfigeration.range(ct_tab_search) gsinputfilename = dir(grnct_file_loc) set gwkscurrent = activeworkbook  each ws in thisworkbook.worksheets        if ws.name <> gcsconfigsheetname ws.delete next ws      on error goto err:         set gwkbinputdata = workbooks.open(grnct_file_loc & gsinputfilename)     on error goto 0      on error goto err1:         sheets(grnct_tab_search)     on error goto 0          end  gsinputfilename = dir loop until gsinputfilename = vbnullstring  application.screenupdating = true application.displayalerts = true  exit sub  err:     msgbox ("no files or files in incorrect format in " & grnct_file_loc)     application.screenupdating = true     application.displayalerts = true     exit sub err1:     msgbox ("sheet " & grnct_tab_search & " doesn't exist in file " & gsinputfilename)     application.screenupdating = true     application.displayalerts = true     resume next  end sub 

you missing : after password

set gwkbinputdata = workbooks.open(grnct_file_loc & gsinputfilename),password = "openfile" 

should be:

set gwkbinputdata = workbooks.open(grnct_file_loc & gsinputfilename,password:="openfile") 

you use split second problem using space delimiter e.g.

split(str, " ")(0) 

where str original name of file e.g. "test file may 13"