Question on Macro

[santhosha]

Dear seniors,


I have a question on the macro events here. Below is my code. This performs the below Operations’

[pre]

Private Sub SaveAsTabDelimited(ByVal sFileName As String)

With ActiveWorkbook

.SaveAs Filename:=sFileName, FileFormat:=xlText, CreateBackup:=True

End With

End Sub

Public Sub test()

SaveAsTabDelimited "C:Userste160772DesktopToad TestTestsanthu.txt"

ActiveWorkbook.Close savechanges:=True

End Sub

[/pre]

1. It converts the excel file into tab delimited txt file

2. It creates a backup copy of the excel file

I wanted this action to be performed each day inorder the excel file to upload to Oracle tables on daily basis. The reason behind converting excel spreadsheet to tab delimited txt file is to preserve the format (To prevent the removal of leading zeros while exporting it to oracle)


But now I am trying to enhance this code so that the following actions should be performed without any glitch. I have tried to do this in all the possible ways .since I am very new to the programming I was not able to do this.


1. The code should convert the excel file to tab delimited save on my desktop (Hope that my current piece of code is sufficient for this action)

2. It should create a backup copy in a folder called “Repository” (A folder in a desktop). Each change to my workbook should result a backup copy with the version history if possible(Date and Time stamp in the file name)

3. the biggest problem associated with my code is that upon close, three separate dialogue boxes appear - one asking me if i want to save, the other asking me if i want to keep what i have copied in the clipboard, another one is replace the existing text file saved in the folder is there a way of answering these dialogue boxes (or suppressing them) from within the macro, so i do not need to manually click on yes or no each time the macro is run?

4. I have attached my macro to a “shape” in excel but my priority is to run my macro upon closing of my workbook. Every time when any changes happen to my workbook and when I save the workbook, it should create a tab delimited Text files which should replace the existing Txt File without any confirmation dialogue boxes.


Please help me on this. This is badly require for me


Wit million thanks


santhosha

 

[DaveTurton]

Application.DisplayAlerts = False should stop the dialogue boxes appearing for you


In ThisWorkbook module:

[pre]Private Sub Workbook_BeforeClose(Cancel As Boolean)

'your code here

End Sub

[/pre]

this will run your macro when the workbook closes

 

[santhosha]

Hi DaveTurton


Firstly thanks for your help. As you said i have inserted my code between the statements you mentioned above. but it did not work for me. I am not sure if i am doing it in correct way.


I created a new module and placed my piece of code between the statements you provided.Is that way it should be done?.Please suggest. if possible could you please tell me the complete lines of macro.


secondly coming to display alerts, it worked fine but only the problem is with the replacing existing file with new file.That alert still i am receiving.


Thanks in advance

santhosha

 

[DaveTurton]

Hi, it does not go into a new module. It needs to go into the ThisWorkbook module under your project