By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Compile Error that I can't seem to get around in Button Click function

P: 8
I have the following code that I've built thru some help and searching here on this forum (Thanks a million to all the great help here). I'm getting a Compile error: Invalid outside procedure message box on the "Set objFileDialog =" line.
Expand|Select|Wrap|Line Numbers
  1. Dim strPath As String
  2. Dim strFile As String
  3. Dim strTable As String
  4. Dim strSpecification As String
  5. Dim intImportType As AcTextTransferType
  6. Dim blnHasFieldNames As Boolean
  7. Dim objFileDialog As Office.FileDialog
  8.  
  9. Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFolderPicker)
  10.  
  11. '**** Modify these values as needed ****
  12. strTable = "Daily Aeppays Consolidated"
  13. strSpecification = "DailyAeppayImport"
  14. blnHasFieldNames = True
  15. intImportType = acImportDelim
  16. '***************************************
  17.  
  18. 'Let user select a folder
  19. With objFileDialog
  20.   .AllowMultiSelect = False     'Meaningless, but set anyway
  21.   .ButtonName = "Folder Picker"
  22.   .Title = "Folder Picker"
  23.      If .Show Then
  24.        strPath = .SelectedItems(1) & "\"
  25.  
  26.        'Loop through the text files
  27.        strFile = Dir(strPath & "*.txt")
  28.          Do While strFile <> ""
  29.            'For Testing purposes only
  30.            DoCmd.TransferText intImportType, , strTable, strPath & strFile, False
  31.          strFile = Dir
  32.          Loop
  33.      End If
  34. End With
Any idea where to move it or how to get around this? I need this code to work on the click of the button which it begins to, but then hits the error on this particular line.

Thanks!
Jun 27 '17 #1
Share this Question
Share on Google+
5 Replies


P: 7
Have you referenced the object library. If not, from the code window select tools and select Microsoft Office xx Object Library, then it will compile.
Jun 27 '17 #2

P: 7
you also need to make sure it's in a procedure such as

public function whatever
your code
exit function
Jun 27 '17 #3

P: 8
Just checked the Object Library and everything seems fine there. I put the code into a Private Function On Click() with exit function at the end and still getting same error. Going to keep plugging away at it. Thanks for your suggestions!
Jun 27 '17 #4

P: 7
Put this in the click event of the button
Expand|Select|Wrap|Line Numbers
  1.     Call GetFile
  2.  
put this into a module
Expand|Select|Wrap|Line Numbers
  1. Public Function GetFile() As Boolean
  2.     On Error GoTo Proc_Err
  3.  
  4.     Dim strPath             As String
  5.     Dim strFile             As String
  6.     Dim strTable            As String
  7.     Dim strSpecification    As String
  8.     Dim intImportType       As AcTextTransferType
  9.     Dim blnHasFieldNames    As Boolean
  10.     Dim objFileDialog       As Office.FileDialog
  11.  
  12.     Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFolderPicker)
  13.  
  14.     '**** Modify these values as needed ****
  15.     strTable = "Daily Aeppays Consolidated"
  16.     strSpecification = "DailyAeppayImport"
  17.     blnHasFieldNames = True
  18.     intImportType = acImportDelim
  19.     '***************************************
  20.  
  21.     'Let user select a folder
  22.     With objFileDialog
  23.         .AllowMultiSelect = False 'Meaningless, but set anyway
  24.         .ButtonName = "Folder Picker"
  25.         .Title = "Folder Picker"
  26.         If .Show Then
  27.             strPath = .SelectedItems(1) & "\"
  28.             'Loop through the text files
  29.             strFile = Dir(strPath & "*.txt")
  30.             Do While strFile <> ""
  31.             'For Testing purposes only
  32.             DoCmd.TransferText intImportType, , strTable, strPath & strFile, False
  33.             strFile = Dir
  34.             Loop
  35.         End If
  36.     End With
  37.  
  38. Proc_Exit:
  39.     Exit Function
  40.  
  41. Proc_Err:
  42.     MsgBox "Error Nbr. " & Err.Number & vbCrLf & _
  43.            "Line Nbr. " & Erl & vbCrLf & vbCrLf & _
  44.            Err.Description, vbExclamation, "Mod: Common.GetFile"
  45.     Resume Proc_Exit
  46.  
  47. End Function
  48.  
Jun 27 '17 #5

NeoPa
Expert Mod 15k+
P: 31,418
Can I assume you've already looked at Select a File or Folder using the FileDialog Object?

PS. Welcome to the club Baderms. Alweays good to see new members answering questions :-)
Jun 28 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.