Catherine
I have commented these code lines (prefixed with an '. these do not run they are just notes.
I have shortened the import code, removing the defaults the macro recorder does which are not necessary. I do not understand why you are deleting the columns as the 9's in the array mean they should not import at all??? (I have left the column deleting out therefore but could easily put it back.
I am taking a new sheet (not sheet1) and copying the code onto there (I could easily rename the sheet tab to sheet1 if you wanted?). I then delete the original import sheet. which as you were cutting the data seemed Ok anyhow.
If you have questions on the code or wish it modified then shout. It should now present you with a dialog to choose the file (restricted to *.txt files) and go from there. We could copy the code of the second macro into the first two and do everything in one fell swoop if that would help.
Anyhow, here's the code
Option Explicit
Sub importtextfile()
'
' importtextfile Macro
' Macro recorded 10/06/2010 by Catherine
'
'Set aside a variable for the name of the file
Dim sFileName As String
'Collect the file name. This does nothing, just collects the text of the filename
sFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
'this just checks if someone pressed cancel on the dialog
If sFileName = "False" Then Exit Sub
'This is your recorded code with the variable in place of teh hard-coded name
'and the defaults taken out (not needed)
'all the 9's are columns which shouldn't import.
With ActiveSheet.QueryTables.Add(Connection:=sFileName _
, Destination:=Range("A1"))
.Name = "abc"
.TextFileParseType = xlDelimited
.TextFileTabDelimiter = True
.TextFileColumnDataTypes = Array(1, 9, 9, 9, 9, 9, 1, 9, 1, 1, 1, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9 _
, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, _
9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
End With
End Sub
Sub Appendtxtdata()
'
' Appendtxtdata Macro
' Macro recorded 10/06/2010 by Catherine
'
'Resereves space for variables
Dim lLastRow As Long, lLastColumn As Long
Dim wksNew As Worksheet, wksCurrent As Worksheet
'Commissions and error handlers should anything fail
On Error GoTo ErrHandler
'With construct saves typing Object (in this case Application) on each line
'This statement stops the screen updating (flashing) and any warnings popping up
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
'If you have an object variable (see above) you need to set it. Now
'I can just refer to these variables and they will always point to
'that object, for example no matter what worksheet is active
Set wksCurrent = ActiveSheet
'This one adds a new sheet. I don't need to know the name as it will always be
'called sksNew to me in this code
Set wksNew = Worksheets.Add
'finds the last row and column in the data and then copies that range
'and pastes it in one operation and deletes the original sheet
With wksCurrent
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 1), .Cells(lLastRow, lLastColumn)).Copy Destination:=wksNew.Range("A1")
.Delete
End With
'Switches screen updating and alerts back on
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
'Exits the routine if successful otherwise it would run the error handler as
'the next line of code
Exit Sub
'Error handler should anything go wrong which pops a message box
'and resets stuff
ErrHandler:
MsgBox "Somthing went wrong", vbInformation + vbOKOnly, "Error"
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub