Results 1 to 8 of 8
  1. #1
    mt1013 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2013
    Posts
    16

    Display msg box when user picks the wrong file to import (Access 2007)

    HI. I would like to know how to code a VBA module that will display an error mesage or msg box if a user clicks on the wrong excel file to import. I'm not to sure if I should look for a unique field that resides in the file...or? I guess the logical thing would be to have a message box displayed if a user tries to import a file that i not similar to the files needed (i.e. if the user tries to click on a file that is not like "Contact*" then a message box will pop up asking the user to pick another file. Any help out there?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    The simple fact that the user is selecting the file tells me that you will need to check what the file consists of for verification. I guess you can get file properties from a filesystem object. Not sure if that helps your cause though.

    Sounds like having Access just go after the file without the User interaction is not an option.

  3. #3
    mt1013 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2013
    Posts
    16
    Hi. Thanks for the advice. Yes, I'm not suppose to allow access to search for the file automatically because the file changes title each week so it is better for the user to browse for it. Unfortunately the user in this case doesn't have much experience browsing for the file. That' why I wanted to put in some kind of error message that will warn when the wrong file has been select. Haven't done something like this before so I'm not to sure how to use the file properties to open up the file and check for a unique field. Actually, in this case, I thought the name of the file would be a good identifier. Although the file name changes each week, the one constant is the word, "contacts" as part of the filename. Thanks.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe you can dedicate a folder. You can have Access go after every file in a specific folder. If the file name changes but the file type and the folder does not, that is an easy thing to manage. Can you dictate where the file will reside and what the file type is?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Validating the file name includes 'contacts' as well as file type will reduce error, but not eliminate. ItsMe's suggestion for a dedicated folder would be a big help in controlling what user can select.

    How is the navigation initiated? If you are using the import wizard, can't run any custom validation code anyway.
    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.

  6. #6
    mt1013 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2013
    Posts
    16
    I never thought of that! Thanks. I'll give it a try.

  7. #7
    mt1013 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2013
    Posts
    16
    Yes. ItsMe has some great advice. For what it's worth, the navigation is through a vba module. The code is set up to prompt the user for the file and the user goes to the directory for the excel workbook. Then the file is imported via a DoCmd transferspreadsheet line of code. Thanks.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Do you need help with code to loop through the files in a folder?

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

Similar Threads

  1. Replies: 13
    Last Post: 11-18-2013, 02:20 PM
  2. Import from Excel 2007 to Access 2007
    By Chris LNG in forum Import/Export Data
    Replies: 3
    Last Post: 07-09-2013, 11:36 AM
  3. Access 2007 import fix width ANSI text file
    By jsama in forum Import/Export Data
    Replies: 0
    Last Post: 03-05-2013, 09:07 PM
  4. import excel file to access 2003 yes, access 2007 no
    By stapik in forum Import/Export Data
    Replies: 9
    Last Post: 07-27-2011, 07:09 AM
  5. how to display the extension file access 2007
    By tintincute in forum Access
    Replies: 4
    Last Post: 08-04-2010, 10:09 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