Results 1 to 5 of 5

Thread: Macro To Browse Select File And Import Specific Data

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0

    Macro To Browse Select File And Import Specific Data

    I'm helping my boss with a payroll calculation worksheet. All of the calculations has been created and I just need a few Macros to do the following:

    1) Open directory C:\ to allow me to select an invoice file
    2) Once the Invoice has been selected (see attached sample), I need the data from cell A3:J29 to be copied to the destination workbook in sheet Calculation, cell A2-J28.
    3) The next Macro would copy data from cell A3:J29 in Invoice file and paste it to cell L2-U28 in the Calculation worksheet. The same will follow for the the rest of the tables.

    I'd really really appreciate your help. It always take my boss a good couple of hours to do the payroll calculation and I just wanted to help.

    THANK YOU!!!
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Welcome to ExcelFox !!

    Try this code. The code will allow you to select the invoice file as well as the destination range. So don't need multiple macros.

    Code:
    Sub kTest()
        
        Dim SourceFile  As String
        Dim Dest        As Range
        Dim wbkSource   As Workbook
        Dim SourceData
        
        Const SourceRange = "A3:J29"
        
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .Filters.Clear
            .AllowMultiSelect = False
            If .Show = -1 Then
                SourceFile = .SelectedItems(1)
            End If
        End With
        
        If Len(SourceFile) Then
            Set wbkSource = Workbooks.Open(SourceFile)
            SourceData = wbkSource.Worksheets("Contractors").Range(SourceRange).Value2
            wbkSource.Close 0
            Set wbkSource = Nothing
            
            Set Dest = Application.InputBox("Select the destination range", "Destination", "A2", Type:=8)
            
            Dest.Cells(1).Resize(UBound(SourceData, 1), UBound(SourceData, 2)) = SourceData
            
        End If
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    THANK YOUUU SO MUCH!
    It works perfectly!!!!

  4. #4
    Junior Member
    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Hello,

    I'm having some trouble with these spreadsheets again. Now different Invoices have different formats, therefore I can't use the standard data range (A2:A28) anymore. Can you please make some changes to the macro so I can select the data from the Invoice spreadsheet?

    Thank you a zillions!! It has helped us so much.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Code:
    Sub kTest()
        
        Dim SourceFile  As String
        Dim Dest        As Range
        Dim wbkSource   As Workbook
        Dim SourceRange As Range
        Dim SourceData
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .Filters.Clear
            .AllowMultiSelect = False
            If .Show = -1 Then
                SourceFile = .SelectedItems(1)
            End If
        End With
        
        If Len(SourceFile) Then
            Set wbkSource = Workbooks.Open(SourceFile)
            Set SourceRange = Application.InputBox("Select the source range", "Destination", "A2", Type:=8)
            SourceData = SourceRange.Value2
            wbkSource.Close 0
            Set wbkSource = Nothing
            
            Set Dest = Application.InputBox("Select the destination range", "Destination", "A2", Type:=8)
            
            Dest.Cells(1).Resize(UBound(SourceData, 1), UBound(SourceData, 2)) = SourceData
            
        End If
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 3
    Last Post: 06-10-2013, 06:12 PM
  2. Replies: 6
    Last Post: 06-01-2013, 03:24 PM
  3. Select Sheets For Specific Date By Clicking On A Calendar
    By peter renton in forum Excel Help
    Replies: 10
    Last Post: 05-17-2013, 01:57 PM
  4. Replies: 7
    Last Post: 05-08-2013, 07:12 PM
  5. Excel VBA Macro To Open A File Through Browse Dialog Box
    By Safal Shrestha in forum Excel Help
    Replies: 2
    Last Post: 04-05-2013, 12:59 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
  •