A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Active Workbook



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 06:45 PM posted to microsoft.public.excel.misc
Kim
external usenet poster
 
Posts: 820
Default Active Workbook

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you
Ads
  #2  
Old June 1st, 2010, 07:15 PM posted to microsoft.public.excel.misc
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Active Workbook

Correct, when you save-as, you are changing the name of active file, not
creating a copy. Perhaps this code?

Sub ReName()
xName = ActiveWorkbook.FullName
'Change this to something appropriate
ThisWorkbook.SaveAs "New Name"
'Opens original file
Workbooks.Open (xName)
'Close the copied file
ThisWorkbook.Close
End Sub


--
Best Regards,

Luke M
"Kim" wrote in message
...
Using Excel 2007, I have a macro where I use a workbook, then save as a
new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as
a
copy and keep original file open.

Thank you



  #3  
Old June 1st, 2010, 07:20 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Active Workbook

Look at SaveCopyAs in VBA's help.

Kim wrote:

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you


--

Dave Peterson
  #4  
Old June 1st, 2010, 07:45 PM posted to microsoft.public.excel.misc
Gary Brown[_6_]
external usenet poster
 
Posts: 61
Default Active Workbook

You need to re-open the original.
Prior to save as, put the current name in a variable.
After the save as, open the original using that variable.

Something like...

strOriginalName = ActiveWorkbook.FullName
....save as code
Workbooks.Open Filename:= strOriginalName

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Kim" wrote:

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you

  #5  
Old June 1st, 2010, 07:59 PM posted to microsoft.public.excel.misc
Harald Staff[_2_]
external usenet poster
 
Posts: 198
Default Active Workbook

SaveCopyAs

HTH. Best wishes Harald

"Kim" wrote in message
...
Using Excel 2007, I have a macro where I use a workbook, then save as a
new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as
a
copy and keep original file open.

Thank you


  #6  
Old June 1st, 2010, 09:20 PM posted to microsoft.public.excel.misc
Kim
external usenet poster
 
Posts: 820
Default Active Workbook

I have another kink in this.
I am working in 2007, but original file was .xls and saved as .xls. With
the SaveAs I originally had FileFormat:=xlNormal, but this returned "argument
not found".


"Dave Peterson" wrote:

Look at SaveCopyAs in VBA's help.

Kim wrote:

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you


--

Dave Peterson
.

  #7  
Old June 1st, 2010, 10:08 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Active Workbook

SaveCopyAs doesn't give you a choice of what type of file you're saving as.
It's saving as an exact copy of the workbook file you're saving.

If you need to change fileformat, then .savecopyas isn't going to do it by
itself.

You could:
savecopyas
open that copy you just saved.
save it in the format you want.
close that file.

And the original workbook stays untouched.



Kim wrote:

I have another kink in this.
I am working in 2007, but original file was .xls and saved as .xls. With
the SaveAs I originally had FileFormat:=xlNormal, but this returned "argument
not found".

"Dave Peterson" wrote:

Look at SaveCopyAs in VBA's help.

Kim wrote:

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you


--

Dave Peterson
.


--

Dave Peterson
  #8  
Old June 1st, 2010, 11:08 PM posted to microsoft.public.excel.misc
Kim
external usenet poster
 
Posts: 820
Default Active Workbook

Actually, it is the same format that it started in, so I don't know what the
problem is.

"Dave Peterson" wrote:

SaveCopyAs doesn't give you a choice of what type of file you're saving as.
It's saving as an exact copy of the workbook file you're saving.

If you need to change fileformat, then .savecopyas isn't going to do it by
itself.

You could:
savecopyas
open that copy you just saved.
save it in the format you want.
close that file.

And the original workbook stays untouched.



Kim wrote:

I have another kink in this.
I am working in 2007, but original file was .xls and saved as .xls. With
the SaveAs I originally had FileFormat:=xlNormal, but this returned "argument
not found".

"Dave Peterson" wrote:

Look at SaveCopyAs in VBA's help.

Kim wrote:

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you

--

Dave Peterson
.


--

Dave Peterson
.

  #9  
Old June 1st, 2010, 11:33 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Active Workbook

Don't use the fileformat parm in your .savecopyas line.

If that doesn't help, then you'll have to share your code.

Kim wrote:

Actually, it is the same format that it started in, so I don't know what the
problem is.

"Dave Peterson" wrote:

SaveCopyAs doesn't give you a choice of what type of file you're saving as.
It's saving as an exact copy of the workbook file you're saving.

If you need to change fileformat, then .savecopyas isn't going to do it by
itself.

You could:
savecopyas
open that copy you just saved.
save it in the format you want.
close that file.

And the original workbook stays untouched.



Kim wrote:

I have another kink in this.
I am working in 2007, but original file was .xls and saved as .xls. With
the SaveAs I originally had FileFormat:=xlNormal, but this returned "argument
not found".

"Dave Peterson" wrote:

Look at SaveCopyAs in VBA's help.

Kim wrote:

Using Excel 2007, I have a macro where I use a workbook, then save as a new
named workbook. I want to keep the original workbook open. What code
language will accomplish this? Even manually, I do not see how to save as a
copy and keep original file open.

Thank you

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.