Hi,
I have a piece code that does SaveAs at the end of the program but it always tells me that file already exists and do you want to replace it.
I do not want that message to pop up and i want program to just overwrite it.
I had in the code both application.displayalerts and screenupdating set to false but still does not work.
I had the same code in another macro and it works fine.
Any ideas why this does not work. I am exahausted trying to figure this out.
please help
thank you
SaveAs question - Excel
Hot Topic
- What's your experience with the buddy program?
- cannot save file as
- Installing Business Scorecard Manager 2005 on a SQL Server 2005 instance.
- experiences with MS software testing?
- Lack of mouse scroll wheel support in VBA
- Changing Outlook email settings with VBE
- a big thank you!
- Novice VBA User needs help with using data from input box for Cell Find
- oledb connaction display xml
- format field
Microsoft ISV
- Can anyone download VS Team Edition?
- transmitting a string from excel to com1
- Read data from excel through VB.NET
- VBA SDK with VB.NET(2005)
- Creating AD user with profile & home directories
- Error On Execution
- VS2005: Any more CTP or RC builds to come?
- VS Code Editor
- Openening an Excel file from within Project
- Does the VBA save word as XML???
SaveAs question - Excel
Teddy Chen
Delete the file before you do the SaveAs.
Dim filesys, file as Object
Set filesys= CreateObject("Scripting.FileSystemObject")
Set file = filesys.GetFile("C:\Readme.txt")
file.Delete
Anjan Das
Hey
I think this piece of code will help you
Function SaveWorkbook(Myfile As String, Optional DisplMess As Boolean) As Boolean
'Saving of Myile, optional displaying an errormessage
Dim Message As String
Dim MyErr As Label
On Error GoTo MyErr 'Init of Err.routine
ActiveWorkbook.SaveAs Filename:=Myfile, _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
MyErr: 'MyErrorsroutine.
Select Case Err 'Evaluation of Err
Case 0 'Everything is OK
SaveWorkbook = True
Case Else 'There is an error
Message = "File not saved: " & Myfile & Chr(10) _
& Trim(Str(Err) & ": " & Err.Description)
End Select
If Message = "" Then Message = "Saved: " & Myfile
If DisplMess Then MsgBox Message
End Function
'Testing this code with 'Test'
Sub Test()
Dim Tmp As Boolean
Application.DisplayAlerts = False 'You don't get alerts/questions
'If you suppress the row above, you will get alerts/questions
Tmp = SaveWorkbook("C:\Temp\MyTestBook", True)
Application.DisplayAlerts = True 'Reset Alerts
If Tmp Then
MsgBox "Everything OK"
Else
MsgBox "Do your Err handling"
End If
End Sub
Kind Regards and much suc6
FiftyFive
abhishek.cbsa
I am moving this thread to the VBA forum since the original post was made to the Visual Studio Tools for Office forum.
Thanks,
Rachel
Minh Tran
Sorry, i am using officeXP -excel
Danny_40
I am assuming you are using VSTO. Which Office application are you working with
Displayalerts should work in this scenario. So, I would first make sure that it is getting set on the right instance of the application. Perhaps trying being explicit (ie, myxlapp.displayalerts = false)
If that doesn't work, why don't you send a snippit showing what you're tyring to do.
Thanks,
Rachel