File open and close, get some data

[Gert-Jan]

The situation:

In file test.xls

sheet1A1: in a formula there is the filename (including dir). For example:
C:\test\test.xls
sheet1A2: in a formula there is the filename of data.xls (including dir).
For example: C:\test\data.xls

The files are placed with a setup, so the dir "test" can be changed to
anything the user prefers.

In file data.xls (password protected, "ABC"):

sheet1B420: some data I want to copy. Destination: test.xls sheet2 C1:E16,
so: selection cell: C1.

I would like to know how to write macro that opens data.xls, copies the data
to test.xls, closes data.xls.

I have this (nor ready yet), but an error occured. The first part (opening
and copying) works fine.

Sub Macro1()
Dim GJ
GJ = Range("Sheet1!A1").Value
Dim GJ2
GJ2 = Range("Sheet1!A2").Value
Workbooks.Open Filename:=GJ, Password:="XYZ"
Range("B420").Select
Selection.Copy
'this doesn't work:
Workbooks ("GJ2").Activate
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Thanks for helping!

 

[Tom Ogilvy]

Workbooks ("GJ2").Activate

should be

Workbooks(GJ2).Activate

Unless you want to activate a workbook named "GJ2.xls"

Also, if GJ2 holds the string C:\test\data.xls then you have a second
error. It would only work if GJ2 held the string "data.xls" and data.xls
is already open.

the simple answer would be to change the value in Sheet1!A2 to "Data.xls",
but I don't know what else you are using that value for.

 

[Gert-Jan]

Thanks (good old) Tom, your reaction brought me to the solution ;-)