Ahah! I assumed that since this was on an Access help forum that's the technology you wanted to use!
Here's a snippet that I use in Excel, which gets you a filename.
Function GetFileToScan(DirName As String) As String
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = Application.DefaultFilePath + "\"
.Title = "Please select the directory to scan"
.Show
If .SelectedItems.Count = 0 Then
GetFileToScan= ""
Else
GetFileToScan= .SelectedItems(1)
End If
End With
End Function
Assuming then, that the file contains lines of delimited fields, you would move through the target spreadsheet, one row at a time, using the TextToColumns function.
Here's a sample that I managed to get by simply recording a macro as I stepped through the process (Good technique to learn!)
Sub TextToColumns()
'
' TextToColumns Macro
'
'
Range("A1").Select
Selection.TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True
End Sub
You'll need to do some learning on the TextToColumns function, but the skeleton of the code is right there.
HTH
Tony