Network data gathering

Mooresmi

Board Regular
Joined
Oct 15, 2002
Messages
68
Is there a way for Excel to get data out of a workbook without opening the file?

There used to be a way in Excel8, but that approach doesn't appear to work all that successfully now.

Reason for request: Running many programs that do just that across a large national network (language is VB), and it takes too much time. This is because Excel is opening the file with the data in the background (I am using application.ScreenUpdating=false etc).
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Yep .. here's an example ..

Code:
Public Sub GetDirXlsContents()
' Source sheet name, Source directory path, Source cell Range
Call CopyFromEachFileInPath("Sheet1", "C:\test", "A1:I500")
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:K30"
    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 & ":I" & NxRw).Value = .Range("A" & A.Row & ":Z" & A.Row).Value
        Range("J" & NxRw).Value = fName
        End If
    Next A
    End With
    ' have user 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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,087
Members
412,566
Latest member
TexasTony
Top