Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72

    Prompt for filename for import/export??

    Good day. I have a button that runs an export from MYTABLE to MyExcelFile.xls. I need for the user to be able to choose the name and path of the destination.

    Similarly, after they do some validation and provide additional information to the spreadsheet, there is another button that currently imports MyExcelFile.xls to MYTABLE. It works fine, but I need for the user to be able to choose the name of the SOURCE file, not the target file.

    The reason for the need is that the back end will reside on the server. Individuals use different locations for receiving, holding and working on the interim spreadsheet, and I don't want to mess with that.



    Thanks in advance!

    Stephen.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Many developers use this API: http://www.mvps.org/access/api/api0001.htm

  3. #3
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72

    ouch- no idea how to use that

    As a complete neophyte, ok, not complete, but close, I suppose I'll continue specifying a location. I can't follow how to actually use that code.

    Thanks for the try, though. I'm just learning my way though, hoped it might be easier than that.

    Stephen.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That code is real easy to use. First copy from:
    *** Code Start ***
    to...
    *** Code End ***
    ...into a standard module named basCode. Then to use it just follow the instruction above the code in the link:
    Code:
      To call the actual dialog from your code, see the included function TestIt() within the module or use the following example as a guideline and 
    Dim strFilter As String
    Dim strInputFileName as string
     
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
    strInputFileName = ahtCommonFileOpenSave( _
                    Filter:=strFilter, OpenFile:=True, _
                    DialogTitle:="Please select an input file...", _
                    Flags:=ahtOFN_HIDEREADONLY)
    Note that in order to call the Save As dialog box, you can use the same wrapper function by just setting the OpenFile option as False. For example,
     
    'Ask for SaveFileName
    strFilter = ahtAddFilterItem(myStrFilter, "Excel Files (*.xls)", "*.xls")
    strSaveFileName = ahtCommonFileOpenSave( _
                                        OpenFile:=False, _          
                                        Filter:=strFilter, _
                        Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

  5. #5
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72

    Import/Export.

    OK, so I'm stepping my way through, tackling the IMPORT piece first. Much appreciate your help.

    I created a module called basCode as you said, and inserted the Start to End code into it. Now for actually launching it, and then getting the selection to go where I want it.

    First, how do you "launch" the code to get the select window to pop up?

    Thanks!

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Create a button on your form that uses the code posted in my post #4.

  7. #7
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72

    import

    I really appreciate the help. Created a button, with the onclick code below. I get the following error: "ByRef argument type mismatch"


    Private Sub LaunchFileSelect_Click()
    Dim strFilter As String
    Dim strInputFileName As String

    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
    strInputFileName = ahtCommonFileOpenSave( _
    Filter:=strFilter, OpenFile:=True, _
    DialogTitle:="Please select an input file...", _
    Flags:=ahtOFN_HIDEREADONLY)
    'Note that in order to call the Save As dialog box, you can use the same wrapper function by just setting the OpenFile option as False. For example,

    'Ask for SaveFileName
    strFilter = ahtAddFilterItem(myStrFilter, "Excel Files (*.xls)", "*.xls")
    strSaveFileName = ahtCommonFileOpenSave( _
    OpenFile:=False, _
    Filter:=strFilter, _
    Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
    End Sub

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I've tested this and it works.
    Code:
    Private Sub LaunchFileSelect_Click()
       Dim strFilter As String
       Dim strInputFileName As String
       strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
       strInputFileName = ahtCommonFileOpenSave( _
                          Filter:=strFilter, OpenFile:=True, _
                          DialogTitle:="Please select an input file...", _
                          Flags:=ahtOFN_HIDEREADONLY)
    End Sub

  9. #9
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72
    You are the man. Yes, it does work. Now, I need to know where it PUTS that filename. I have code that imports a specific filename, via docmd.transferspreadsheet. It works well. Now, I need to inject the file selected instead of "c:\documents and settings\stephen\desktop\CallImport.xls".

    Please tell me it's simple... and THANKS!

    The code I'm using is:

    DoCmd.TransferSpreadsheet _
    acImport, _
    acSpreadsheetTypeExcel5, _
    "SvcCallImportTbl", _
    path & "c:\documents and settings\stephen\desktop\CallImport.xls", _
    True

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Change the first to a Function from a Sub and try this:
    Code:
    Private Function SelectFile() As String
       Dim strFilter As String
       strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
       SelectFile = ahtCommonFileOpenSave( _
                    Filter:=strFilter, OpenFile:=True, _
                    DialogTitle:="Please select an input file...", _
                    Flags:=ahtOFN_HIDEREADONLY)
    End Function
     
    Private Sub Foo()
       DoCmd.TransferSpreadsheet _
             acImport, _
             acSpreadsheetTypeExcel5, _
             "SvcCallImportTbl", _
             SelectFile, _
             True
    End Sub

  11. #11
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72
    I'm clearly not doing it right... Pardon my obtuse-ness.

    Private Sub ImportButton_Click()
    Private Function SelectFile() As String
    Dim strFilter As String
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
    SelectFile = ahtCommonFileOpenSave( _
    Filter:=strFilter, OpenFile:=True, _
    DialogTitle:="Please select an input file...", _
    Flags:=ahtOFN_HIDEREADONLY)
    End Function

    Private Sub Foo()
    DoCmd.TransferSpreadsheet _
    acImport, _
    acSpreadsheetTypeExcel5, _
    "SvcCallImportTbl", _
    SelectFile, _
    True
    End Sub
    End Sub

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try this:
    Code:
    Private Sub ImportButton_Click()
    Private Function SelectFile() As String
       Dim strFilter As String
       strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
       SelectFile = ahtCommonFileOpenSave( _
                    Filter:=strFilter, OpenFile:=True, _
                    DialogTitle:="Please select an input file...", _
                    Flags:=ahtOFN_HIDEREADONLY)
       DoCmd.TransferSpreadsheet _
             acImport, _
             acSpreadsheetTypeExcel5, _
             "SvcCallImportTbl", _
             strFilter, _
             True
    End Sub

  13. #13
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72
    Hi again. I did it exactly as below (cut and paste), and get "expected end sub." There IS and end sub. Any ideas??

    Private Sub ImportButton_Click()
    Private Function SelectFile() As String
    Dim strFilter As String
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
    SelectFile = ahtCommonFileOpenSave( _
    Filter:=strFilter, OpenFile:=True, _
    DialogTitle:="Please select an input file...", _
    Flags:=ahtOFN_HIDEREADONLY)
    DoCmd.TransferSpreadsheet _
    acImport, _
    acSpreadsheetTypeExcel5, _
    "SvcCallImportTbl", _
    strFilter, _
    End Sub

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    My bad. Get rid of the second line.
    Code:
    Private Sub ImportButton_Click()
       Dim strFilter As String
       Dim SelectFile As String
       strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
       SelectFile = ahtCommonFileOpenSave( _
                    Filter:=strFilter, OpenFile:=True, _
                    DialogTitle:="Please select an input file...", _
                    Flags:=ahtOFN_HIDEREADONLY)
       DoCmd.TransferSpreadsheet _
             acImport, _
             acSpreadsheetTypeExcel5, _
             "SvcCallImportTbl", _
             SelectFile, _
             True
    End Sub

  15. #15
    stephenaa5 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    72
    Compile Error
    "Function call on left hand side must return variant or object."

    Selectfile = is highlighted.

    Sorry this is such a pain. I appreciate your help.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Password Prompt
    By thestappa in forum Security
    Replies: 1
    Last Post: 01-28-2010, 09:19 PM
  2. Filename displaying with image
    By Rob Parker in forum Forms
    Replies: 2
    Last Post: 01-13-2010, 05:05 PM
  3. prompt criteria issues
    By Rik_StHelens in forum Queries
    Replies: 1
    Last Post: 10-16-2009, 10:17 AM
  4. Removing the delete prompt.
    By botts121 in forum Programming
    Replies: 4
    Last Post: 06-26-2009, 11:45 AM
  5. Replies: 0
    Last Post: 06-11-2009, 01:54 PM

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