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).
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,020
Messages
5,834,986
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top