SaveAs: Keep original copy open?

[YellowLabPro]

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?

[VBA]
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

[/VBA]

[Bob Phillips]

Doug, Normally I would suggest using SaveCopyAs instead of SaveAs, but this will not save it as a text file.

[YellowLabPro]

Ok, Good to know.

[mdmackillop]

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]

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]

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?

[VBA]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"[/VBA]

[YellowLabPro]

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.

[vba]
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
[/vba]

[YellowLabPro]

I tried this, no luck....


[VBA]
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
[/VBA]

[Bob Phillips]

Untested, but this is the sort of thing you want

[vba]

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"
[/vba]

[YellowLabPro]

Xld,
Thanks for having a look at this. I get an error:
Cannot access 'Complete_Upload_File.xls'.

[VBA].SaveCopyAs sPath & "Complete_Upload_File.xls"[/VBA]
This file is open at the time...

[VBA]
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"
[/VBA]

[YellowLabPro]

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]

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]

Right, makes sense....

[YellowLabPro]

xld-
That is resolved now, renaming copy w/ a different name....

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]

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]

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.

[VBA]
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"
[/VBA]

[YellowLabPro]

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.

[vba]
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"
[/vba]