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
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 |
#4
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 |
#5
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 |
#6
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 |
#7
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 |
#8
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
Not concerned about the memory leak then?
-- Regards, Tom Ogilvy "Paul" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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!! "Tom Ogilvy" wrote: Not concerned about the memory leak then? -- Regards, Tom Ogilvy "Paul" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
Hi, You must have [ ] and a $ at the end of the sheetname, i.e. [mySheet$] Look here for more info: www.connectionstrings.co -- Nick ----------------------------------------------------------------------- Nicke's Profile: http://www.excelforum.com/member.php...nfo&userid=293 View this thread: http://www.excelforum.com/showthread.php?threadid=26952 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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 --------------------------------- "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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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 --------------------------------- "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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using ADO CurrentProject in Excel
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 "TK" wrote: 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 --------------------------------- "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|