Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts

    VBA to select files from a list

    I would like VBA code to be able select files from a list in directory C:/accounts and to copy the file select to a particular range pertaining to the file name

    I guess an array would have to be set up and to link each file to a range

    For EG BR1H (source workbook) to be copied to ranged name applicable for this workbook, Br2K to be copied to ranged name applicable for this workbook

    It would be appreciated if someone could assist me in providing me with code to do this

    In lotus 1-2-3 one could use F3 to select a list or file and have a macro to copy to the specified range

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    I have some code below which works ok, but would like to streamline this so as to filter files so I can see all the files in the array and then select the file and then copy the file to the range name in the destination file

    Code:
    Options Explicit
    
    
    
    Sub Sub_ArraySelect()
    Dim TBArray As Variant, Response As Single, iOptions As Integer, 
    iErr As Integer
    
    BArray = Array ("", "Br1", "Br2", "Br3","Br4","Br5", "BR6", "Br7", "Br8")
    iOptions = UBound(TBArray) - LBound(TBArray)
    
    iErr = 1
    While iErr <> 0
        On Error Resume Next
        Response = InputBox(Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    " 1   " & TBArray(1) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    " 2   " & TBArray(2) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    " 3   " & TBArray(3) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    " 4   " & TBArray(4) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    " 5   " & TBArray(5) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    " 6   " & TBArray(6) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    " 7   " & TBArray(7) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    " 8   " & TBArray(8) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _
                    "", "SELECT AREA BEING UPDATED", Default:="Select 1 TO " & iOptions)                                                                 
        If vbCancel Then
        MsgBox "Macro Terminated"
        End
        End If
        If Err.Number <> 0 Or Response < 1 Or Response > iOptions Or Int(Response) <> Response Then
            MsgBox "Invalid Response - Enter Number of Selection. Please try again"
            iErr = 1
        Else
            iErr = 0
            TB = TBArray(Response)
            Range(TB).Select
        End If
    Wend
    
    
    End Sub

    Your assistance in this regard is most appreciated
    Last edited by HowardC; 2014-08-01 at 10:26.

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Are you looking for something like?
    Code:
    Option Explicit
    Sub Sub_ArraySelect()
      Dim TBArray As Variant, Response As Integer, iOptions As Integer
      Dim TB As String, x As Integer, sPrompt As String
      TBArray = Array("", "Br1", "Br2", "Br3", "Br4", "Br5", "BR6", "Br7", "Br8")
      iOptions = UBound(TBArray) - LBound(TBArray)
      TB = vbCrLf & vbCrLf
      sPrompt = TB
      For x = 1 To iOptions
        sPrompt = sPrompt & " " & x & "   " & TBArray(x) & TB
      Next
      sPrompt = sPrompt & vbCrLf & "Select 1 TO " & iOptions
      Do
        Response = Application.InputBox(sPrompt, Title:="SELECT AREA BEING UPDATED", _
          Default:=1, Type:=1)
           
          If Response = False Then
            MsgBox "Macro Terminated"
            Exit Sub
          Else
            Response = Int(Response)
          End If
      Loop Until Response >= 1 And Response <= iOptions
      TB = TBArray(Response)
      Range(TB).Select
    End Sub

  5. #4
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Steve

    This is great

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •