import txt file macro

[Mike]

I am trying to write a macro that will be in file1.xls. I want to
import/open a text file and that to be the only worksheet in my file1.xls
workbook. I can't delete all worksheets in a workbook so I run the macro,
open the text file, copy the worksheet to file1.xls, delete "Sheet1" from
file1.xls -- when I do this I get a prompt that says something like there may
be data in the worksheet are you sure you want to delete it?

How do I avoid the prompt? or is there a better way to import to an empty
workbook? --- My code is at the bottom

Thanks for any help
Mike

__________________________________________
Sub ImportDataFile()

'Opens Input form and takes user input
Call GetUserInput

'Open file using the input of runname as file to open
Call ImportFile

End Sub


Sub ImportFile()
ChDir "C:\Documents and Settings\drewermr.LABS\Desktop\EPD\New from Judi"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\drewermr.LABS\Desktop\EPD\New from Judi\"
& UserInput.txtRunName.Text & ".txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)),
TrailingMinusNumbers _
:=True

' Sheets("Sheet1").Select
Windows(UserInput.txtRunName.Text & ".txt").Activate
Sheets(UserInput.txtRunName.Text).Select
Sheets(UserInput.txtRunName.Text).Copy
Before:=Workbooks("mikes.xls").Sheets(1)
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete


End Sub

[Mike]

Thanks for the tip.

Mike

"Tom Ogilvy" wrote:

> One way is to use code like this sample code at Chip Pearson's site:
>
> http://www.cpearson.com/excel/imptext.htm import/export text files
>
>
> you could also modify your code
>
> Application.DisplayAlerts = False
> ActiveWindow.SelectedSheets.Delete
> Application.displayAlerts = True
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to write a macro that will be in file1.xls. I want to
> > import/open a text file and that to be the only worksheet in my file1.xls
> > workbook. I can't delete all worksheets in a workbook so I run the macro,
> > open the text file, copy the worksheet to file1.xls, delete "Sheet1" from
> > file1.xls -- when I do this I get a prompt that says something like there
> may
> > be data in the worksheet are you sure you want to delete it?
> >
> > How do I avoid the prompt? or is there a better way to import to an empty
> > workbook? --- My code is at the bottom
> >
> > Thanks for any help
> > Mike
> >
> > __________________________________________
> > Sub ImportDataFile()
> >
> > 'Opens Input form and takes user input
> > Call GetUserInput
> >
> > 'Open file using the input of runname as file to open
> > Call ImportFile
> >
> > End Sub
> >
> >
> > Sub ImportFile()
> > ChDir "C:\Documents and Settings\drewermr.LABS\Desktop\EPD\New from Judi"
> > Workbooks.OpenText Filename:= _
> > "C:\Documents and Settings\drewermr.LABS\Desktop\EPD\New from
> Judi\"
> > & UserInput.txtRunName.Text & ".txt" _
> > , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
> _
> > xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
> > Semicolon:=False, _
> > Comma:=False, Space:=False, Other:=False,
> FieldInfo:=Array(Array(1,
> > 1), _
> > Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)),
> > TrailingMinusNumbers _
> > :=True
> >
> > ' Sheets("Sheet1").Select
> > Windows(UserInput.txtRunName.Text & ".txt").Activate
> > Sheets(UserInput.txtRunName.Text).Select
> > Sheets(UserInput.txtRunName.Text).Copy
> > Before:=Workbooks("mikes.xls").Sheets(1)
> > Sheets("Sheet1").Select
> > ActiveWindow.SelectedSheets.Delete
> >
> >
> > End Sub
> >
>
>
>