Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Close
Hi everyone
I have a command button which takes the value of A20, opens another .xls file and pastes the value in A20 of the new file (the new file has been assigned to variable cd). It is then supposed to close and save the new file with the following code: Workbooks(cd).Close SaveChanges = True But it doesn't save the changes. I don't know if it is because the command button is in the first .xls file or not though. Any Ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Close
Hard to comment only seeing the one line of code but that line you posted is
not correct. You are missing the colon. Workbooks(cd).Close SaveChanges:= True So that line you posted will not even execute... Perhpas post all of your code so we can see what is going on... -- HTH... Jim Thomlinson "andim" wrote: Hi everyone I have a command button which takes the value of A20, opens another .xls file and pastes the value in A20 of the new file (the new file has been assigned to variable cd). It is then supposed to close and save the new file with the following code: Workbooks(cd).Close SaveChanges = True But it doesn't save the changes. I don't know if it is because the command button is in the first .xls file or not though. Any Ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Close
I always use set cd = workbooks.open(filename:=FName) cd.close Savechanges:=true Excel might not like CD because it is the command "Change Directory". Try a different name! "andim" wrote: Hi everyone I have a command button which takes the value of A20, opens another .xls file and pastes the value in A20 of the new file (the new file has been assigned to variable cd). It is then supposed to close and save the new file with the following code: Workbooks(cd).Close SaveChanges = True But it doesn't save the changes. I don't know if it is because the command button is in the first .xls file or not though. Any Ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Close
It was the colon missing that was the problem. Thanks very much. Here is all
of the code if you're interested. Private Sub CommandButton3_Click() Range("A20").Select Selection.Copy Workbooks.Open ("C:\Documents and Settings\Andrew\My Documents\Serious Stuff\TCSpreadsheets\Convoluted.xls") 'Opens Convoluted cd = ActiveWorkbook.Name ' Names Convoluted "cd" for closure later ActiveSheet.Range("A20").Select ActiveSheet.Paste Application.CutCopyMode = False Workbooks(cd).Close SaveChanges:=True ' Closes Convoluted and saves (cd) End Sub Thanks again for your help I am very new to this and I am just basically learning by recording macros and figuring out the code. "Jim Thomlinson" wrote: Hard to comment only seeing the one line of code but that line you posted is not correct. You are missing the colon. Workbooks(cd).Close SaveChanges:= True So that line you posted will not even execute... Perhpas post all of your code so we can see what is going on... -- HTH... Jim Thomlinson "andim" wrote: Hi everyone I have a command button which takes the value of A20, opens another .xls file and pastes the value in A20 of the new file (the new file has been assigned to variable cd). It is then supposed to close and save the new file with the following code: Workbooks(cd).Close SaveChanges = True But it doesn't save the changes. I don't know if it is because the command button is in the first .xls file or not though. Any Ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Close
CD is the old DOS command.
ChDir is the VBA command to change directory. Joel wrote: I always use set cd = workbooks.open(filename:=FName) cd.close Savechanges:=true Excel might not like CD because it is the command "Change Directory". Try a different name! "andim" wrote: Hi everyone I have a command button which takes the value of A20, opens another .xls file and pastes the value in A20 of the new file (the new file has been assigned to variable cd). It is then supposed to close and save the new file with the following code: Workbooks(cd).Close SaveChanges = True But it doesn't save the changes. I don't know if it is because the command button is in the first .xls file or not though. Any Ideas? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.Close
this approach may do what you want.
Private Sub CommandButton3_Click() Dim cd As Workbook Dim rng1 As Range Set rng1 = ActiveSheet.Range("A20") Set cd = Workbooks.Open("C:\Documents and Settings\Andrew\My Documents\SeriousStuff\TCSpreadsheets\Convoluted.x ls") 'Opens Convoluted rng1.Copy cd.Worksheets("Sheet1").Range("A20") '<<change sheet name as required cd.Close True ' Closes Convoluted and saves Application.CutCopyMode = False End Sub -- jb "andim" wrote: It was the colon missing that was the problem. Thanks very much. Here is all of the code if you're interested. Private Sub CommandButton3_Click() Range("A20").Select Selection.Copy Workbooks.Open ("C:\Documents and Settings\Andrew\My Documents\Serious Stuff\TCSpreadsheets\Convoluted.xls") 'Opens Convoluted cd = ActiveWorkbook.Name ' Names Convoluted "cd" for closure later ActiveSheet.Range("A20").Select ActiveSheet.Paste Application.CutCopyMode = False Workbooks(cd).Close SaveChanges:=True ' Closes Convoluted and saves (cd) End Sub Thanks again for your help I am very new to this and I am just basically learning by recording macros and figuring out the code. "Jim Thomlinson" wrote: Hard to comment only seeing the one line of code but that line you posted is not correct. You are missing the colon. Workbooks(cd).Close SaveChanges:= True So that line you posted will not even execute... Perhpas post all of your code so we can see what is going on... -- HTH... Jim Thomlinson "andim" wrote: Hi everyone I have a command button which takes the value of A20, opens another .xls file and pastes the value in A20 of the new file (the new file has been assigned to variable cd). It is then supposed to close and save the new file with the following code: Workbooks(cd).Close SaveChanges = True But it doesn't save the changes. I don't know if it is because the command button is in the first .xls file or not though. Any Ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbooks close before | Excel Programming | |||
How to close All Workbooks | Excel Programming | |||
why do all excel worksheets/workbooks close when I close one? | Excel Discussion (Misc queries) | |||
close all other workbooks | Excel Programming | |||
Close all other workbooks but this one? | Excel Programming |