Excel Save Continued/...

[12ory]

Can anyone help by telling me what is wrong with my code. I am trying to automate saving an excel file once data has been input. The problem is I still get a command asking if I wish to save changes to the file that already exists even although i've just saved it as this filename!

strPath = "C:\Test\"
strFile = frmStartScreen.Text1.Text & ".xls"
strFileName = strPath & strFile
 
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
On Error GoTo ErrRoutine
If objExcel Is Nothing Then
Set objExcel = New Excel.Application
End If
 
If Text1.Text = "1" Then
With objExcel
.Workbooks.Open "C:\Test\Test.xls"
.Cells(1, 1) = Text3.Text
.Cells(2, 1) = Text2.Text
.Cells(3, 1) = Text4.Text
.Cells(4, 1) = Text5.Text
.Cells(5, 1) = Text6.Text
.Cells(6, 1) = Text7.Text
.Cells(7, 1) = Text8.Text
.Cells(8, 1) = Text9.Text
.ActiveWorkbook.SaveAs strFileName
.ActiveWorkbook.Close savechanges:=True
End With
End If

I receive the message:

A file named 'C:\Test\Test.xls' already exists in this location. Do you want to replace it?

with the options to say Yes, No, Cancel

Can anyone tell me why it is asking me to save again? Have I forgotten some code or can anyone suggest a fix?



Edit: Added [vbcode][/vbcode] tags for clairty. - Hack

[RobDog888]

When you do a .SaveAs your still left with the original wb in Excel and the new SaveAs wb. So the first issue is when you run this multiple times your file will alread exist and you will get the overwrite prompt (no way to surpress it without accepting the default of 'No') You could delete the preexisting file just before your .SaveAs so it will never prompt you for the overwrite. Second issue is the .SaveAs saving a duplicate file as whatever you name it. The solution is to tell Excel that the original copy has alread been saved witht the .Saved = True property assignment.

[12ory]

Can you perhaps suggest some code thiat would allow me to delete the file? I am pretty new to vb.

[RobDog888]

Sure, use the Kill function.

Kill "C:\Book1.xls"