Unprotect workbook and merge

  • Thread starter Thread starter Boss
  • Start date Start date
B

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!
 
Try..

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

If this post helps click Yes
 
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
 
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
 
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
 
Back
Top