Grab worksheet From Closed Workbook

[artrookie]

Hi
Not sure if this can be done on excel I was wondering if it was possible to grab a worksheet full of data from a closed workbook?

I have used ExecuteExcel4Macro to grab single cell data from closed workbook - but it is not very efficient grabbing large chunks of data. I work with many large excel files most 20+ mpegs, and run alot of scenarios on these. Some large files take over 5 minutes to open, if it was possible to grab a worksheet without actually opening the file it would be much more efficient

Thanks

artrookie

[Bob Phillips]

You can do it with ADO

[vba]

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\test\ADO test.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sheet1$]"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then

Sheet1.Range("A1").CopyFromRecordset oRS
Else

MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub
[/vba]

[artrookie]

Thanks again Xld - I have a run-time '3001' error though "arguments are of the wrong type, are out of acceptable range, or are conflict with one another" if you could help? The only change I made is regarding inputting the sFilename . The line with the error is

oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

I have to admit this ADO is new to me - so I have no idea how to fix this

Thanks

[Bob Phillips]

Sorry, I missed a bit

[vba]

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\test\ADO test.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sheet1$]"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then

Sheet1.Range("A1").CopyFromRecordset oRS
Else

MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub
[/vba]

[artrookie]

Thanks Xld, this is amazing

[Bob Phillips]

It gets better as you get to know SQL, as you can filter the data retrieved via the SQL code.

[artrookie]

Hi Xld

I have follow up question, the macro seems to be half working. In that it paste about 1/3 of the data from the unopened excel file, the rest are not pasted through. There does not seem to be a pattern in what data goes through, would you know what possible reason it is? and how the coding can be adjusted? Lastly (I know alot of questions) where is recommended site to learn more about basic ADO?

Thanks

artrookie

[Bob Phillips]

Is the file multiple sheets or just the one?

[artrookie]

Just one sheet, but this one sheet has data scattered from A1:FW1000. If I reduce the range would that ensure all data goes through?

Thanks

[Bob Phillips]

No, if you specify the whole range it should all be retrieved.

[GTO]

No, if you specify the whole range it should all be retrieved.

@XLD
Good morning sir. You must be on DST, as we were 8 hrs apart?
At hand: I have very/extremely/minute experience ADO/sequel; but was watching to see if this happened.

In your current example, row one's info (from the book being queried) disappears. (Boo, eh?)

Also, and a bit more critical, is that data of different types, at a minimum, when in a contiguous range, either disappears (text) or gives spotty results.

Copy both files to same sub-directory, open SQL.xls (your code). I didn't bother with buttons; run ShOne or ShTwo.

If you can get it to read everything in one swipe, well golly, not only the OP will be happy! (This was one of the reasons I started working on delineating the various strings necessary for a temp link (FormulaArray) and/or workaround refedit)


@artrookie:

By chance, does consistency exist in the full path to the books being looked at? To wit, will they all be in the same path, will the wb's all be named in a like manner, and the sheets as well? Mostly I am asking about spaces (vs none) in the path/wb/sheet names.

Very interesting problem , but may be unable to check back until tomorrow night (my time) .

Mark

[GTO]

Oops, addendum:

I should have mentioned that I changed to various formats (zip, percentage, etc) in the cells below "mary had a little lamb". Sorry about that.

Mark

[OhGorgeous1]

Morning GTO from the UK

I have a question regarding the above code, how would you go about giving the user the choice to pick the file that want to import from?

I have a workbook that imports but it opens the file so makes the exersice very slow.

Would appreciate the help
Niki

[GTO]

Morning GTO from the UK

I have a question regarding the above code, how would you go about giving the user the choice to pick the file that want to import from?

I have a workbook that imports but it opens the file so makes the exersice very slow.

Would appreciate the help
Niki

Greetings Niki and nice to "meet",

The code is XLD's, and I already tossed in a clarification/question relating to what I suspect the OP is experiencing. Let's please wait for Bob's response, so the issue isn't muddled.

After that, and regardless of whether ADO can snag the data in one swipe, then you could post a thread asking how to get the user's choice and I would be utterly happy to answer.

Thank you so much (from the other side of the pond),

Mark

PS - it's actually 'good-night' as I'm up far too late. As mentioned,may not be able to return til Thursday night or Friday (my time). Thanks again.

[OhGorgeous1]

Greetings Niki and nice to "meet",

PS - it's actually 'good-night' as I'm up far too late. As mentioned,may not be able to return til Thursday night or Friday (my time). Thanks again.

Will do, and have happy dreams!

[Bob Phillips]

You must be on DST, as we were 8 hrs apart?
At hand: I have very/extremely/minute experience ADO/sequel; but was watching to see if this happened.

Yep, clocks went back last weekend.

In your current example, row one's info (from the book being queried) disappears. (Boo, eh?)

ADO is querying tables, tables have columns, and columns have names. In an Excel 'table' there is no metadata for the name etc., so the Excel driver treats row 1 as the column names. Fair assumption as this is where you would normally put the headings.

You get at them via the Fields property of the recordset.

Also, and a bit more critical, is that data of different types, at a minimum, when in a contiguous range, either disappears (text) or gives spotty results.

This is the way that the data is interpreted. Again, as you have no meta-data as to what the datatype is, the driver has to 'guess'. It reads a few rows and makes a smart assumption from this, and then treats everything as that datatype.

Of course this can mean problems if you mix datatypes in a column, but why would you? If you have multiple tables on the same sheet, some in the same columns, don't read the whole sheet, read each table separately.

[Bob Phillips]

Morning GTO from the UK

I have a question regarding the above code, how would you go about giving the user the choice to pick the file that want to import from?

I have a workbook that imports but it opens the file so makes the exersice very slow.

Would appreciate the help
Niki

Just add a FileDialog to allow a user selection of the target file.

[GTO]

@XLD:
Ohhh I am going to be sooo tired; but couldn't stop myself, as this topic is very interesting to me.

Thank you for the reminder/new knowledge, as (after your saying it) I do recall the issue of a 'header row' being assumed. I did not know about Fields property though, and of course wouldn't have recalled the header row...

As to the second part, zero knowledge as to "meta-data" but do I gather correctly that the driver is reading row-by-row in ea given column (L to R presumably)? Your answer is plenty clear enough, I just want to make sure for my own education.

Just one sheet, but this one sheet has data scattered from A1:FW1000. ...

As to the "...why would you...?", shucks, where I'm at 'forms' (wb's) are more often than not, layed out in a 'whatever looks neat/easy to read when printed' layout, and all sorts of different junk is/may be in the same column. Shift/Squad rosters, OT reports, etc, ad nauseum.

@artrookie:
At this point... maybe an example (no private info) wb, or descript as to whether columns have different data types?

A good day (or night for me) to all, will check back for sure,

Mark

[Bob Phillips]

As to the second part, zero knowledge as to "meta-data" but do I gather correctly that the driver is reading row-by-row in ea given column (L to R presumably)? Your answer is plenty clear enough, I just want to make sure for my own education.

If you are familiar with Access , you will know that when you define a database you give it a name, you add tables and name them, you add columns and give them a datatype maybe a key assignation and so on. This is meta-data, it is stored with the database, and can be queried by ADO drivers to help process the data.

Excel (and text files and many others) do not have such meta-data, so the ADO driver has to deduce that stuff some other way. If it couldn't, you wouldn't be able to issue SQL commands such as "Select * From myTable Where Name = 'Bob'". ADO gives us tremendous potential, but it ain't perfect with non-structured data repositories.

And it doesn't go through every row, I forget how many, but it only reads in a few and determines the datatype to apply to the whole column from that. As for L-R, that is irrelevant (think in terms of a database table not an Excel spreadsheet), it is working on table columns, so it works by the column names in the query, it is totally Excel agnostic at this point.

As to the "...why would you...?", shucks, where I'm at 'forms' (wb's) are more often than not, layed out in a 'whatever looks neat/easy to read when printed' layout, and all sorts of different junk is/may be in the same column. Shift/Squad rosters, OT reports, etc, ad nauseum.

Sure, that is not the problem, but why would you put a date and a name and say an income in the same column, that just doesn't make sense in Excel, never mind trying to run a structured query on the data.

As I say, if your sheet contains many tables, you approach it slightly differently.

[artrookie]

omg, hahaha - I need to borrow SQL book from library - so many new terms in this thread