Macro/VBAProject to copy entire .CSV to Sheet2 of active Workbook (xlsm)?

[SomethingMore]

So here's the situation...

I have an Excel 2007 .xlsm file that only contains Sheet1.
I have two options, but the outcome has to be the same...

Options
Option 1: Have a "Load CSV" button on Sheet1. When clicked, the user needs to find a .csv file and Open it.
Option 2: Press Alt+F8 to run Macro. The user needs to find a .csv file and Open it.

Desired Outcome
Copy entire contents of .csv file to Sheet2 of the .xlsm file that is running the code.

Undesired Outcome
I don't want the .csv to be loaded to a new Workbook or any other existing Workbook. I have found the code for this, but this is not functional to me.
I don't want Sheet2 to be renamed in any way. It should always be called Sheet2. This is because Sheet1 will have some conditional formatting based on the data in Sheet2.

Help!

 

[SomethingMore]

edit:

Here's what I get when I 'Record Macro' in Excel 2007...

Sub Macro1()
'
' Macro1 Macro
'

'
Workbooks.Open Filename:="C:\Documents and Settings\dan\Desktop\PASS.csv"
Cells.Select
Selection.Copy
Windows("test code.xlsm").Activate
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub

Workbooks.Open Filename:="C:\Documents and Settings\dan\Desktop\PASS.csv"
should give me an option to choose the .csv file as opposed to autoloading 'PASS.csv'

Windows("test code.xlsm").Activate
should go back to the original .xlsm file containing the macro... as opposed to auto-selecting 'test code.xlsm'

How...?

Thanks!

 

[Kenneth Hobson]

This doesn't insert the first line of the csv as it is typically the fieldnames. It is easily added though.

'Similar to http://vbaexpress.com/forum/showthread.php?t=21654
Sub main()
    Sheets.Add
    Cells.Clear
    
    Read_CSV_File Application.GetOpenFilename _
      (FileFilter:="CSV files (*.csv), *.csv", _
      Title:="Select CSV File", _
      MultiSelect:=False), _
      ActiveSheet.Name
End Sub

Sub Read_CSV_File(strCSVFile As String, aSheetName As String)
'Requires Reference: Microsoft ActiveX Data Objects Library xx
    Dim strPathtoTextFile As String
    Dim objRecordset As ADODB.Recordset
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim sConnection As String
    Dim sSQL As String
    
    Set ws = ThisWorkbook.Worksheets(aSheetName)
    
    strPathtoTextFile = ThisWorkbook.Path & "\"
    
    sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & strPathtoTextFile & ";" & _
              "Extended Properties=""text;HDR=Yes;FMT=CSVDelimited"""
              
    sSQL = "SELECT * FROM " & strCSVFile
    
    Set objRecordset = New ADODB.Recordset
    
    objRecordset.Open sSQL, sConnection, adOpenStatic, adLockReadOnly, adCmdText
   
    If Not objRecordset.EOF Then
        LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        ws.Cells(LastRow + 1, 1).CopyFromRecordset objRecordset
    End If
    
    objRecordset.Close
    Set objRecordset = Nothing
End Sub