Code to import data from another Workbook.

[SamarthSalunkhe]

Hi All,

I am using the below code to import data from another workbook, but it is importing data from a given range only, but I want to import data as per the availability.

Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A2:L50").Copy
        ThisWorkbook.Worksheets("Import Data").Range("A2").PasteSpecial xlPasteValues
        OpenBook.Close False
       
    End If
    Application.ScreenUpdating = True
End Sub

 

[JEC]

What do you mean by "as per the availability" ?

 

[JEC]

This macro just copies the usedrange of the selected workbook. The workbook will be opened in the background.

Sub jec()
 Dim ar
 With Application.FileDialog(msoFileDialogFilePicker)
   If .Show Then
      With GetObject(.SelectedItems(1))
         ar = .Sheets(1).UsedRange.Value
         ThisWorkbook.Sheets("Import Data").Cells(2, 1).Resize(UBound(ar), UBound(ar, 2)) = ar
        .Close 0
      End With
   End If
 End With
End Sub

 

[Misca]

If the data you're copying is always found on the first sheet of the file try changing the line

OpenBook.Sheets(1).Range("A2:L50").Copy

to something like

OpenBook.Sheets(1).Range("A2").CurrentRegion.Copy

This copies all the data adjacent to cell A2.