Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel macro: Make a macro delete itself?

Mar 28, 2005 12:30AM PST

I need help with a macro. The macro runs automatically, imports a .txt file, then saves the worksheet with a new name.

Problem is, when I open the new worksheet the macro tries to run again. Here's what I have:

Sub Auto_Open()
'
' Auto_Open Macro
'

'
Range("A2:F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;c:\xxxxxxx\xxxxxx\users\xxxxxxx\xxxx.txt", Destination:=Range("A2"))
.Name = "sy060z1_14"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 9, 9, 1, 1, 1, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.SaveAs Filename:="c:\xxxxxxx\xxxxxx\users\xxxxxxx\xxxx.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
Application.Quit
End Sub


I need to make the macro delete itself, then resave the file under a new name. I don;t know if this is even possible, since it's still running. Can someone help me out with this?

Discussion is locked

- Collapse -
Use the command line.
Mar 28, 2005 1:06AM PST
- Collapse -
Not sure you need the macro to delete itself...
Apr 30, 2005 12:47PM PDT

The other reply sounds like good info. Here is a macro that runs automatically, imports a .txt file and then saves itself as you requested. It also asks for a users input... but you can hardcode that if you like. You can put this code in a specific excel file or put in in your Personal.xls file. Hope this helps...

Sub Auto_Open()
'
' Auto_Open Macro
'

'


Application.DisplayAlerts = False
input1 = Application.InputBox(prompt:="What is your filename prefix?", Type:=2)

'quick error trap
'If user clicked cancel
If y_n_input = 2 Then Exit Sub

'hardcoded this
ChDir "C:\New_Folder"

Workbooks.OpenText Filename:="" & input1 & ".txt", Origin:= _
437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1)), _
TrailingMinusNumbers:=True

'insert statements here if you want to do stuff to file

ActiveWorkbook.SaveAs Filename:="C:\New_Folder\" & input1 & ".xls", _
FileFormat:=xlText, CreateBackup:=False

Application.DisplayAlerts = True
Application.Quit
End Sub