import txt file macro
 

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
Befo=Workbooks("mikes.xls").Sheets(1)
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete


End Sub

import txt file macro
 

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" wrote in message
...
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
Befo=Workbooks("mikes.xls").Sheets(1)
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete


End Sub

import txt file macro
 

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" wrote in message
...
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
Befo=Workbooks("mikes.xls").Sheets(1)
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete


End Sub