This is rough code but it does do what I want to a point . I would like to select a range through the application.inputbox function and collect the first cell in the range and the last cell in the range and pass this to the TransferSpreadsheet function.

Any help with this would be greatly appreciated!!

Private Sub cmdGo_Click()

Dim xlApp As Object
Dim r As Object

Set xlApp = CreateObject("Excel.Application")
Set xlApp = GetObject("c:\ET.xls")

xlApp.Application.Visible = True
xlApp.Parent.Windows(1).Visible = True
'Excel.Worksheets("Sheet1").Activate

Set r = Excel.Application.InputBox(prompt:="Please select a range of cells.", Type:=8)
'I thought to use + 62 here to dump both values into an array but I can't find any press on how to access that array either

DoCmd.TransferSpreadsheet acImport, 8, "Sheet2", xlApp, , Range:=(r.Values)

'Set xlApp = Nothing ' Release reference to the application and spreadsheet.
End Sub

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Sheet2", "ET.xls", HasFieldNames:=True, _
Range:="Sheet1!b11:f522"