Accesar Excel con ADO

hola, JC !

cuando se usa ADO para tomar como "base de datos" un archivo de excel
lo recomendable es: que el archivo que se consulta este cerrado
de lo contrario (si consultas un libro abierto o el mismo) dejaras "proyectos fantasma" en el editor de vba

otra (posible) "herencia" es que no se estan "destruyendo" los objetos creados (por cada instruccion Set <variable> = ...)
y cada vez que ejecutas esas macros vas dejando "recursos disminuidos" (como memoria)

y (por si las dudas) comprueba que las "nuevas" cadenas de conexion (link sugerido) no corrijan este comportamiento (?)

saludos,
hector.

__ OP __

Redireccione el recordset a otra cache de otra tabla dinamica pero sucede algo extrano (y no se porque):
Ejecuto el procedimiento paso a paso la primera vez y se ejecuta correctamente, es decir
asigna al cache de la tabla dinamica existente el recordset PERO la segunda vez que quiero ejecutar paso a paso
me da el mismo error cuando llega a la asignacion del recordset: "El metodo recordset del objeto PivotCache fallo".

Porque sirve para la primera vez y no para la segunda?
Quiere decir que el procedimiento esta correcto solo que debe de existir una consideracion a nivel de recordset
que me impide ejecutarlo por segunda vez sin que me de el error?

Tienes alguna idea de a que pueda deberse?

Hay alguna forma de hacerte llegar el archivo para que veas a que se puede deber?

Hola Hector

Si, eso es lo que estoy haciendo, manipulando una de las hojas del libro
donde estoy trabajado. Si esto es un problema, crees que una alternativa sera
utilizar el "copyfromrecordset" para trasladar la informacion ya agrupada a
otra hoja del mismo libro y despues actualizar la tabla dinamica? Aunque
hubiera querido que se pasara directamente al recordset de la tabla :-(

Con respecto a las variables tanto la coneccion como el recordset se cierran
y se limpian con el Nothing. Lo que No se es si tengo que hacerlo con el
cache que al final igualo a Nothing tambien paro no se si de la forma
correcta.

Por ultimo: Como compruebo que las "nuevas" cadenas de conexion (link
sugerido) no corrijan este comportamiento?

Aclaro, no se si estas confundiendo[me] con otro colistero que se "llama" JC
y que he visto tiene un conocimiento avanzado de Excel y yo no seria el caso.

Te adjunto de nuevo el codijo, Cualquier ayuda...

Sub Prueba()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim PC As PivotCache
Dim PT As PivotTable

Set cn = New ADODB.Connection
With cn
' Codijo para OLE DB
'.Provider = "MSDASQL"
'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _

'"DBQ=C:\Users\Owner\Documents\Pollazo\Rendimientos\ControlMermaCartago.xls;
ReadOnly=False;"
'.Open

.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data
Source=C:\Users\Owner\Documents\Rendimientos\ControlCartago.xls;" & _
"Extended Properties=Excel 8.0"
.Open
End With

Set rst = New ADODB.Recordset

With rst
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With

rst.Open "SELECT SUM(Cantidad) AS Cantidad, HH, FECHA, Media, Dia FROM
[VentasPolloHora$A1:F15000]" & _
"GROUP BY HH, FECHA, Media, Dia Order by Fecha", cn, , , adCmdText

'' ThisWorkbook.Worksheets("Sheet4").Range("A2").CopyFromRecordset rst

Set PC = ActiveWorkbook.PivotCaches(1)
Set PC.Recordset = rst
Set PT = ActiveSheet.PivotTables("Tabla dinámica1")
PT.PivotCache.Refresh

rst.Close
Set rst = Nothing
cn.Close
Set cn = Nothing
Set PC.Recordset = Nothing
Set PT.PivotCache = Nothing


End Sub

JC.

"Héctor Miguel" wrote:

hola, JC !

cuando se usa ADO para tomar como "base de datos" un archivo de excel
lo recomendable es: que el archivo que se consulta este cerrado
de lo contrario (si consultas un libro abierto o el mismo) dejaras "proyectos fantasma" en el editor de vba

otra (posible) "herencia" es que no se estan "destruyendo" los objetos creados (por cada instruccion Set <variable> = ...)
y cada vez que ejecutas esas macros vas dejando "recursos disminuidos" (como memoria)

y (por si las dudas) comprueba que las "nuevas" cadenas de conexion (link sugerido) no corrijan este comportamiento (?)

saludos,
hector.

__ OP __
> Redireccione el recordset a otra cache de otra tabla dinamica pero sucede algo extrano (y no se porque):
> Ejecuto el procedimiento paso a paso la primera vez y se ejecuta correctamente, es decir
> asigna al cache de la tabla dinamica existente el recordset PERO la segunda vez que quiero ejecutar paso a paso
> me da el mismo error cuando llega a la asignacion del recordset: "El metodo recordset del objeto PivotCache fallo".
>
> Porque sirve para la primera vez y no para la segunda?
> Quiere decir que el procedimiento esta correcto solo que debe de existir una consideracion a nivel de recordset
> que me impide ejecutarlo por segunda vez sin que me de el error?
>
> Tienes alguna idea de a que pueda deberse?
>
> Hay alguna forma de hacerte llegar el archivo para que veas a que se puede deber?


.

hola, JC ! (entre las lineas de tu mensaje)...
___

Si, eso es lo que estoy haciendo, manipulando una de las hojas del libro donde estoy trabajado.
Si esto es un problema, crees que una alternativa sera utilizar el "copyfromrecordset"
para trasladar la informacion ya agrupada a otra hoja del mismo libro y despues actualizar la tabla dinamica?
Aunque hubiera querido que se pasara directamente al recordset de la tabla :-(

me refiero a que al consultar sobre el mismo libro (de la macro con ADO) se crean los proyectos "fantasma" en vba
a menos que hagas un "thisworkbook.savecopyas ..." y trabajes el ADO con la copia del archivo
___

Con respecto a las variables tanto la coneccion como el recordset se cierran y se limpian con el Nothing.
Lo que No se es si tengo que hacerlo con el cache que al final igualo a Nothing tambien paro no se si de la forma correcta.

solo comentar que en la consulta original NO contenia la destruccion ( ... = nothing) de los objetos de la TD (?)
___

Por ultimo: Como compruebo que las "nuevas" cadenas de conexion (link sugerido) no corrijan este comportamiento?

es probable que debas cambiar las cadenas de conexion las "extended properties" de "excel 8" a "excel 12" (por las TD en 2007 ?)

__

... no se si estas confundiendo[me] con otro colistero que se "llama" JC y que he visto tiene un conocimiento avanzado de Excel...

solo me he estado dirigiendo a "quien" propuse los procedimientos Sub CrearTablaADO_RS(), y Sub ModificarADO_RS_de_Tabla()
___

Te adjunto de nuevo el codijo, Cualquier ayuda...

(creo que) vendria bien (ahora si) si subes tu archivo en algun sitio publico y proporcionas el enlace para su descarga (?)
(o enviamelo si quieres a: hemiordi<con dominio de>gmail.com

saludos,
hector.

__ el codigo expuesto __

Sub Prueba()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim PC As PivotCache
Dim PT As PivotTable
Set cn = New ADODB.Connection
With cn
' Codijo para OLE DB
'.Provider = "MSDASQL"
'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
'"DBQ=C:\Users\Owner\Documents\Pollazo\Rendimientos\ControlMermaCartago.xls; ReadOnly=False;"
'.Open
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Users\Owner\Documents\Rendimientos\ControlCartago.xls;" & _
"Extended Properties=Excel 8.0"
.Open
End With
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With
rst.Open "SELECT SUM(Cantidad) AS Cantidad, HH, FECHA, Media, Dia FROM [VentasPolloHora$A1:F15000]" & _
"GROUP BY HH, FECHA, Media, Dia Order by Fecha", cn, , , adCmdText
'' ThisWorkbook.Worksheets("Sheet4").Range("A2").CopyFromRecordset rst
Set PC = ActiveWorkbook.PivotCaches(1)
Set PC.Recordset = rst
Set PT = ActiveSheet.PivotTables("Tabla dinà¡mica1")
PT.PivotCache.Refresh
rst.Close
Set rst = Nothing
cn.Close
Set cn = Nothing
Set PC.Recordset = Nothing
Set PT.PivotCache = Nothing
End Sub

Hola Hector

Con respecto a las "extended properties" ya habia probado con 12.0 pero me
da error.

Con a limpieza de las variables estamos OK.

Ya ten envie el archivo para que le des un vistaso a los proyectos fantasmas
para ver si es eso.

Saludos!!!
JC

"Héctor Miguel" wrote:

hola, JC ! (entre las lineas de tu mensaje)...
___
> Si, eso es lo que estoy haciendo, manipulando una de las hojas del libro donde estoy trabajado.
> Si esto es un problema, crees que una alternativa sera utilizar el "copyfromrecordset"
> para trasladar la informacion ya agrupada a otra hoja del mismo libro y despues actualizar la tabla dinamica?
> Aunque hubiera querido que se pasara directamente al recordset de la tabla :-(

me refiero a que al consultar sobre el mismo libro (de la macro con ADO) se crean los proyectos "fantasma" en vba
a menos que hagas un "thisworkbook.savecopyas ..." y trabajes el ADO con la copia del archivo
___
> Con respecto a las variables tanto la coneccion como el recordset se cierran y se limpian con el Nothing.
> Lo que No se es si tengo que hacerlo con el cache que al final igualo a Nothing tambien paro no se si de la forma correcta.

solo comentar que en la consulta original NO contenia la destruccion ( ... = nothing) de los objetos de la TD (?)
___
> Por ultimo: Como compruebo que las "nuevas" cadenas de conexion (link sugerido) no corrijan este comportamiento?

es probable que debas cambiar las cadenas de conexion las "extended properties" de "excel 8" a "excel 12" (por las TD en 2007 ?)

__
> ... no se si estas confundiendo[me] con otro colistero que se "llama" JC y que he visto tiene un conocimiento avanzado de Excel...

solo me he estado dirigiendo a "quien" propuse los procedimientos Sub CrearTablaADO_RS(), y Sub ModificarADO_RS_de_Tabla()
___
> Te adjunto de nuevo el codijo, Cualquier ayuda...

(creo que) vendria bien (ahora si) si subes tu archivo en algun sitio publico y proporcionas el enlace para su descarga (?)
(o enviamelo si quieres a: hemiordi<con dominio de>gmail.com

saludos,
hector.

__ el codigo expuesto __
> Sub Prueba()
> Dim cn As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim PC As PivotCache
> Dim PT As PivotTable
> Set cn = New ADODB.Connection
> With cn
> ' Codijo para OLE DB
> '.Provider = "MSDASQL"
> '.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
> '"DBQ=C:\Users\Owner\Documents\Pollazo\Rendimientos\ControlMermaCartago.xls; ReadOnly=False;"
> '.Open
> .Provider = "Microsoft.Jet.OLEDB.4.0"
> .ConnectionString = "Data Source=C:\Users\Owner\Documents\Rendimientos\ControlCartago.xls;" & _
> "Extended Properties=Excel 8.0"
> .Open
> End With
> Set rst = New ADODB.Recordset
> With rst
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> .LockType = adLockOptimistic
> End With
> rst.Open "SELECT SUM(Cantidad) AS Cantidad, HH, FECHA, Media, Dia FROM [VentasPolloHora$A1:F15000]" & _
> "GROUP BY HH, FECHA, Media, Dia Order by Fecha", cn, , , adCmdText
> '' ThisWorkbook.Worksheets("Sheet4").Range("A2").CopyFromRecordset rst
> Set PC = ActiveWorkbook.PivotCaches(1)
> Set PC.Recordset = rst
> Set PT = ActiveSheet.PivotTables("Tabla dinà¡mica1")
> PT.PivotCache.Refresh
> rst.Close
> Set rst = Nothing
> cn.Close
> Set cn = Nothing
> Set PC.Recordset = Nothing
> Set PT.PivotCache = Nothing
> End Sub


.

hola, JC !

Ya te envie el archivo para que le des un vistaso a los proyectos fantasmas para ver si es eso...

creo recordar que a partir de la version 2007 se da un tratamiento distinto a la colecion "caches" de las tablas de datos
estas son las correcciones/adaptaciones/... que hice a tus codigos expuestos y la macro resultante (al final)
corri varias ejecuciones en la version 2007 y no obtuve mas errores ;)

saludos,
hector.

usas windows vista o superior ?...
FALTA: Microsoft ActiveX Data Objects 6.0 Library

la cambio por la version 2.8 (windows-xp)

cambio la linea de apertura de la conexion de esto:
.ConnectionString = "Data Source=C:\Users\Owner\Documents\Pollazo\Rendimientos\ControlMermaCartago.xls;" & _
"Extended Properties=Excel 8.0"

a esto (para no tener que reproducir la misma ubicacion de tu cadena de texto "constante":
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 8.0"

invierto el orden de creacion de los objetos de la TD de esto:
Set PC = ActiveWorkbook.PivotCaches(1)
Set PC.Recordset = rst
Set PT = ActiveSheet.PivotTables("Tabla dinámica1")
PT.PivotCache.Refresh

a esto:
Set PT = ActiveSheet.PivotTables("Tabla dinámica1")
Set PC = PT.PivotCache
Set PC.Recordset = rst
PT.PivotCache.Refresh

y le agrego la destuccion de los objetos de la TD
Set PT = Nothing
Set PC = Nothing

Sub Prueba()
Dim cn As ADODB.Connection, rst As ADODB.Recordset, PC As PivotCache, PT As PivotTable
Set cn = New ADODB.Connection
With cn
.Provider = "microsoft.jet.oledb.4.0"
.ConnectionString = "data source=" & ThisWorkbook.FullName & ";extended properties=excel 8.0"
.Open
End With
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With
rst.Open "select sum(cantidad) as cantidad, HH, Fecha, Media, Dia from [ventaspollohora$a1:f15000]" & _
"group by HH, Fecha, Media, Dia Order by Fecha", cn, , , adCmdText
Set PT = ActiveSheet.PivotTables("Tabla dinámica1")
Set PC = PT.PivotCache
Set PC.Recordset = rst
PT.PivotCache.Refresh
rst.Close
Set rst = Nothing
cn.Close
Set cn = Nothing
Set PT = Nothing
Set PC = Nothing
End Sub