Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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 "Paul" wrote in message ... | 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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 "Jim Rech" wrote: 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 "Paul" wrote in message ... | 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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=e...ing.google.com the URL should all be on one line. -- From: (Jamie Collins) Newsgroups: microsoft.public.excel.programming Subject: memory issue using ADO to query Excel Date: 16 Jun 2004 03:52:58 -0700 Organization: http://groups.google.com Lines: 93 Message-ID: References: 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: NNTP-Posting-Date: Wed, 16 Jun 2004 10:53:00 +0000 (UTC) "Dennis" wrote ... 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...;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. -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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... -- Nick ----------------------------------------------------------------------- Nicke's Profile: http://www.excelforum.com/member.php...nfo&userid=293 View this thread: http://www.excelforum.com/showthread.php?threadid=26952 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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 "Nicke" wrote: 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 ------------------------------------------------------------------------ Nicke's Profile: http://www.excelforum.com/member.php...fo&userid=2930 View this thread: http://www.excelforum.com/showthread...hreadid=269526 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
Hi, What do you want CurrentProject to return? Do you want to open an AD connection that reflects the values in the sheet -- Nick ----------------------------------------------------------------------- Nicke's Profile: http://www.excelforum.com/member.php...nfo&userid=293 View this thread: http://www.excelforum.com/showthread.php?threadid=26952 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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" wrote: Hi, What do you want CurrentProject to return? Do you want to open an ADO connection that reflects the values in the sheet? -- Nicke ------------------------------------------------------------------------ Nicke's Profile: http://www.excelforum.com/member.php...fo&userid=2930 View this thread: http://www.excelforum.com/showthread...hreadid=269526 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|