Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File open and close, get some data
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"): sheet1B4:D20: 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("B4:D20").Select Selection.Copy 'this doesn't work: Workbooks ("GJ2").Activate Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Thanks for helping! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File open and close, get some data
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. -- Regards, Tom Ogilvy "Gert-Jan" wrote: 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"): sheet1B4:D20: 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("B4:D20").Select Selection.Copy 'this doesn't work: Workbooks ("GJ2").Activate Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Thanks for helping! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File open and close, get some data
Thanks (good old) Tom, your reaction brought me to the solution ;-)
"Tom Ogilvy" schreef in bericht ... 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. -- Regards, Tom Ogilvy "Gert-Jan" wrote: 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"): sheet1B4:D20: 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("B4:D20").Select Selection.Copy 'this doesn't work: Workbooks ("GJ2").Activate Range("C1").Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Thanks for helping! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Close & save a file at set time only IF the file is open | Excel Programming | |||
Close Current File Then Open New file | Excel Programming | |||
OPen and close file | Excel Programming | |||
VBA - on a button event, open another closed file, post changes, close file | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |