Retrieving different ranges of data from different workbooks

[ijswalker]

I am trying to return a variable ranges instead of a fixed range from my workbooks. For example. Workbook 1 can have Range A2:BB12 as shown in the code below and the next workbook can have range A2:BB5 and so on.

Is there a way to change the code to relect the different ranges without hard coding each range in turn?

Hope someome can help?

Thanks

Ian

Public Sub GetDirXlsContents()
' Source sheet name, Source directory path, Source cell Range
Call CopyFromEachFileInPath("DataBase", "C:\Documents and Settings\iwalker\My Documents\Capital Budget 2007\Project Manager", "A2:BB12")
End Sub

Private Sub CopyFromEachFileInPath(SheetName, Path, Rng)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(Path & "\")
Set fc = f.Files

' make a temp sheet
Application.ScreenUpdating = False
TargSh = ActiveSheet.Name
Sheets.Add
TempSh = ActiveSheet.Name
Sheets(TargSh).Activate
Application.ScreenUpdating = True

For Each f1 In fc
With Sheets(TempSh)

' clear temp sheet and start again
.Cells.ClearContents

' Place Src Info on Temp Targ Sheet
If Right(f1.Name, 3) = "xls" Then
fName = Left(f1.Name, Len(f1.Name) - 4)
.Range(Rng).FormulaArray = "='" & Path & "\[" & fName & "]" & SheetName & "'!" & Rng
.Range(Rng).Value = .Range(Rng).Value

'GetValuesFromAClosedWorkbook Path, f1.Name, SheetName, "A1:bb12"
End If

' if columD = 1 copy over
For Each A In .Columns("A:A").SpecialCells(xlCellTypeConstants, 3)
NxRw = Cells(65536, 1).End(xlUp).Row + 1
If Not A.Value = 0 And A.Offset(1, 0).Value = 0 Then ' copy to final sheet
Range("A" & NxRw & ":BB" & NxRw).Value = .Range("A" & A.Row & ":BZ" & A.Row).Value
Range("AS" & NxRw).Value = fName
End If
Next A
End With
' have use:banghead: r see list build, so know not frozen
Cells(NxRw, 1).Select
Next ' workbook

' get rid of temp sheet
Application.DisplayAlerts = False
Sheets(TempSh).Delete
Application.DisplayAlerts = True


End Sub

[Zack Barresse]

Hi there, welcome to VBAX!

I would think about possibly using a seperate Function to call that has the ranges hardcoded, so you only need to enter it once then call the function whenever you want to retreive the range.

I would do that unless there was some way you could do so logically in each workbook.

[ijswalker]

Thanks Firefytr

The problem with the hard code is that I do not know what the range is as relates to number of rows per workbook. There could also be up to 100 of these workbooks. I was hoping to be able to selected the used range in each workbook but can't seem to get that to work. Any Ideas?

Thanks

Ian

[Zack Barresse]

The UsedRange is not very reliable. Check out DRJs code here for getting the working range of a sheet. Is this what you're looking for?

[geekgirlau]

If your range always starts in A2, you could also set the range using the Offset function

=OFFSET(A2,0,0,COUNTA(A:A),COUNTA(2:2))

[ijswalker]

From the last reply, where would I insert this formula because I only see it returning a fixed range and not a variable range?

I would love to attach the file for you all to look at but the file is too large. The upload file function only allows me 221K. The file is around 500k.

Cheers

Ian

[geekgirlau]

You can define the range using Insert | Name | Define, then type the formula above as the Refers to. You can then test this by adding some rows and columns to your data, and selecting Insert | Name | Define. Click on the range name you've added, then click in Refers to. You should find that your additional rows and columns have been incorporated into the range.

By the way, did you try zipping the file before attaching it?