Excel Password and carving out Tab name

I'm in the process of setting up a Macro to open up all files in a directory and copy a certain tab from each into a combined file (merge them in one workbook). I have two problems. Firstly the files are password protected - So when I use this line it opens the file.

Set gwkbInputdata = Workbooks.Open(gRnCT_File_Loc & gsInputFileName)

However when it's password protected it fails. So I added the following to the end but it still fails.

Set gwkbInputdata = Workbooks.Open(gRnCT_File_Loc & gsInputFileName),Password = "openfile"

2nd issue - When I copy the tabs (sheets) in I want to name them with the file name I took them from. However the file name is too long - So I want to take the name up to the first space (e.g. "Test file May 13" = sheet name "Test"). How do I get this code to work.

Any help greatly appreciated.

Full code below:

* gRnCT_File_Loc = Directory location. * gsInputFileName = File Name.

Code to 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

For Each ws In ThisWorkbook.Worksheets
       If ws.Name <> gcsConfigSheetName Then ws.Delete
Next ws

Do
    On Error GoTo err:
        Set gwkbInputdata = Workbooks.Open(gRnCT_File_Loc & gsInputFileName)
    On Error GoTo 0

    On Error GoTo err1:
        With Sheets(gRnCT_Tab_Search)
    On Error GoTo 0

        End With

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

Answers


You are missing a : 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 could use split for your second problem using a space as your delimiter e.g.

Split(str, " ")(0)

where str is the original name of the file e.g. "Test file May 13"


For the 2nd question about getting the 1st part of the file name, try:

if(instr(gsInputFileName," ") > 0
   then short_file_name=left(gsInputFileName,instr(gsInputFileName," ")-1)
   else short_file_name=gsInputFileName
   endif

Need Your Help