View Full Version : [SOLVED] Copy cells from password protected (to open) workbook into another file

08-07-2014, 06:25 AM

I have the below vba code which copies cells from one file into another. The thing is when I run it I need to press OK when I am prompted for a password. How can I alter the code so I do not need to press OK?

Sub Button1_Click()
Sheets("Sheet1").Range("H1").Value = Format(Now, "dd-mmm-yy hh-mm-ss")
Range("E1:E12").Value = "='\\zzz\www\[source.xls]Sheet1'!E1:E12"
Range("C1:C12").Value = "='\\zzz\www\[source.xls]Sheet1'!C1:C12"
Workbooks.Open Filename:="\\zzz\www\source.xls (file://\\zzz\www\source.xls)", Password:="Harrypotter2014"
ActiveWorkbook.Close False
End Sub

08-07-2014, 07:03 AM
Avoid those passwords:

Sub Button1_Click()
Thisworkbook.Sheets("Sheet1").Range("H1").Value = Format(Now, "dd-mmm-yy hh-mm-ss")

with Getobject("\\zzz\www\source.xls")
thisworkbook.sheets("Sheet1").Range("C1:C12").Value = .Sheets("Sheet1").range("C1:C12").value
thisworkbook.sheets("Sheet1").Range("E1:E12").Value = .Sheets("Sheet1").range("E1:E12").value
.close 0
end with
End Sub

08-08-2014, 02:26 AM
Thank you for your help. :biggrin: