Hi again.
This macro will open a standard getopenfile dialog, and import the text file to a new worksheet named after the current date(mmddyy)
Will need a bit of customization, perhaps.<pre>
Sub Import1()
On Error Resume Next
Dim YourFile, RenameSheet
'if the file name is always the same and in the
'same path, replace the next two lines with:
'YourFile = Path and Filename Ex. "C:YourPathFileName.???"
YourFile = Application.GetOpenFilename
If YourFile = False Then Exit Sub
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = Format(Now, "mmddyy")
If Err.Number = 1004 Then
Err.Clear
RenameSheet = InputBox("You already have a sheet named " & _
Format(Now, "mmddyy") & "." & "Please enter another name.")
If RenameSheet = "" Then Exit Sub
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & YourFile, Destination:=Range("A2"))
.Name = Format(Now, "mmddyy")
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'change this array to match the number of columns in your text file
'and the data types
'replace the ones with constants if necc
'xlDMYFormat DMY date or 4
'xlDYMFormat DYM date or 7
'xlEMDFormat EMD date or 10
'xlMDYFormat MDY date or 3
'xlSkipColumn Skip column or 9
'xlTextFormat Text or 2
'xlYDMFormat YDM date or 8
'xlYMDFormat YMD date or 5
'xlGeneralFormat or 1
'for example: If the first column is a date, 2nd text,
'then the first two values of the following array would be
'your choice of 4,7,10,3,8,5 for the first and 2 for the second.
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub</pre>
This message was edited by TsTom on 2002-05-13 00:34