Results 1 to 4 of 4
  1. #1
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43

    User defined path for transfer spreadsheet macro


    In transfer spreadsheet macro whether user has an option to select the file path each time of import from spreadsheet

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    usage:
    vFile = UserPick1File("c:\folder")
    if vFile <> "" then docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12, "tTable", vFile, true




    Code:
    Public Function UserPick1File(pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    If IsMissing(pvPath) Then pvPath = "c:\"
    
    
    With Application.FileDialog(msoFileDialogFilePicker)   'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
        .Filters.Add sDecr, sExt
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
    
            If .show = 0 Then
               'There is a problem
               Exit Function
            End If
    
    
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function

  3. #3
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    Sir,
    Thank you for the reply.
    I have only little knowledge in VBA. Hence I am using the transfer spreadsheet macro action. How is it possible in macro?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Most of us don't use macros. Convert it to VBA by the following:

    In the top left of the window it says "Convert Macro to Visual Basic".

    Sometimes this seems to be grayed out. Here is the solution:
    "The solution I found was, rather than loading the form in Design View,
    I clicked the Macro button under Create on the Ribbon which brought up
    the Macro Design window. Under the Action Catalog window, there is a
    section called "In this Database." I clicked down to the form and
    textbox which contained the macro I wanted to convert, then double-
    clicked. The macro displayed in the left hand window and the "Convert
    Macro's to Visual Basic", was not grayed out so I clicked it and the
    conversion was simple after that."

    You will see that the VBA code follows the macro and you can easily see what is going on, it will all feel very similar to you.

    Once you have done this, change the line to refer to the variable as in post # 2.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-20-2016, 05:34 PM
  2. Replies: 6
    Last Post: 05-27-2014, 05:41 AM
  3. Replies: 13
    Last Post: 12-12-2013, 07:22 PM
  4. Replies: 3
    Last Post: 11-12-2013, 04:13 PM
  5. File path name using Transfer spreadsheet
    By JohnN in forum Import/Export Data
    Replies: 0
    Last Post: 11-14-2005, 06:57 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