PDA

View Full Version : SaveAs: Keep original copy open?



YellowLabPro
04-10-2007, 07:10 AM
I save a copy of the worksheet I am using w/ .SaveAs w/ a .txt file format.
This approach closes the original and keeps the newly saved copy open. I would prefer to keep the original open and close the newly created one.
Can I alter the existing code to instruct it not to close, inherent to .SaveAs, or put another line to open it back up?


Dim wbCUF As Workbook
Dim wsEC As Worksheet
Set wbCUF = Workbooks("Complete_Upload_File.xls")
Set wsEC = Worksheets("EC Products")
wbCUF.Save
wsEC.Activate
wsEC.Copy
Application.DisplayAlerts = False
With wsEC
.SaveAs Filename:="H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\Complete_Upload_File " & Format(Now, "m-dd-yy") & " @ " & Format(Now, "h_mm_ss am/pm") & ".txt", _
FileFormat:=xlTextMSDOS
End With
ActiveWindow.Close

Application.DisplayAlerts = True
End Sub

Bob Phillips
04-10-2007, 07:58 AM
Doug, Normally I would suggest using SaveCopyAs instead of SaveAs, but this will not save it as a text file.

YellowLabPro
04-10-2007, 08:01 AM
Ok, Good to know.

mdmackillop
04-10-2007, 10:23 AM
I would:
SaveCopyAs
Open the copied file
Run your SaveAs code
Kill the SaveCopyAs file.

A little laborious, but should achieve the desired result.

YellowLabPro
04-10-2007, 10:26 AM
Thanks MD,
That is what I was looking for. Kind of thought it was going to be that sort of thing w/ Bob's answer.

YLP

YellowLabPro
04-10-2007, 07:33 PM
Only problem I need to overcome: The file that is saved gets a timestamp. How do I get around that to know which file to reopen?

wbCUF.SaveCopyAs "H\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\Complete_Upload_File " & Format(Now, "m-dd-yy") & " @ " & Format(Now, "h_mm_ss am/pm") & ".xls"
Workbooks.Open "H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\Complete_Upload_File ????????????????.xls"

YellowLabPro
04-10-2007, 07:49 PM
I Got It!
I assigned the newly created workbook as a variable and then opened the original workbook and then closed the variable workbook....

But I cannot figure out how to close the variable workbook w/out it needing a response. I understand why, because it is reading
the code from the original workbook and the Alert is not getting this piece of code since it resides in the new workbook's code.


With wsEC
.SaveAs Filename:="H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\Complete_Upload_File " & Format(Now, "m-dd-yy") & " @ " & Format(Now, "h_mm_ss am/pm") & ".xls", _
FileFormat:=xlTextMSDOS
End With
Dim wbVar As Workbook
Set wbVar = ActiveWorkbook
Workbooks.Open "H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\Complete_Upload_File.xls"
wbVar.Save
wbVar.Close
Application.DisplayAlerts = False

YellowLabPro
04-10-2007, 08:03 PM
I tried this, no luck....



With wsEC
.SaveAs Filename:="H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\Complete_Upload_File " & Format(Now, "m-dd-yy") & " @ " & Format(Now, "h_mm_ss am/pm") & ".xls", _
FileFormat:=xlTextMSDOS
End With

Dim wbVar As Workbook
Set wbVar = ActiveWorkbook
Workbooks.Open "H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\Complete_Upload_File.xls"

With wbVar
.Save
.Close
.DisplayAlerts = False
End With

Bob Phillips
04-11-2007, 12:36 AM
Untested, but this is the sort of thing you want



Dim sPath As String
sPath = "H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\"
With ActiveWorkbook
.SaveCopyAs sPath & "Complete_Upload_File.xls"
.Save
.Close
End With
Workbooks.Open sPath & "Complete_Upload_File.xls"
With ActiveWorkbook
.SaveAs Filename:=sPath & "Complete_Upload_File " & Format(Now, "m-dd-yy") & " @ " & Format(Now, "h_mm_ss am/pm") & ".xls", _
FileFormat:=xlTextMSDOS
End With

Kill sPath & "Complete_Upload_File.xls"

YellowLabPro
04-11-2007, 04:38 AM
Xld,
Thanks for having a look at this. I get an error:
Cannot access 'Complete_Upload_File.xls'.

.SaveCopyAs sPath & "Complete_Upload_File.xls"
This file is open at the time...


Dim sPath As String
sPath = "H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\"
With ActiveWorkbook
.SaveCopyAs sPath & "Complete_Upload_File.xls"
.Save
.Close
End With
Workbooks.Open sPath & "Complete_Upload_File.xls"
With ActiveWorkbook
.SaveAs Filename:=sPath & "Complete_Upload_File " & Format(Now, "m-dd-yy") & " @ " & Format(Now, "h_mm_ss am/pm") & ".xls", _
FileFormat:=xlTextMSDOS
End With

Kill sPath & "Complete_Upload_File.xls"

YellowLabPro
04-11-2007, 04:42 AM
I would:
SaveCopyAs
Open the copied file
Run your SaveAs code
Kill the SaveCopyAs file.

A little laborious, but should achieve the desired result.

Did not realize "Kill" was a VBA method, thought it was slang for close.

Bob Phillips
04-11-2007, 04:49 AM
Xld,
Thanks for having a look at this. I get an error:
Cannot access 'Complete_Upload_File.xls'.


I guess that measn that is the name of the initial file. If so, use another name, and then re-open that saved file, and then kill it by that name.

YellowLabPro
04-11-2007, 04:51 AM
Right, makes sense....

YellowLabPro
04-11-2007, 05:40 AM
xld-
That is resolved now, renaming copy w/ a different name.... :doh:

But the second file, the Text file is not created.
It seems like the mainbook gets closed prior to making a second copy for the Text file, or maybe is not getting opened again.
I am stepping through now.

YellowLabPro
04-11-2007, 05:53 AM
xld,
After stepping through the code I think I see what is happening.
(Just because I see something does not mean that what is happening is what I see).
I don't know if this was intended, but if I close the workbook, then the code stops running here since the file is closed down here.

YellowLabPro
04-11-2007, 06:11 AM
xld,
Either the way I have altered the code trying to work through what is not working and this has messed w/ your solution, or I am back where I started.
The code below is what I have stepped through and changed.
Now the Text file is created but the original workbook "Complete_Upload_File.xls" is closed and the *.txt file remains open.
My apologies if I am missing what your and Mdmackillop's point is.


Dim sPath As String
sPath = "H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\"
With ActiveWorkbook
.SaveCopyAs sPath & "Complete_Upload_File_Copy.xls"
.Save
'.Close
End With
'Workbooks.Open sPath & "Complete_Upload_File.xls"
With ActiveWorkbook
.SaveAs Filename:=sPath & "Complete_Upload_File " & Format(Now, "m-dd-yy") & " @ " & Format(Now, "h_mm_ss am/pm") & ".xls", _
FileFormat:=xlTextMSDOS
End With

Kill sPath & "Complete_Upload_File_Copy.xls"

YellowLabPro
04-11-2007, 07:48 AM
I finally got it.
Bob, I could not get that method to work. But I did figure out that I needed to place the DisplayAlerts prior to closing the .txt file to advert the message box and not after.
The sPath was a nice touch, thanks.
Any feedback is welcome.


sPath = "H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\"
With wsEC
.SaveAs Filename:=sPath & "Complete_Upload_File " & Format _
(Now, "m-dd-yy") & " @ " & Format(Now, "h_mm_ss am/pm") & ".txt", _
FileFormat:=xlTextMSDOS
End With
Dim wbVar As Workbook
Set wbVar = ActiveWorkbook
Workbooks.Open sPath & "Complete_Upload_File.xls"
Application.DisplayAlerts = False
With wbVar
.Save
.Close
End With
Kill sPath & "Complete_Upload_File_Copy.xls"