Mschart and Excel

[lintz]

Is it possible to use ADO in Excel to return a recordset?

I've got about 1000 rows for my data that will be plotted on a MSChart but I don't want to loop from row 1 to row 1000 to get all the values.

Can I use something like....

SELECT * FROM WorkSheetName Range A1 to A1000

[RobDog888]

Yes, but you need to add the reference to ADO in the VBA IDE. Then its about the same as it is from outside of Excel. Then when you have your recordset you can show it on your chart/form.

Why not use a chart inside Excel? Easier.

Moved from Classic VB forum.

[lintz]

Thanks RobDog, so would my example query work or would it need to be re-written?

SELECT * FROM WorkSheetName Range A1 to A1000

Also, if the spreadsheet was already open would it be a problem when connecting to it? If so, is there a way around it since it will be open when I want to run the query?

[RobDog888]

Its no problem.

You would want to use a querystring like this...

SELECT * FROM [Sheet1$A1:A1000]

[lintz]

I found the connection string from www.connectionstrings.com and this is what I've got. However it attempts to open up the spreadsheet again even though it's already open.

xlPath = App.Path & "\Research.xls"
 
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlPath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
 
sSQL = "SELECT DrawDown FROM [Trades Database$EG34:EG" & LastRow & "]"
rsSelectedTrades.Open sSQL, strCn 'This line attempts to open the spreadsheet again.

[RobDog888]

What do you mean "attempts"? Visibly opens another instance or errors?

[lintz]

Yes, visibly opens another instance so I then have two copies of my spreadsheet open....