Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro-import another xls file | Excel Discussion (Misc queries) | |||
Error in large file import macro | Excel Programming | |||
macro for import of csv .txt file? | Excel Programming | |||
macro to import file (help with a piece of code) | Excel Programming | |||
macro which import alfa*.txt file, how to ? | Excel Programming |