Results 1 to 2 of 2

Thread: Please Help With File Dialog Syntax

  1. #1
    Join Date
    Oct 2017

    Please Help With File Dialog Syntax


    Can one of you pros take a look at this code and help get the bugs out?
    I'm trying to create a folder browser that will let the user go to the folder, pick the excel file and import it into access them into a table in access then run a couple questions to get it formatted do some calculations and export it back out with the same name it came in as

    Compile error: Invalid outside procedure / syntax error
    Compile error: Sub or Function not defined

    Visual Basic for Applications
    Microsoft Access 16.0 Object Library
    OLE Automation
    Microsoft Office 16.0 Object Library

    Private Sub cmdShow_Click()
    On Error GoTo SubError
    **** 'Add "Microsoft Office 16.0 Object Library" in references
        Dim fdialog As Office.FileDialog
        Dim varfgile As Variant
        txtSelectedName = ""
    **** ' Set up the File Dialog
    ****Set fdialog = Application.FileDialog(msoFileDialogFilePicker)
        With fdialog
    ********.Title = "Choose the spreadsheet you would like to import"
    ********.AllowMultiSelect = False
    ********.InitialFileName = "C:\Users\cvlasa1\Downloads\SelfTrade\Export to Excel\" 'Folder picker needs trailing slash
    ********.Filters.Add "Excel files", "*.xls*"
    '*******.Filters.Add "Excel files", "*.xls"
    '*******.Filters.Add "Excel files", "*.xlsx"
    '*******.Filters.Add "Excel macro-enabled", "*.xlsm"
    *******     If .Show = True Then
    *******     If .SelectedItems.Count = 0 Then
    **************** 'User clicked open but didn't select a file
    ****************GoTo SubExit
            End If
    ************ 'An option for MultiSelect = False
    ************ 'varFile = .SelectedItems(1)
    ************ 'txtSelectedName = varFile
    ************ 'Needed when MultiSelect = True
    ************For Each varFile In .SelectedItems
    ****************txtSelectedName = txtSelectedName & varFile & vbCrLf
    ************ 'user cancelled dialog without choosing!
    ************ 'Do you need to react?
    ********End If
    ****End With
    On Error Resume Next
    ****Set fdialog = Nothing
    ****Exit Sub
    ****MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
    ********"An error occurred"
    ****GoTo SubExit
    End Sub

  2. #2
    Join Date
    Nov 2020
    Code declares varfgile variable but uses varfile variable. However, that should not cause error because varfile is consistently used. txtSelectedName is not declared. Should have Option Explicit at top of every code module to catch spelling errors in variables.

    Code works for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts