Browse for a file to import and capture its path

[Mindzeye]

Hello all
I am new to this forum and hope I am in the right place to ask a question.
I am creating a database where I want to be able to browse to a file, select it and import it into a table. I am able to do this. BUt what I want to do is create a log of these imports and haveit include the path of where the file was inported from.
The field I am having issues with is the capturing the path of the file.
Below is what I am using to browse and import a file.

Private Sub Command5_Click()
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
strFileName = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "oshpd", strFileName, True
Else
Exit Sub
End If
End With
End Sub

 

[pbaldy]

I moved this to a more appropriate forum. You can create a table with the desired fields, and populate it with either the AddNew method of a recordset or executing an append query. Either way, you can use your file name variable to store the path.

 

[Mindzeye]

Thanks for putting this is the right place Paul.
I am able to browse and get the data into the table. I want to create a table to track where the data is being imported from. I would presume that the best place to grab that is as I am importing it.

Forgive me, I am new to coding.

 

[pbaldy]

I cut a process in a db I have open to the essentials of what you want. Cut out the parts you need and add to your code:

Private Sub cmdApprove_Click()
  Dim strSql                  As String
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  strSql = "SELECT * FROM YourNewTableName WHERE 1=0"
  Set rs = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

  rs.AddNew
  rs!PathName = strFileName
  rs.Update

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description & " in cmdApprove_Click "
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

 

[Mindzeye]

Thanks Paul I have it figured out now

 

[pbaldy]

No problemo.