Accessing or importing dynamic data from external Excel file or sheet

[Hockeynow70]

Hi there,

I've been scanning the web for a way to access information from an external Excel document and use it in another workbook for analysis, but haven't found a way to do what I want. Basically, I want to pull in specific columns from an external sheet, which have a variable number of rows, which may change (so I don't know the end of the range). I've looked into "Get External Data", but it appears I can only import a whole sheet as a table. But I do not want to pull the entire external sheet because it's huge. Rather, I would like to select only specific columns from the sheet, which I can then 'Refresh' to update as the source sheet changes. Possible?

UPDATE!:

I forgot to mention that am also wanting to pull other columns from other sheets and/or workbooks...

I'm running Excel 2010.

Thank you in advance!

Regards,
Chris

 

[JLGWhiz]

If you want to get data from column Q of sheet 3 in workbook("OtherFile.xlsx") and put it in the next available cell of column "S" of sheet 1 of the active workbook, you can use variables to set up and define the data even if you do not know which row that column ends on;

sub getxtlDat()
dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long 'Declare variables
Set wb1 = ThisWorkbook 'assign first workbook variable
Set sh1 = wb1.Sheets(1) 'assign sheet 1 of first workbook variable
Set wb2 = Workbooks.Open("OtherFile.xlsx") 'assign second workbook variable
Set sh2 = wb2.Sheets(3) 'assign sheet 3 of second workbook variable
lr = sh2.Cells(Rows.Count, "Q").End(xlUp).Row 'assing variable for last row with data in column Q, sheet 3, second workbook.
sh2.Range("@2:Q" & lr).Copy sh1.Cells(Rows.Count, "S").End(xlUp)(2) 'copy from wb2 to wb1
End Sub

By using the variables, it eliminates having to repeat the full qualification of locations of data within the workbooks and sheets. The variable e.g. sh1 contains all the information for VBA to know which workbook and worksheet is being referenced. The statement assigning the lr variable makes the range dynamic. If the range increases or decreases in size between running the procedure, then that statement will automatically adjust the last row variable to the current range size. You can declare and assign as many variables as needed to do you tasks.