VBA to copy cell from opened or closed workbook into another workbook

Hello,
I have the below code which copied cells from Phase3 WP6.xls into another file. It works perfectly when the Phase3 WP6.xls is not open. When I open it before I run the macro it does not work. Could you please help me to change it so it works no matter if the Phase3 WP6.xls is open and used by other users or closed. I would appreciate your help.
Many thanks,


Workbooks.Open Filename:="\\zzz\Phase3 WP6.xls", Password:="gold", ReadOnly:=True
With GetObject("\\zzz\Phase3 WP6.xls")
ThisWorkbook.Sheets("Sheet1").Range("A549:CS550").Value = .Sheets("WP6").Range("A75:CS76").Value

.Close 0
End With

Hi megtoma,

Try the following code and/or the attached files which implement the code.

Please Login or Register  to view this content.

Lewis

Hi,
The files you attached work perfectly. However, I must be doing sth wrong when I run the macro on my files...I changed the line: #Const megtoma = False to: #Const megtoma = True
and it still asks me for the password...

Hi,

You aren't doing anything wrong. The GetObject() construct has no way of obtaining a password when the file is closed. There may be workarounds (such as SendKeys or SendKeys alternatives that may allow the password to be pro grammatically entered).

By the way, the GetObject() construct actually opens the Excel Workbook as an Excel file, This was the first time I have seen GetObject(), and originally thought it was a magic elixir that would allow access to closed Excel files, but that is not the case.

There are alternatives that access Excel files, without opening Excel (which can be useful with very large Excel files) such as:
a. ExecuteExcel4Macro() - Reference: http://msdn.microsoft.com/en-us/libr...ice.11%29.ASPX and
b. ADO ( ActiveX Data Objects ) - Reference: http://www.xtremevbtalk.com/showthre...618#post969618

I will try to do some research in the next couple of weeks and upload updates if and when I am successful.

Lewis