Using ADO CurrentProject in Excel

[Guest]

Hi All,
I could really use some help. I'm writing some VBA code in Excel and I want
to use an ADO connection to the data to populate a number of recordsets but
I'm having some trouble defining the connection. Does anyone know if there is
an Excel equivilent of Access's CurrentProject? I can define a connection to
the current database in Access like this:

Dim conGlobalConnection as new ADODB.Connection

conGlobalConnection.Open CurrentProject

This is much like DAO's Currentdb. I would like to use the same in Excel but
CurrentProject doesn't work and I can't find what it should be. I haven't
tried just using the path as I'm not sure where the SSheet will end up and so
would like to avoid declaring it that way, also I think the only time I've
tried this in the past I got read only errors.

Any help would be appreciated

Many Thanks
Paul

 

[Jim Rech]

I know little about Access but per Access VBA help CurrentProject is:

"The CurrentProject object refers to the project for the current Microsoft
Access project (.adp) or Access database (.mdb)."

So are you saying that the user will have Access open and a database loaded
and you want Excel to be able to find out what MDB Access is currently
using?

Bit if Access is not open there can be no CurrentProject, right?

If Access is not running and you're concerned about the path to the MDB
being variable you'd have to ask the user for the path. You can use the DIR
function to see if the MDB is where you think it should be and, if it's not,
use Application.GetOpenFilename to ask the user where it is.

--
Jim Rech
Excel MVP
| Hi All,
| I could really use some help. I'm writing some VBA code in Excel and I
want
| to use an ADO connection to the data to populate a number of recordsets
but
| I'm having some trouble defining the connection. Does anyone know if there
is
| an Excel equivilent of Access's CurrentProject? I can define a connection
to
| the current database in Access like this:
|
| Dim conGlobalConnection as new ADODB.Connection
|
| conGlobalConnection.Open CurrentProject
|
| This is much like DAO's Currentdb. I would like to use the same in Excel
but
| CurrentProject doesn't work and I can't find what it should be. I haven't
| tried just using the path as I'm not sure where the SSheet will end up and
so
| would like to avoid declaring it that way, also I think the only time I've
| tried this in the past I got read only errors.
|
| Any help would be appreciated
|
| Many Thanks
| Paul

 

[Nicke]

Hi,

What do you want CurrentProject to return? Do you want to open an AD
connection that reflects the values in the sheet

 

[Guest]

Sorry Jim, I've confused the issue by talking about Access! I'm not using
Access at all. I'm using Excel. I'm writing a VBA module that will manipulate
the data in the current workbook for me. However I need to connect to the
current workbook/worksheet using ADO, so that I can populate a number of
different recordsets, but don't know what to use as the provider string for
the ADO connection. I was hoping there was a simple way of setting the
adodb.connection to the current workbook?

Does that clarify my query?

Sorry for the confusion,

Paul

 

[Tom Ogilvy]

ThisWorkbook refers to the workbook containing the code

ActiveWorkbook refers to the workbook that is top most in Excel

Activesheet is the topmost worksheets (the sheet with the focus).

----------------------------------

Previously posted by Jamie Collins:

http://groups.google.com/groups?hl=en&lr=&[email protected]


the URL should all be on one line.
--

From: (e-mail address removed) (Jamie Collins)
Newsgroups: microsoft.public.excel.programming
Subject: Re: memory issue using ADO to query Excel
Date: 16 Jun 2004 03:52:58 -0700
Organization: http://groups.google.com
Lines: 93
Message-ID: <[email protected]>
References: <[email protected]>
NNTP-Posting-Host: 81.171.142.210
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1087383180 1480 127.0.0.1 (16 Jun 2004 10:53:00
GMT)
X-Complaints-To: (e-mail address removed)
NNTP-Posting-Date: Wed, 16 Jun 2004 10:53:00 +0000 (UTC)

I set up a SQL text box to run queries on a 38k rows by
100 columns using ADO. I've got to working fine.
However, after about 10 query requests, I receive
insufficient memory errors and I'm forced to shut down
Excel. I clear my recordset variable after each query. I
was wondering if there is some cache that should be
cleared. All of my queries are SELECT queries.

Are you querying an open workbook?

Microsoft Knowledge Base Article - 319998
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO

If this applies, save the worksheet to a temporary workbook, close it
and query the closed workbook. Here's some example code:

Option Explicit

Sub Test()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String

' Amend the following constants to suit
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"
Const TABLE_NAME_CURRENT As String = "" & _
"MySheet"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection strings
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH>", strPath)
strCon = Replace(strCon, _
"<FILENAME>", FILENAME_XL_TEMP)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "SELECT Col1 FROM "
strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]"
' strSql1 = strSql1 & " WHERE Co2=1 OR Col2=3"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _
Copy .Worksheets(1)
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.CursorLocation = 3
.Open
Set rs = .Execute(strSql1)
End With

' <<do something with recordset>>

rs.Close
Con.Close

End Sub

Jamie.

 

[Guest]

Hi,

I want the code to look something like this:

Dim conCurrentWorkbook as new ADODB.Connection
Dim rstSectors as new ADODB.Recordset
Dim strSQL as string

conCurrentWorkbook.Open <this is the bit I don't know, I'm assuming
CurrentProject or similar>

strSQL = "Select * from Sheet1"
rstSectors.open strSQL, conCurrentWorkbook, adOpenKeyset, adLockOptimistic

do while rstSectors.eof = false
'do stuff
rstSectors.MoveNext
Loop

If that helps?

Paul

 

[Nicke]

You can try this,

Dim c As ADODB.Connection
Dim Rs As Recordset
Dim Book as string

Set c = New ADODB.Connection
Book = ActiveWorkbook.Fullname


c.Open "Provider=Microsoft.Jet.OLEDB.4.0" & ";Data Source=" & Boo
& ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

Set Rs = New ADODB.Recordset

Rs.ActiveConnection = c

Rs.Open "SELECT * from [Sheet1$]"

While Not Rs.EOF...

 

[Guest]

Hi Nicke, that's nearly working! The connection goes through fine but it
doesn't recognise the sheet name in the select statement. I've changed the
name to the name of my sheet and i've tried it with and without the square
brackets but still no joy. Any tips I could try?

Thanks for your help
Paul

 

[Tom Ogilvy]

Not concerned about the memory leak then?

--
Regards,
Tom Ogilvy

Hi Nicke, that's nearly working! The connection goes through fine but it
doesn't recognise the sheet name in the select statement. I've changed the
name to the name of my sheet and i've tried it with and without the square
brackets but still no joy. Any tips I could try?

Thanks for your help
Paul

You can try this,

Dim c As ADODB.Connection
Dim Rs As Recordset
Dim Book as string

Set c = New ADODB.Connection
Book = ActiveWorkbook.Fullname


c.Open "Provider=Microsoft.Jet.OLEDB.4.0" & ";Data Source=" & Book
& ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

Set Rs = New ADODB.Recordset

Rs.ActiveConnection = c

Rs.Open "SELECT * from [Sheet1$]"

While Not Rs.EOF....

 

[Guest]

Yeah I am, but I'll come to that bit when I've got the basics sorted out! I'm
struggling to even query the spreadsheet at the moment!!

Not concerned about the memory leak then?

--
Regards,
Tom Ogilvy

Hi Nicke, that's nearly working! The connection goes through fine but it
doesn't recognise the sheet name in the select statement. I've changed the
name to the name of my sheet and i've tried it with and without the square
brackets but still no joy. Any tips I could try?

Thanks for your help
Paul

You can try this,

Dim c As ADODB.Connection
Dim Rs As Recordset
Dim Book as string

Set c = New ADODB.Connection
Book = ActiveWorkbook.Fullname


c.Open "Provider=Microsoft.Jet.OLEDB.4.0" & ";Data Source=" & Book
& ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

Set Rs = New ADODB.Recordset

Rs.ActiveConnection = c

Rs.Open "SELECT * from [Sheet1$]"

While Not Rs.EOF....

 

[Nicke]

Hi,

You must have [ ] and a $ at the end of the sheetname, i.e. [mySheet$]

Look here for more info:

www.connectionstrings.co

 

[Patrick Molloy]

Open a new workbook, go to the IDE and set a reference to Microsoft Active
Data Objects 2.7 Library

Add the following code:

Option Explicit
Property Get MyDatabase() As String
MyDatabase = ControlsA1
End Property
Property Get MyServer() As String
MyServer = ControlsA2
End Property

Sub LoadFromSQL()
Dim RST As ADODB.Recordset
Dim db As Connection
Dim SQL As String
Dim i As Long
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=" & MyServer &
";uid=;pwd=;database=" & MyDatabase & ";"
Set RST = New Recordset
SQL = "SELECT DISTINCT [Instrument] FROM PL"
RST.Open SQL, db, adOpenStatic, adLockOptimistic
' prepare active sheet
Cells.ClearContents
With RST
For i = 0 To .Fields.Count - 1
Cells(1, i + 1).Value = .Fields(i).Name
Next
End With
Range("A2").CopyFromRecordset RST
RST.Close
db.Close
Set RST = Nothing
Set db = Nothing
End Sub




http://www.xl-expert.com/html_pages/dataConnectivity_SQL.html

 

[Guest]

Hi Paul:
I trying to understand what you want to accomplish.
Lets say everyone here understands that ADO
(ActiveX Data Objects ) are objects, properties,
methods , arguments and events in Visual Basic
primarily used to connect to and manipulate records
returned from databases.
What is the purpose of using ADO to connect to
Excel when you are in Excel and further you cannot
use ADO methods to manipulate the rs if you are not
in Visual Basic.
Are you sending the rs to other backends ?

Good Luck
TK

 

[Guest]

Hi Paul:

I played around with this and have some neet stuff
if you are still working on it.

Post back if you would like the code.

Good Luck
TK

 

[Guest]

First of all, thanks for all your help guys. I've got some very useful stuff
to try now.

TK - I would love to see any code that you've got. You can never have to
many different examples of ways to do things I always say!

As an aside I'll give you the brief of what I'm trying to accomplish. My
data is split with details of Business Sector (Food, Electronics, Clothing
for example), Stage (completed, outside deadline, outstanding), Source
(Annual Report, News Item), Source Date (>Now - 365) and Research Type (eg In
house). I was going to use a recordset to set up a kind of pivot table that
would provide me with a summary of the data. EG this macro would produce this
table for all in house research:

Source: Annual Report (date used to find latest)
Sector Completed Outside Deadline
Food 1 3
Electronics 7 0
Clothing 9 1

etc. This table would be produced for each sector/source and the figures are
a count satisfying the 5 mentioned criteria.

Does that make any sense? Hope so! If anyone can think of a better way to do
it then I'd love to hear it?

Thanks
Paul