Unprotect workbook and merge

[Boss]

Hi,

I got the code from http://www.rondebruin.nl/

Sub CombineAll()
Dim FilesToOpen
Dim x As Integer

On Error GoTo ErrHandler
Application.ScreenUpdating = False

FilesToOpen = Application.GetOpenFilename _
(filefilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(x)

Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend


ExitHandler:
Application.ScreenUpdating = True
Sheets("start").Select

Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

This is excellent code and works fine... But the files which i am trying to
merge are protected. The workbook is protected in all files. How do i solve
this...

I tried...

Workbooks.Open Filename:=FilesToOpen(x), WriteResPassword:="password"
Workbooks.Open Filename:=FilesToOpen(x), Password:="password"

please help...
Thx!

 

[Jacob Skaria]

Try..

Workbooks.Open Filename:="c:\filename.xls", _
Password:="password", WriteResPassword:="password"

If this post helps click Yes

 

[Boss]

This does not work, I tried it before...
I need to unprotect the workbook...

Thx!

 

[Boss]

This does not work... I need to unprotect the workbook... Thx!

 

[Boss]

I got it...

ActiveWorkbook.Unprotect Password:="password"

thx!

 

[Jacob Skaria]

Hi

'To unprotect workbook try
ActiveWorkbook.Unprotect "password"

'If workbook is password protected (both passwords in one line)
Workbooks.Open Filename:="c:\filename.xls", _
Password:="password", WriteResPassword:="password"

If this post helps click Yes

 

[john]

This approach may solve the problem to unprotect the workbook.
Change "mypassword" for the correct password

Sub CombineAll()
Dim FilesToOpen As Variant
Dim WB As Workbook
Dim x As Integer
Dim passwrd As String

On Error GoTo ErrHandler


FilesToOpen = Application.GetOpenFilename _
(filefilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

passwrd = "mypassword"
x = 1
While x <= UBound(FilesToOpen)
Set WB = Workbooks.Open(Filename:=FilesToOpen(x), Password:="passwrd")

WB.Password = "passwrd"

Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend


ExitHandler:
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

Sheets("start").Select

Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

 

[john]

whoops - soory, typing error with workbook unprotect!

Sub CombineAll()
Dim FilesToOpen As Variant
Dim WB As Workbook
Dim x As Integer
Dim passwrd As String

On Error GoTo ErrHandler


FilesToOpen = Application.GetOpenFilename _
(filefilter:="Microsoft Excel Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

passwrd = "mypasswrd"
x = 1
While x <= UBound(FilesToOpen)
Set WB = Workbooks.Open(Filename:=FilesToOpen(x), Password:="passwrd")

WB.Unprotect Password:="passwrd"

Sheets().Move After:=ThisWorkbook.Sheets _
(ThisWorkbook.Sheets.Count)
x = x + 1
Wend


ExitHandler:
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

Sheets("start").Select

Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub