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

    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

    MsgBox ("No files or files in incorrect format in " & gRnCT_File_Loc)
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Exit Sub
    MsgBox ("Sheet " & gRnCT_Tab_Search & " doesn't exist in file " & gsInputFileName)
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Resume Next

End Sub


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

Need Your Help

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.