Retreiving data from a closed Workbook, Pasting the data into an active Workbook, in VBA?

[Marsmuncher]

Hello, I am currently struggling with trying to get my VBA code to work how I want it. I am trying to achieve, by using a command button, for the user to select an Excel file which contains data, to then be pasted into another sheet of the workbook that is active. However the file selected from the user with the data inside must not open and instead purely just read the info. Below is the code I am using:

Private Sub CommandButton6_Click()

Dim intChoice As Integer
Dim strPath As String
Dim ws As Worksheet


Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
"*.xls", "*.xls")
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
"*.xlsm", "*.xlsm")
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show

If intChoice <> 0 Then

strPath = Application.FileDialog( _
msoFileDialogFilePicker).SelectedItems(1)

End If

With Workbooks(1).Worksheets(1).UsedRange
Sheets("Test").Select
.Formula = strPath
.Value = .Value

Application.ScreenUpdating = False
'Workbooks.Add("60v dual").Activate
'ActiveSheet.UsedRange.copy
'ActiveWorkbook.Close
'Windows("Automotive Product Database Mathew").Activate
'Sheets("Test").Select
'Worksheets("Test").Range("A1").Select
'ActiveSheet.paste
'Sheets("Database").Select
Application.ScreenUpdating = True

End With
End Sub

When I run this program however, instead of the data I want from the Excel file selected, instead I am stuck with pasting the file directory in the Workbook. I commented out the last part as this part of the code works, however it requires to open the document.

 

[Marsmuncher]

bump