Unbound Text box Value to strPathFile variant in Access using Visual basic.

Question

I am in need of some help. I am trying to import an excel book, with different sheets into Access. The problem I am having is copying the link from the Textfilename to module1 so that it is somewhat automated. For the life of me, I can't figure it out. I think it is not really written to the text box is the problem, but not sure. Any help would be appreciated!

Thanks
Mike

Public Sub Import_File(Filename As String, tablename As String)

Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean  
Dim lngCount As Long  
Dim objExcel As Object, objWorkbook As Object  
Dim colWorksheets As Collection  
Dim Item As Variant  
Dim strPassword As String  
Dim strPathFile As Variant  
  
  
  
' Establish an EXCEL application object  
On Error Resume Next  
Set objExcel = GetObject(, "Excel.Application")  
If Err.Number <> 0 Then  
      Set objExcel = CreateObject("Excel.Application")  
      blnEXCEL = True  
End If  
Err.Clear  
On Error GoTo 0  
  
' Change this next line to True if the first row in EXCEL worksheet  
' has field names  
blnHasFieldNames = True  
  
' Replace C:\Filename.xls with the actual path and filename  
strPathFile = File  
  
' Replace passwordtext with the real password;  
' if there is no password, replace it with vbNullString constant  
' (e.g., strPassword = vbNullString )  
strPassword = vbNullString  
  
blnReadOnly = True ' open EXCEL file in read-only mode  
  
' Open the EXCEL file and read the worksheet names into a collection  
Set colWorksheets = New Collection  
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _  
      strPassword)  
For lngCount = 1 To objWorkbook.Worksheets.Count  
      colWorksheets.Add objWorkbook.Worksheets(lngCount).Name  
Next lngCount  
  
' Close the EXCEL file without saving the file, and clean up the EXCEL objects  
objWorkbook.Close False  
Set objWorkbook = Nothing  
If blnEXCEL = True Then objExcel.Quit  
Set objExcel = Nothing  
  
' Import the data from each worksheet into a separate table  
For lngCount = colWorksheets.Count To 1 Step -1  
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _  
            "tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames, _  
            colWorksheets(lngCount) & "$"  
Next lngCount  
  
' Delete the collection  
Set colWorksheets = Nothing  
  
' Uncomment out the next code step if you want to delete the  
' EXCEL file after it's been imported  
' Kill strPathFile  
  
  
End Sub  
  
  
Public Sub bntBrowse_Click()  
    Dim diag As Office.FileDialog  
    Dim Item As Variant  
    Dim File As Variant  
      
    Set diag = Application.FileDialog(msoFileDialogFilePicker)  
    diag.AllowMultiSelect = False  
    diag.Title = "Please select a excel spreadsheet"  
    diag.Filters.Clear  
    diag.Filters.Add "Excel Spreadsheets", "*.xls,*.xlsx"  
      
    If diag.Show Then  
        For Each Item In diag.SelectedItems  
           Me.txtFileName = Item  
        Next  
              
          
    End If  
      
End Sub  
  
Public Sub bntImportSpreadsheet_Click()  
    Dim FSO As New FileSystemObject  
    Dim File As Variant  
      
      
    If Nz(Me.txtFileName, "") = "" Then  
        MsgBox "Please select a file!"  
        Exit Sub  
    End If  
      
    If FSO.FileExists(Nz(Me.txtFileName, "")) Then  
        Module1.Import_File Me.txtFileName, FSO.GetFileName(Me.txtFileName)  
      
      
      
    Else  
  
        MsgBox "File Not found"  
    End If