Forum Home
Press F1
Thread ID: 18117	2002-04-18 04:44:00	Importing ASCII files in Excel	Guest (0)	Press F1

Post ID	Timestamp	Content	User
44430	2002-04-18 04:44:00	Hi there, I am using a simple VB routine to automatically load ASCII files in Excel. It works but it comes up with a text import wizard that asks about what kind of delimiter is in the file; this is always a comma as in a classic *.csv file and I'd like to make this explicit in the routine so as not to be asked about the delimiter everytime. Does anybody know how to do it? Thank you, Marco PS: the routine is as follows: Sub ImportTextFile() Dim DestBook As Workbook, SourceBook As Workbook Dim DestCell As Range Dim RetVal As Boolean ' Turn off screen updating. Application.ScreenUpdating = False ' Set object variables for the active book and active cell. Set DestBook = ActiveWorkbook Set DestCell = ActiveCell ' Show the Open dialog box. RetVal = Application.Dialogs(xlDialogOpen).Show('*.att') ' If Retval is false (Open dialog canceled), exit the procedure. If RetVal = False Then Exit Sub ' Set an object variable for the workbook containing the text file. Set SourceBook = ActiveWorkbook ' Copy the contents of the entire sheet containing the text file. Range(Range('A1'), Range('A1').SpecialCells(xlLastCell)).Copy ' Activate the destination workbook and paste special the values ' from the text file. DestBook.Activate DestCell.PasteSpecial Paste:=xlValues ' Close the book containing the text file. SourceBook.Close False End Sub	Guest (0)
44431	2002-04-18 21:15:00	As the file type *.att is not recognised by Excel then the wizard is called. I assume once the file is imported all the data is spread across column A and has to be subsequently parsed. One way around this is to record a macro while manually importing the file and when the wizard appears amending the column widths to parse the text. The code generated can be incorporated into your existing macro. This has worked for me.	Guest (0)
44432	2002-04-18 22:07:00	Hello Russell, I did record a new macro and yes, I can import a file without going through the 'text import Wizard' but in this way I have lost the 'Open file' window. The *.att extension files have the same structure as a normal *.csv and they are properly loaded in Excel once I specify the comma delimited text format. Following your procedure I found out that the VB code to specify this is DataType:=xlDelimited and Comma:=True I guess I can put this into the original macro and it should work. Thank you very much, Marco	Guest (0)
1