Consulting

Results 1 to 4 of 4

Thread: VBA Browse multiple workbooks how to short the code....

  1. #1
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location

    VBA Browse multiple workbooks how to short the code....

    Hi Team,
    
    
    I need your help plz assist, I am using below code to browse Multiple required Inputworkbooks, 
    is there a way to shorten this code, by creating single function.
    
    
    Thanks in Advance !
    
    
    Sub GetWorkbook1()
    Set myfile = Application.FileDialog(msoFileDialogOpen)
    With myfile
    .Title = "Choose File"
    .AllowMultiSelect = False
    If .Show <> -1 Then
    Exit Sub
    End If
    FileSelected = .SelectedItems(1)
    End With
    
    
    ActiveSheet.Range("b4") = FileSelected
    End Sub
    
    
    Regards,
    mg
    Attached Files Attached Files

  2. #2
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location
    Currently I am using above code multiple times for browsing each workbook.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Easiest thing would be to make it modular and use a sub for all 4 files

    Option Explicit
    
    
    Private Sub GetAnyWorkbook(r As Range)
        With Application.FileDialog(msoFileDialogOpen)
            .Title = "Choose File"
            .AllowMultiSelect = False
        
            If .Show <> -1 Then Exit Sub
    
    
            r.Cells(1, 1).Value = .SelectedItems(1)
        End With
    
    
    End Sub
    
    
    
    
    Sub GetWorkbook1()
        Call GetAnyWorkbook(ActiveSheet.Range("B4"))
    End Sub
    
    Sub GetWorkbook2()
        Call GetAnyWorkbook(ActiveSheet.Range("B6"))
    End Sub
    
    Sub GetWorkbook3()
        Call GetAnyWorkbook(ActiveSheet.Range("B8"))
    End Sub
    
    Sub GetWorkbook4()
        Call GetAnyWorkbook(ActiveSheet.Range("B10"))
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location
    Hi Paul,
    Thanks once again, it worked.

    Regards,
    mg

Posting Permissions

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