Office.FileDialog & FileSystemObject Not defined (1 Viewer)

[smtazulislam]

Hello,
Any help will appreciate....

Private Sub cmdBrowser_Click()
'Dim fDialog As Office.FileDialog
Dim fDialog As Object
Dim item As Variant
Dim Ist As Variant
Dim FSO As New FileSystemObject

    'Set up File dialog box
    'Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    Set fDialog = Application.FileDialog(3)
            .AllowMultiSelect = False
            .Title = "Please select an Excel Spreadsheet"
            .Filters.Clear
            .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"
    
    If fDialog.Show Then
        For Each item In fDialog.SelectedItems
            Me.txtPath = item
        Next
    End If
    
    If FSO.FileExists(Nz(Me.txtPath, "")) Then
            ExcelImport.ImportExcelSpreadsheet Me.txtPath, FSO.GetFileName(Me.txtPath & Now())
            Me.lstNewGosi = Ist
        Next

    If Nz(Me.txtPath, "") = "" Then
        MsgBox "Please select a file!"
        Exit Sub
    End If
    End If

End Sub

I want to import an Excel Spreadsheet file & display in a list box for temporally, Check the data then KILL import excel Spreadsheet .

 

[moke123]

You have the wrong reference. You need Microsoft Scripting Runtime, not MS Scriplet library.
You also need a reference to Microsoft Office XX Object Library.

 

[smtazulislam]

You have the wrong reference. You need Microsoft Scripting Runtime, not MS Scriplet library.
You also need a reference to Microsoft Office XX Object Library.

Thank you. Its work... But FileDialog not defined still not work.
I have reference Microsoft office 16.0 Object Library See Msg #1 picture Serial number-2

Edit /
If I put

Dim fDialog As Office.FileDialog

Not work. So I put here

Dim fDialog As Object

 

[gemma-the-husky]

I don't know if this is a correct command. It may be a new one, or you may be working in excel

ExcelImport.ImportExcelSpreadsheet Me.txtPath, FSO.GetFileName(Me.txtPath & Now())

In Access VBA, you would
docmd.transferSpreadsheet, or domcd.TransferText for an xl or csv document respectively.

 

[moke123]

You have MS ACCESS object library, not office.

 

[smtazulislam]

You have MS ACCESS object library, not office.
View attachment 90451

this is great! Thank you so much. Now work it...

 

[smtazulislam]

I don't know if this is a correct command. It may be a new one, or you may be working in excel



In Access VBA, you would
docmd.transferSpreadsheet, or domcd.TransferText for an xl or csv document respectively.

Dim Ist As Variant
Dim FSO As New FileSystemObject
lst = Me.lstNewGosi

    If FSO.FileExists(Nz(Me.txtPath, "")) Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, Me.txtPath, [Date = Now()]
        Me.lstNewGosi = Ist
    Else
        MsgBox "File not found!"
    End If

TableName = Me.txtpath
FileName = [Date = Now()]

exactly I need Import this Spreadsheet .Temp file insert in the "list Box" when I close form then KILL this .temp Spreadsheet.
Can possible ?

 

[smtazulislam]

Can anyone tell why my list box not LOAD...
I try like that

Private Sub cmdBrowser_Click()
' This requires a reference to the Microsoft Office 16.0 Object Library.
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim varlist As Object

'Clear the list box contents.
Me.lstNewGosi.RowSource = ""

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set varlist = [Forms]![frmNewGosi]![lstNewGosi]
  
    With fDialog
        .InitialFileName = "G:\PayRoll\Gosi"
        .AllowMultiSelect = False
        .Title = "Please select an Excel Spreadsheet"
        .Filters.Clear
        .Filters.Add "Excel Spreadsheet", "*.xls, *.xlsx"
'        .Filters.Add "Access Projects", "*.ADP"
'        .Filters.Add "All Files", "*.*"

        If fDialog.Show Then
            'add it to the list box.
            For Each varFile In fDialog.SelectedItems
                Me.txtPath = varFile

                Me.lstNewGosi.RowSource = "SELECT * FROM New_Gosi"
            Next
        End If
      
        'Path Directory checking
        If Nz(Me.txtPath, "") = "" Then
            MsgBox "Please select a file!"
            Exit Sub
        End If

'Import from Excel.
    If [Forms]![frmNewGosi]!txtPath = 1 Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "New_Gosi", varlist, True, ""
    Else
        MsgBox "You data Imported !", vbOKOnly + vbCritical, "Import"
    End If
End With
End Sub

Edit/
Its was message "You data imported ". But not load in the list box...
I guess I missing format Me.lstNewGosi.RowSource = "SELECT * FROM New_Gosi"
RowSourceType Or ColumnCount...

Where I have missing....

 

[moke123]

You are trying to set the rowsource to New_Gosi before creating it.

Move that line after the docmd.transferSpreadsheet.

'Import from Excel.
    If [Forms]![frmNewGosi]!txtPath = 1 Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "New_Gosi", varlist, True, ""

Me.lstNewGosi.RowSource = "SELECT * FROM New_Gosi"

    Else
        MsgBox "You data Imported !", vbOKOnly + vbCritical, "Import"
    End If

 

[smtazulislam]

You are trying to set the rowsource to New_Gosi before creating it.

Move that line after the docmd.transferSpreadsheet.

'Import from Excel.
    If [Forms]![frmNewGosi]!txtPath = 1 Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "New_Gosi", varlist, True, ""

Me.lstNewGosi.RowSource = "SELECT * FROM New_Gosi"

    Else
        MsgBox "You data Imported !", vbOKOnly + vbCritical, "Import"
    End If

Thank you so much. I really happy to see your reply...
Its was create by Separated,
Import Excel sheet
Args
Unload List box.

Now working...