VBA-Import data from another workbook

[Emmily]

Hi, can the below code be tweaked to allow flexibility of file name and ranges. The file name changes everymonth and i want the code to be able to allow the user to choose file. The data is to be copied from Sheet "Deliverable" Ranges("A5:C")

Option Explicit
 
 'you can extract data from a closed file by using an
 'XLM macro. Credit for this technique goes to John
 'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm
 
Sub GetDataDemo()
     
    Dim FilePath$, Row&, Column&, Address$
     
     'change constants & FilePath below to suit
     '***************************************
    Const FileName$ = "Book1.xls"
    Const SheetName$ = "Sheet1"
    Const NumRows& = 10
    Const NumColumns& = 10
    FilePath = ActiveWorkbook.Path & "\"
     '***************************************
     
    DoEvents
    Application.ScreenUpdating = False
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
        Exit Sub
    End If
    For Row = 1 To NumRows
        For Column = 1 To NumColumns
            Address = Cells(Row, Column).Address
            Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
            Columns.AutoFit
        Next Column
    Next Row
    ActiveWindow.DisplayZeros = False
End Sub
 
 
Private Function GetData(Path, File, Sheet, Address)
    Dim Data$
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
    Range(Address).Range("A1").Address(, , xlR1C1)
    GetData = ExecuteExcel4Macro(Data)
End Function

 

[kpark91]

Hi, Emmily.
You might want to consider looking into msoFileDialogPicker if you want to allow the user to actually select the file.

Here's my implementation of the code

    ' Requires reference to Microsoft Office 11.0 Object Library.
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    
    ' Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    With fDialog
        '.InitialFileName = ThisWorkbook.Path
        ' Allow user to make multiple selections in dialog box
        .AllowMultiSelect = False
        
        ' Set the title of the dialog box.
        .Title = "Please select one or more files"
    
        ' Clear out the current filters, and add our own.
        .Filters.Clear
        .Filters.Add "Semicolon Delimited Data (*.SDD)", "*.SDD"
        .Filters.Add "All Files (*.*)", "*.*"
    
        ' Show the dialog box. If the .Show method returns True, the
        ' user picked at least one file. If the .Show method returns
        ' False, the user clicked Cancel.
        If .Show = True Then
            .Text = .SelectedItems(1)
        End If
        
        ' Freeing Object Variables
        Set fDialog = Nothing
    End With

You just gotta make sure that the selected items are stored in an array from 1 To n as complete file paths.
So, in my case, I had to choose only one file so I've turned off the multiselect and looked at the file path by calling .SelectedItems(1).

 

[Emmily]

Hi i use Excel 2002 so not sure on the office library.

Where in your code does the it copies the data from A5:C in Sheet Deliverable to activesheet in this workbook

 

[kpark91]

Hi, Emily
My code doesn't copy the data from Sheet Deliverable to activesheet.
My code only pops up a file picking file dialog to allow the user to browse through his/her computer to select a file. Then it returns the path of the file chosen.

I haven't used or even seen Excel 2002, so I'm not sure about the office library in it as well. However, in excel 2003/7 I was able to go into VBE (Visual Basic Editor) and go to Tools>References to select the library I want to use.

Anyways, you can open workbook files like

filename = "Hello.xls"
 Workbooks.Open "C:\Users\Blarg\Desktop\" & filename

and you can grab data like

Dim destWS As Worksheet, LR As Long
Set destWS = ActiveSheet
LR = Workbooks(filename).Worksheets("Deliverable").Range("C" & Rows.count).End(xlUp).Row
'Copy data from Sheet deliverable to activesheet
Workbooks(filename).Worksheets("Deliverable").Range("A5:C" & LR).Copy _
    ThisWorkbook.destWS.Range("A5")

Hope this helps.

 

[Emmily]

The only issue is my filename will change everymonth, so how can i combine your earlier code with the above, so when i choose the file via dialogue box, the code will copy the data from "Deliverable" A5:C

I dont think i have office ref 11, only 10, so i assume the code would fail?

 

[kpark91]

Hi, Emmily.
If the filename is changing with patterns and the files are all stored in one folder then the code can be altered to open 'new' workbooks.

However, you will need to tell me how the filename is changing. For example,
hello07192011.xls
hello07202011.xls <- Next day

As per to your question, I believe the code will fail if you have office ref 10.
But try it anyways, it won't destroy your computer.

 

[Emmily]

The file name will change monthly by mmm yy. Nevertheless the code must allow the user to choose file regardless of naming convention and then the code will look in Sheet "Deliverable" and copy data A5:C

Do we need to have office 11 can the code be written to be open in any excel version?

 

[kpark91]

Like I've said, just try the code above as I don't have access to any lower versions of excel.

Sub PopUpDialogBox()
    ' Requires reference to Microsoft Office Object Library.
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    
    ' Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    With fDialog
        '.InitialFileName = ThisWorkbook.Path
        ' Allow user to make multiple selections in dialog box
        .AllowMultiSelect = False
        
        ' Set the title of the dialog box.
        .Title = "Please select one or more files"
    
        ' Clear out the current filters, and add our own.
        .Filters.Clear
        .Filters.Add "All Files (*.*)", "*.*"
    
        ' Show the dialog box. If the .Show method returns True, the
        ' user picked at least one file. If the .Show method returns
        ' False, the user clicked Cancel.
        If .Show = True Then
            MsgBox "You've picked " & .SelectedItems(1)
        Else
            MsgBox "You've clicked Cancel button."
        End If
    End With

    ' Freeing Object Variables
    Set fDialog = Nothing

End Sub

Does this code pop up a browser box?

 

[Emmily]

Yep the dialogue box appears.

Ok when i choose the file how can i get the copy paste to work now.