Extracting data from another file

[cmefly]

Ladies and Gents,

I really need some help with the following situation - i'd greatly appreciate any ideas...

what i want to do: have a macro open a workbook and extract specific cells (ie: sheet1!a2, sheet5!A9, etc..) from a selected file.

this is what i have so far:

filetoopen = Application _
.GetOpenFilename
Workbooks.Open (filetoopen)

What i need: So, the file opens up however, what i want and can't figure out, is how i can tell the macro to extract data from the open file....
Ideally, it would be great if the user never really saw the file open....just the results... ...and when the extraction is complete, i'd like the file that was just opened...to close...

Can this be done?

would love to hear your ideas...

thank you...CMEFLY....

[XLGibbs]

Of course it can be done. There are lots of similar purpose threads in the first couple of pages here..

Search for "Copying data from closed workbooks" and you should get a bunch of hits. (There are ways to open them and close them sight unseen)

If you post a sanitized sample of your data or you give some more information we can also help out (like how many files, all in the same place?, is the source of the data consistent in each file to open and extract from etc)

[lucas]

This works well for cells. uses same path as the activeworkbook. It's pretty well commented.
[VBA]Option Explicit
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating

'Make path selections below
' Set wb = Workbooks.Open("f:\Temp\Social Club.xls", True, True)
Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Social Club.xls")
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Final Results")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B8").Formula = wb.Worksheets("RESULTS").Range("B7").Formula
.Range("R8").Formula = wb.Worksheets("RESULTS").Range("R7").Formula

End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub[/VBA]

[mdmackillop]

This will return the data from closed files. In this example, I've used an array to list the worksheet names and cells, but other methods can be used as appropriate.

[vba]Option Explicit

Sub ProcessFiles()
Dim MyPath As String, MyName As String
Dim Sheet As String, Address As String
Dim z As Long, DataS, d

'List Path and Workbook
MyPath = "C:\AAA\"
MyName = "MyWorkbook.xls"
'List data locations
DataS = Array("Sheet1!A1", "Sheet2!A2", "Sheet3!A3")

z = 1
'Use Split to determine Sheet and Address
For Each d In DataS
Sheet = Split(d, "!")(0)
Address = Split(d, "!")(1)
'Write results to Sheet1, Column A
Sheets(1).Cells(z, 1) = GetData(MyPath, MyName, Sheet, Address)
z = z + 1
Next
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data As String
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
[/vba]