Referencing sheets in closed workbooks with VBA 1

[Fenrirshowl]

Hello all

Question:
Is it possible using VBA to retrieve information from closed workbooks without opening them (similar to when working with text files via object.OpenTextFile)?

If it is, can someone please give me a few keywords to allow me to do some research?

The aim would be equivalent to cycling through folders and subfolders, extracting the entries on the first line of each sheet in each workbook - essentially retrieving all column titles for a set of workbooks used to store employee-type data so we can cross-check entries more easily.
(I appreciate the data would be better stored in a database app, but our company doesn't like or trust Access. [dont ask!])

Thanks in advance

Fen

 

[PHV]

retrieving all column titles
You may use an ADOX.Catalog object to retrieve such info.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Fenrirshowl]

Many thanks - I will have a look into it.

Cheers

Fen

 

[GlennUK]

You could use ExecuteExcel4Macro to run a cell formula to get a single cells information:

http://groups.google.com/group/microsoft.public.excel.programming/msg/265e1754c5048357

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Fenrirshowl]

Thanks Glenn, that could be very useful.

I managed to incorporate it to a pick up a workbook & cell reference in A1 style in a workbook cell (as an input entry) and then return the required value using the following:

Sub referenceclosedworkbook()
Dim locn As Range
Dim posn As Integer
Dim clref As String
Dim newlocn As String
Dim newerlocn As String

Set locn = Range("locn")
 
posn = InStr(1, locn, "!")
clref = Right(locn, Len(locn) - posn)
    
newlocn = Application.ConvertFormula( _
    Formula:=clref, _
    fromReferenceStyle:=xlA1, _
    toReferenceStyle:=xlR1C1, toabsolute:=True)

newerlocn = "'" & Left(locn, posn) & newlocn

MsgBox ExecuteExcel4Macro("" & newerlocn & "")

End Sub

Given that this effectively reads the closed workbook, is there a similar method to write to a closed workbook - this is just for curiosity, not something I need to do.

Cheers

Fen

 

[feipezi]

Yes.
Here is one of the ways of doing it.


Function GetValuesFromAClosedWorkbook(fPath, fName, sName, c_Rng, b_Rng As String)
With ActiveSheet.Range(b_Rng)
.FormulaArray = "='" & fPath & "\[" & fName & "]" & sName & "'!" & c_Rng
.Value = .Value
End With
End Function


I used it many times and it worked fine. However, 'c_rng' and 'b_rng' must be specific, i.e., "A1:A100". You cannot use Range("a1").CurrentRegion.Address things like that. I tried but it didn't work. Because you cannot get CurrentRegion from a closed workbook.
But I think it's better than ExecuteExcel4Macro since it can give you a range bigger than a single cell.
Hope this helps.