Get External Data Macro
 

I need a macro that does the following:
Inserts a new worksheet
Names the worksheet Sheet1 (or any other name)
Prompts for Get External Data (allowing the user to find the file)

Is there any way to do this? Or at least the last 2 parts

--
mkerste
-----------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...fo&userid=2568
View this thread: http://www.excelforum.com/showthread.php?threadid=39339

Get External Data Macro
 

I have put together a skeleton from code I have used and I tested it.
Make sure you add "Microsoft Scripting Runtime" to References in Visual
Basic Editor.

It pops up the open file dialog, creates a new worksheet "NewSheet",
reads a file and populates "NewSheet"


Sub Test()
ReadFile
End Sub

Public Function ReadFile() As Boolean

' Add Tools-Reference-Microsoft Scripting Runtime
Dim fsoFileSystemObject As FileSystemObject
Dim strFileName As String
Dim fFile As File
Dim tsTextStream As TextStream
Dim strLine As String
Dim wsNewSheet As Worksheet
Dim i As Integer

Set fsoFileSystemObject = CreateObject("Scripting.FileSystemObject")

strFileName = Application.GetOpenFilename()

If strFileName = "False" Then
MsgBox "Cancelled"
Else
Set wsNewSheet =
Worksheets.Add(After:=Worksheets(Worksheets.Count) )
wsNewSheet.Name = "NewSheet"
Set fFile = fsoFileSystemObject.GetFile(strFileName)
Set tsTextStream = fFile.OpenAsTextStream(ForReading)
i = 0
Do While tsTextStream.AtEndOfStream < True
strLine = tsTextStream.ReadLine
wsNewSheet.Range("A1").Offset(i, 0) = strLine
i = i + 1
Loop
tsTextStream.Close
End If

Set fsoFileSystemObject = Nothing

Application.StatusBar = "Finished reading file."

ReadFile = True

End Function






*** Sent via Developersdex http://www.developersdex.com ***