PDA

View Full Version : [SOLVED] Getting Data From a Closed Workbook



YellowLabPro
09-21-2007, 08:01 AM
I have found a few different methods on the net to get data from a closed book.
Johnske's method here seems the most straightforward- #2
http://www.vbaexpress.com/forum/showthread.php?t=10474&highlight=data+from+closed+workbook

Also:
http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado

I have not tested either.
On Erlandsendata's method- Do I just add in lines to dim the workbooks and the sheets and then Set them?
Or do these get changed in the arguments in the first line?



Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
' this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
' this will return data from any worksheet in SourceFile
' SourceRange must include the range headers
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(1, 1)
If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
TargetCell.CopyFromRecordset rs
rs.Close
dbConnection.Close ' close the database connection
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Sub
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Sub

Bob Phillips
09-21-2007, 08:08 AM
Surely the easiest method is to open the workbook. The overhead is not much more than ADO unless the workbook is very large.

YellowLabPro
09-21-2007, 08:33 AM
Bob,
Right.... that is why I was looking into doing it while it was closed. The source book is currently 30mgs.
It needs to pull about 6 columns and 15,000 rows.

If I do access w/ one of the procedures above, if the workbook happens to be open and I run the code, will this present a problem/error?

Bob Phillips
09-21-2007, 08:44 AM
No it will not present a problem, but it will access the disk version. That is, any updates in the memory version will not be picked up. It would be best to test if the file is open before taking any action.

YellowLabPro
09-21-2007, 08:56 AM
ok... when I get it to that point I will re-post up my working code.
So circling back to the OP question please....

Bob Phillips
09-21-2007, 09:49 AM
No, you don't dim anything, just pass the SourceFile and the SourceCells or named range as the SourceRange parameter values.

YellowLabPro
09-21-2007, 10:02 AM
Yes that was the other option, to pass the SourceFile to the parameter values- would I just replace the word SourceFile, SourceCells w/ the actual names in the parameter values?
It seems a little too easy this way if that is all it is.
I will try this and post back,

Bob Phillips
09-21-2007, 10:48 AM
We may be saying the same thing, but SourceFile and SourceRange are the parameters. So as I read it, you would just do



Call GetDataFromClosedWorkbook(SourceFile:="C:\Dir\Subdir\Filenme.xls", _
SourceRange:="A1:A10", _
TargetRange:=Range("A1"), _
IncludeFieldNames:=False)

Aussiebear
09-21-2007, 02:59 PM
Bob, if you wanted info from a particular sheet eg Sales, would the SourceRange then be:

SourceRange:= "!Sales A1:A10"

Bob Phillips
09-21-2007, 03:11 PM
Not sure Ted, haven't looked closely enough at the code. But you can always use a Named Range, and pass that name.

Bob Phillips
09-21-2007, 03:18 PM
Just tried it, and it doesn't work. Must be because of the way the ADO driver sees the workbook,, only seeing the first sheet, but being able to see any other tables (named ranges)

Aussiebear
09-22-2007, 01:18 AM
So then, opening the other workbook becomes critical?

Bob Phillips
09-22-2007, 01:56 AM
No, use a named range in the book, or get the whole sheet.

rory
09-22-2007, 06:32 AM
To get a range from a specific sheet, you need to pass the second argument like this:
"Sales$A1:F10"
and it should work.

YellowLabPro
09-23-2007, 06:11 AM
I need a hand here please ...
I have this working w/ the first sheet in the source workbook.
I also have it working by getting data from a sheet other than the first sheet by setting up a named range.
However, if I want to setup a dynamic named range, I get an error:
"The Source File or Source Range Is Invalid"

Edit: Originally tested on small range. This has now been increased to reflect the entire range, plus an additional 5000 rows to accomodate for new data entering until a dynamic range solution is found.
The non-dynamic named range is
=DataPrep!$A$2:$H$20000

The dynamic named range is
=OFFSET(DataPrep!$A$1,1,0,COUNTA(DataPrep!$A:$A)-1,8)

rory
09-23-2007, 06:30 AM
To the best of my knowledge, there is no way to do it with defined names that aren't a simple address - ADO can't work out what they refer to because it can't calculate the Excel functions involved.

YellowLabPro
09-23-2007, 11:18 AM
Thanks Rory...
I think I will have to conceed on this one, your solution makes a lot of sense....