Results 1 to 9 of 9
  1. #1
    Chaser is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    9

    Please Help With File Dialog Syntax

    Hi,




    Can one of you pros take a look at this code and help get the bugs out?


    I also attached some screenshotsClick image for larger version. 

Name:	References.jpg 
Views:	19 
Size:	78.9 KB 
ID:	30935Click image for larger version. 

Name:	2017-10-24_14-22-48.jpg 
Views:	20 
Size:	100.6 KB 
ID:	30936 to help.


    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


    ---------------------------------------------------------------------------------------------------------


    Code:
    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.Clear
            .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
                Next
            
            Else
                 'user cancelled dialog without choosing!
                 'Do you need to react?
            End If
      
        End With
      
    SubExit:
    On Error Resume Next
        Set fdialog = Nothing
        Exit Sub
      
    SubError:
        MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
            "An error occurred"
        GoTo SubExit
          
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Disable the error handler (just comment the On Error line).

    Which line throws the runtime error?

    Enable error handler only after procedure is tested and working properly.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Chaser is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    9
    Please see attached. 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
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Did you even try what I suggested?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    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  ' <<<--- check spelling
        
        txtSelectedName = ""     ' <<<--- variable not declared OR could be a control on a form/ If control, would use Me.txtSelectedName
        
         ' Set up the File Dialog
        Set fdialog = Application.FileDialog(msoFileDialogFilePicker)
    
    <snip>

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    After I posted, I downloaded your dB...... You have some serious corruption in the VBA project. I ran Debug/Compile about 20 times (I didn't count - but it was a lot!) and deleted the lines with errors, then re-added the lines.

    Don't understand why you have a Class module... I deleted it.
    I fixed the code behind the form... seems to work now....
    Attached Files Attached Files

  7. #7
    Chaser is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    9
    That was my fault June7,


    While still trying to work out the coding issue, other errors were popping up and with a newly created database and having next to now custom VBA added.
    I logged back in and attached the database with my other notes. After I closed I look at my emails and saw you responded.


    Thanks Steve for jumping in and taking a look at the database.
    So I know better for next time. When you do a run of the debug for error checking, how are you going through it and fixing the errors? Do you just go line by line making sure it runs? I just created this database the other day.


    Any ideas what I did to get it so corrupted.
    Was the Class Module used incorrectly or just causing problems?


    This is the tutorial that I copied the code from:
    http://youtu.be/DTtDchA4XQ4


    ***Is next step for me to import it into a table? Where do I find it from the import in the form to be able to import it into a table?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    If the Excel files are simple enough structure, use DoCmd.TransferSpreadsheet within the For Each loop.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So I know better for next time. When you do a run of the debug for error checking, how are you going through it and fixing the errors? Do you just go line by line making sure it runs?
    The Basics of Writing and Testing VBA Code https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

    Debugging VBA http://www.cpearson.com/excel/debug.htm



    Any ideas what I did to get it so corrupted.
    Have no idea ..... one of the great mysteries of the universe....



    Was the Class Module used incorrectly or just causing problems?
    For sure, used incorrectly, but could also have been causing problems.
    About writing classes:
    http://www.thelandbeyondspreadsheets...-in-excel-vba/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-12-2014, 06:31 PM
  2. File dialog Cancel issue
    By KWarzala in forum Modules
    Replies: 11
    Last Post: 05-09-2014, 01:54 PM
  3. Dialog windows and file requestor
    By daveinuk in forum Programming
    Replies: 1
    Last Post: 03-06-2014, 07:09 AM
  4. Search for associated folders in file dialog box
    By john_billau in forum Programming
    Replies: 1
    Last Post: 01-27-2012, 03:48 PM
  5. How to get a load file dialog?
    By degras in forum Programming
    Replies: 4
    Last Post: 04-21-2011, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums