Simplificar el cidogo de una macro realizada con el grabador de macros

[Stackado]

Hola,

Bueno hace poco empece a trabajar con macros y parecen ser bastante útiles, entre mis deberes tengo que realizar una forma en Excel para extraer la información desde algunas tablas con la extensión .dbf, lo logre después de leer algunos manuales y utilizando el grabador de macros.

El grabador de macros resulto ser bastante útil pero también resulto ser (para mi al menos) un arma de doble filo, pues como sabrán el grabador de macros registra todo lo que haces y en ocasiones con redundancias y eso se refleja en código de mas, bueno después de probar que funciona me dispuse a analizar el código me tope con varios errores cuando cambian los entornos de los archivos, quisiera saber si pueden auxiliarme un poco en esto ya que aun soy bastante nuevo en este tema.

Verán uno de los errores es que la ruta es relativa, al usar el asistente de BD Query del Excel graba la ruta relativa donde se encuentran las tablas y por lo tanto si pruebo la hoja en otra computadora no se ejecuta, por lo tanto debo usar rutas estáticas donde a pesar de estar en otras maquinas siga encontrando las tablas (osea ubicarme en la misma carpeta), bueno despues de analizar el codigo me tope con varias sorpresas y aunque simplifique algunas cosas en lo de las rutas realmente me trabe, pues al parecer utiliza varios objetos de nombre Array para concatenar cadenas muy largas.

Quisiera saber como puedo ajustar el código para que quede siempre ubicando a la carpeta donde me ubico el archivo Excel junto con las tablas, podrían darme una mano, el código es el siguiente:

Código vb:

Sub Macro_exportación()
'Macro_exportación Macro para extraer
'información de tabla clientes
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;CollatingSequence=ASCII;DBQ=C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEMA" _
        ), Array( _
        " DE EMBARQUES SAE;DefaultDir=C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEM" _
        ), Array( _
        "A DE EMBARQUES SAE;Deleted=0;Driver={Driver do Microsoft dBase (*.dbf)};DriverId=277;FIL=dBase IV;MaxBufferSize=2048;MaxScanRow" _
        ), Array( _
        "s=8;PageTimeout=5;SafeTransactions=0;Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
        )), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT clientes.NUM, clientes.NOMB, clientes.CALLE, clientes.RFC" & Chr(13) & "" & Chr(10) & "FROM clientes clientes" & Chr(13) & "" & Chr(10) & "ORDER BY clientes.NUM, clientes.NOMB, clientes.RFC" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabla_Consulta_desde_cmbjpc1"
        .Refresh BackgroundQuery:=False
    End With
End Sub
 

En la parte donde comienzan los arrays es donde esta todo lo de las rutas hacia las tablas.

Saludos y gracias por su tiempo.

[mrocf]

Entiendo que tu problema está en esta parte del código:

Código PHP:

  With ActiveSheet.ListObjects.Add(SourceType:=0, _
Source:=Array(Array("ODBC;CollatingSequence=ASCII;" & "DBQ=" & _
"C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEMA" _
), Array(" DE EMBARQUES SAE;DefaultDir=" & _
"C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEM" _
), Array("A DE EMBARQUES SAE;Deleted=0;Driver={Driver do Microsoft dBase (*.dbf)};DriverId=277;" & _
"FIL=dBase IV;MaxBufferSize=2048;MaxScanRow"), Array("s=8;PageTimeout=5;SafeTransactions=0;" & _
"Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;")), Destination:=Range("$A$1")).QueryTable 
   

1º) Nota que aprovechando que se trata de una línea de código muy larga, la he subdividido en varios tramos.
¿Primera comprobación a realizar?... Verifica que de este modo el código sigue funcionando adecuadamente.

2º) El problema de lo que llamas "rutas relativas / estáticas" estaría dado por estas dos expresiones:

C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEMA
y
C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEM

Difieren en una letra, por lo que no sé decirte si es lo correcto o así está bien.

3º) En todo caso, ¿cómo lo solucionas?... Pues mira lo siguiente que es equivalente a lo que tienes:

Código PHP:

  Dim Ruta1 As String, Ruta2 As String
Ruta1 = "C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEMA"
Ruta2 = "C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEM"
 
With ActiveSheet.ListObjects.Add(SourceType:=0, _
Source:=Array(Array("ODBC;CollatingSequence=ASCII;" & "DBQ=" & Ruta1 _
), Array(" DE EMBARQUES SAE;DefaultDir=" & Ruta2 _
), Array("A DE EMBARQUES SAE;Deleted=0;Driver={Driver do Microsoft dBase (*.dbf)};DriverId=277;" & _
"FIL=dBase IV;MaxBufferSize=2048;MaxScanRow"), Array("s=8;PageTimeout=5;SafeTransactions=0;" & _
"Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;")), Destination:=Range("$A$1")).QueryTable 
   

Como ya debes haber advertido, el problema se circunscribe -ahora- a definir correctamente "Ruta1" y "Ruta2": ¡pero eso es lo más sencillo!.

¿Te sirve la idea?
Saludos, Cacho.

[Stackado]

Hola,

Gracias por tu respuesta, había estado fuera de la ciudad y no tenia Internet por eso no podía responder el post.

Como comentas mi problema esta en la parte de código que citas donde comienzan los Arrays ( que parecen estar anidados ), al parecer

Código vb:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;CollatingSequence=ASCII;DBQ=C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEMA" _
        ), Array( _
        " DE EMBARQUES SAE;DefaultDir=C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEM" _
        ), Array( _
        "A DE EMBARQUES SAE;Deleted=0;Driver={Driver do Microsoft dBase (*.dbf)};DriverId=277;FIL=dBase IV;MaxBufferSize=2048;MaxScanRow" _
        ), Array( _
        "s=8;PageTimeout=5;SafeTransactions=0;Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
        )), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT clientes.NUM, clientes.NOMB, clientes.CALLE, clientes.RFC" & Chr(13) & "" & Chr(10) & "FROM clientes clientes" & Chr(13) & "" & Chr(10) & "ORDER BY clientes.NUM, clientes.NOMB, clientes.RFC" _
        )
 

Parece ser que la sintaxis de un Array es "Array(_ _)" utilizando el caracter & para concatenar, intente eliminar algunos pero solo me tiraba error y error y no ejecutaba, veras la ruta completa donde se encuentran las tablas serian

C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEMA DE EMBARQUES SAE

C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEMA DE EMBARQUES SAE

Francamente no tengo idea del por que la ponga 2 veces, primero con el asistente de Query del Excel creo el origen de los datos y después selecciono los campos pero según veo al empezar a poner la ruta el grabador la separa y por eso no aparece completa, ahí es donde empieza todo el embrollo de los Arrays puesto que me empiezo a confundir.

Noto que creas las variables rutas como globales para toda la hoja, como voy a utilizar mas tablas lo ideal seria simplemente cambiar el nombre de la tabla sin tener tanta escala entre carpetas, osea en vez de tener:

C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\RV SISTEMA DE EMBARQUES SAE

Tener algo como:

Tablas/nombre_de_la_tabla.dbf

O podria ser tambien

/nombre_de_la_tabla.dbf

Eso seria lo ideal ya que como destino final sera el acceso a las tabla a través de red pero por ahora debe estar en la misma carpeta todo.

[Stackado]

Hola,

Eh tratado lo que me comentas pero la verdad casi no te eh entendido ademas lo que necesito es que la ruta no cambie y que solo tenga que cambiar los nombres de las tablas, verás tratando de simplificar la ruta para hacer referencia solo a la carpeta donde se encuentran las tablas y así en cada hola solo cambiar el nombre de la carpeta que deba trabajar, me topo con que no me permite hacerlo, al momento de ejecutar la macro me lanza:

"' 'EMBARQUES_SAE\clientes.DBF' no es una ruta de acceso válida.
Asegúrese de que la ruta está escrita correctamente y que está
conectado al servidor donde se encuentra el archivo."

Cosa la cual no entiendo ya que el archivo Excel y la carpeta donde estan las tablas estan juntas :(, esquemáticamente hablando, están de la siguiente forma:

Proyectos
|___Tabla clientes.xlsm
|___EMBARQUES_SAE

|__clientes.DBF
|__gondolas.dbf
|__recamiones.dbf
|__regondolas.dbf

donde EMBARQUES_SAE es la carpeta que contiene las tablas,

Código vb:

Sub Macro_exportación()
'Macro_exportación Macro para extraer
'información de tabla clientes
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;CollatingSequence=ASCII;DBQ=EMBARQUES_SAE\clientes.DBF" _
), Array( _
";DefaultDir=EMBARQUES_SAE" _
), Array( _
";Deleted=0;Driver={Driver do Microsoft dBase (*.dbf)};DriverId=277;FIL=dBase IV;MaxBufferSize=2048;MaxScanRow" _
), Array( _
"s=8;PageTimeout=5;SafeTransactions=0;Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("$A$1")).QueryTable
 

Pero eso no entiendo eso de la conexión a ODBC, quizás tengo un error de sintaxis pero la verdad no logro encontrarlo, el error me lo marca en las rutas precisamente.

[mrocf]

La verdad es que sigo sin entender cual es -exactamente- tu dilema. Sin embargo, espero, poder orientarte si te comento lo siguiente:

El código que la grabadora de macros te entregó te permite ver dos cosas importantes:

-> Los dos parámetros "DBQ=" y "DefaultDir=" te indican la carpeta en la que el MS Query localizará a los *.dbf.

Dices que todas tus tablas se encuentran en la misma posición... Por lo tanto: esa parte del código no la tienes que modificar.
(Mi sugerencia inicial devino de interpretar que estabas buscando tablas en distintas carpetas. Luego: olvida esa sugerencia inicial.)

-> En la siguiente parte del código:

"SELECT clientes.NUM, clientes.NOMB, clientes.CALLE, clientes.RFC" & Chr(13) & Chr(10) & "FROM clientes" & Chr(13) & Chr(10) & "ORDER BY clientes.NUM, clientes.NOMB, clientes.RFC"

es donde especificas la tabla "a la que apuntas". En el ejemplo que has mostrado, se refiere a "clientes.dbf".
_________________

Si tu consulta se corresponde con información de una sola tabla (recuerda que a veces -en consultas más elaboradas- cruzamos datos entre dos o más tablas), entonces el código anterior puede simplificarse del siguiente modo:

"SELECT NUM, NOMB, CALLE, RFC" & Chr(13) & Chr(10) & "FROM clientes" & Chr(13) & Chr(10) & "ORDER BY NUM, NOMB, RFC"

Puedes ver que hacemos una única mención a la tabla correspondiente.
El paso que sigue es introducir la "variabilidad" del siguiente modo:

Dim myTable As String
myTable = "clientes"

"SELECT NUM, NOMB, CALLE, RFC" & Chr(13) & Chr(10) & "FROM " & myTable & Chr(13) & Chr(10) & "ORDER BY NUM, NOMB, RFC"

Variando -entonces- el valor de la variable "myTable" es como parametrizas el acceso a la tabla que prefieras.

Saludos, Cacho.

[Stackado]

Hola,

Gracias por tu respuesta amigo, de verdad agradezco mucho tu ayuda y paciencia

Veras te explico, mi problema no es la tabla con la que trabajo, la tabla funciona perfectamente, la tabla al parecer la asigna la macro en el momento de la consulta.

Código vb:

"SELECT clientes.NUM, clientes.NOMB, clientes.CALLE, clientes.RFC FROM clientes ORDER BY clientes.NUM, clientes.NOMB, clientes.RFC"
 

Nota: Quite los caracteres Chr(13) & Chr(10) y Chr(13) & Chr(10) entre la consulta ya que no son necesarios, esto para purgar el código generado por el grabador de macros y hacerlo mas sencillo .

Ahora continuo pues veras, estas macros que realizo después van a ubicarse en otra maquina (), lo que significa que las rutas donde ubico las tablas osea DBF y Defaultdir van a cambiar y por lo tanto ya no podre trabajar con las tablas ya que las rutas no van a ser las mismas en cada maquina, esto por que no todas las maquinas tienen el mismo nombre de usuario con los mismos nombres de carpeta, el grabador por defecto me pone la Ruta completa iniciando desde C: hasta la carpeta donde están las tablas de mi PC, ahí esta mi problema, si me llevo mi archivo de Excel con la macro a otra PC, esta tendra grabada la ruta de mi computadora y por lo tanto al ejecutarla en otra PC diferente, no va encontrar la ruta y no va a extraer nada, solo me va tirar error, lo que significa que la ruta que uso, osea.

Código vb:

DBQ=C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\EMBARQUES_SAE;"
 

Código vb:

DefaultDir=C:\USERS\ABRAHAM\DOCUMENTS\ESCUELA Y PRACTICAS PROFESIONALES\PROYECTOS PEÑA\INTERFAZ SAE\EMBARQUES_SAE
 

Son rutas relativas al cambiar de PC y lo que necesito es una ruta que a pesar de ser en otra PC siga encontrando las tablas...

La solución que se puede aplicar (o al menos que se me ocurrio ), es como cuando tenemos una web con su index.php y todas sus paginas en la misma carpeta y dentro de esa carpeta junto con las paginas otras carpetas llamadas "Imagenes" o "Scripts" para que así desde el CSS del index solo hagamos referencia a la carpeta y el archivo y no a toda la ruta completa desde C:, mira por decir algo:

Código CSS:

background-image:Imagenes/fondo.gif;
 

En vez de:

Código CSS:

background-image: C:/USERS/ABRAHAM/DOCUMENTS/ESCUELA Y PRACTICAS PROFESIONALES/PROYECTOS PEÑA/INTERFAZ SAE/Imagenes/fondo.gif;
 

.

Con esto se supone que ubique donde ubique el archivo siempre va buscar en la msima ruta.

No se si me di a entender jejeje, la verdad estoy vuelto loco con este maldito problema y nomas no le eh hallado solución, me tira error en al ruta al dejar solo "EMBARQUES_SAE/clientes.DBF" y "EMBARQUES_SAE" y no tengo idea de porque me diga que la ruta sea invalida

[mrocf]

Creo que acabo de entender el problema y, de ser así: ¡la solución es sencilla!... (ja ja ja). Veamos:

a) En tu PC tienes el libro Excel (en el que reside la macro que vemos) ubicado en

c:\users\abraham\documents\escuela y practicas profesionales\proyectos peña\interfaz sae

b) La carpeta anterior contiene una subcarpeta:

c:\users\abraham\documents\escuela y practicas profesionales\proyectos peña\interfaz sae\rv sistema de embarques sae

en la que residen las tablas.

¿Es así la situación?

¿Deseas provocar el mismo "efecto relativo" en otras PC's?
Saludos, Cacho.

[Stackado]

Hola,

El problema quedo resuelto, solo tenia que hacer referencia al libro de trabajo completo y no solo dejar la carpeta.

El código de la macro seria de la siguiente forma.

Código vb:

Sub Macro_exportación()
' Macro_exportación Macro
' Para la tabla clientes.
 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;CollatingSequence=ASCII;DBQ=" & ThisWorkbook.Path & "\EMBARQUES_SAE;" _
        ), Array( _
        "DefaultDir=" & ThisWorkbook.Path & "\EMBARQUES_SAE;Deleted=0" _
        ), Array( _
        ";Driver={Driver do Microsoft dBase (*.dbf)};DriverId=277;FIL=dBase IV;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0" _
        ), Array("Statistics=0;Threads=3;UID=admin;UserCommitSync=Yes;")), _
        Destination:=Range("$A$2")).QueryTable
        .CommandText = "SELECT clientes.NUM, clientes.NOMB, clientes.CP, clientes.RFC FROM clientes clientes ORDER BY clientes.NUM, clientes.NOMB, clientes.RFC"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabla_Consulta_desde_tabla_clientes"
        .Refresh BackgroundQuery:=False
End With
End Sub
 

Donde:

Código vb:

DBQ=" & ThisWorkbook.Path & "\EMBARQUES_SAE;
DefaultDir=" & ThisWorkbook.Path & "\EMBARQUES_SAE
 

Es donde hacemos referencia desde donde esta el archivo principal de Excel..

Quiero agradezer tanto su ayuda como su paciencia a los que me ofrecieron sus respuestas, la verdad programando soy muy malo y nunca había programado en macros así que no entendía tanto las sintaxis como las limitaciones y propiedades de los archivos, sin ustedes no lo hubiera logrado.

Saludos.

[mrocf]

Ésa es la sencilla solución a la que me referí en mi mensaje anterior... (ja ja ja).

Me disculpo contigo por no comprender -de entrada- cual era tu dificultad.
Hasta la próxima, entonces.