Ya le hemos pedido a la inteligencia artificial que nos genere archivos de Excel, fórmulas, dashboards. Pero hasta hoy no le habíamos pedido macros. En este post te cuento cómo le pedí a Claude una macro funcional para acumular archivos de una sola carpeta, y lo más importante: cómo pedirla bien.
¿Claude reemplaza al programador de VBA en Excel? Hice la prueba
🎁 Descarga el archivo de ejemplo
Escribe tu correo electrónico para recibir gratis el archivo para practicar.
*Al enviar mi correo electrónico, acepto recibir noticias y ofertas. Puedo darme de baja en cualquier momento.
Una experiencia diferente a como grabo normalmente
Este ejercicio fue distinto a lo que hago siempre. Cuando grabo un video práctico, ya lo tengo practicado de antemano. Esta vez no. Prácticamente lo hicimos en vivo, pero grabado. Nunca le había pedido esta macro a Claude, así que no sabía qué me iba a devolver. Lo único que tenía claro era qué macro necesitaba.
El escenario: una carpeta con archivos de Excel
Tenía una carpeta con cuatro archivos de Excel. Cada uno con la misma cantidad de columnas, pero valores diferentes. Quería una macro que acumulara todos esos archivos en uno solo.
Sí, ya sé lo que estás pensando: eso ya lo hace Power Query. Correcto. Pero se me hace un buen ejercicio para ver cómo se resuelve haciendo macros.
Aquí algo importante: no basta con decirle “hazme una macro” y ya. Puedes hacerlo, claro, pero si no tienes los fundamentos y el conocimiento del lenguaje VBA, te va a costar más trabajo llegar a lo que necesitas. No es obligatorio dominar macros, pero sí es importante que sepas leer el código y corregir en caso de que algo falle.
La anatomía de un buen prompt
Abrí la ventana de Claude y armé el prompt siguiendo una estructura:
Actúa como. “Actúa como un desarrollador de aplicaciones en Excel con VBA.”
Contexto. “Tengo una carpeta con archivos de Excel tipo xlsx. Todos tienen la misma estructura de columnas pero diferentes filas.”
Solicitud. “Quiero una macro que me ayude a elegir una carpeta y luego los archivos de esa carpeta se concentren en un nuevo archivo de Excel.”
Y algo que siempre hago: le pedí que hiciera las preguntas pertinentes en caso de ser necesario. Con esto le estoy diciendo que si no fui claro, me pregunte.
De hecho, quienes saben de macros seguramente ya notaron que omití varias cosas a propósito: ¿qué pasa si hay otros archivos que no son Excel? ¿Qué pasa si sucede un error? ¿Qué pasa si la ruta de la carpeta es muy larga? Son las mil y un posibilidades que uno como desarrollador debe controlar para que la aplicación no se rompa sin que sepas por qué.
Claude preguntó antes de generar el código
Claude me hizo tres preguntas antes de darme la macro:
- ¿Los archivos tienen encabezados en la fila uno? Le dije que sí.
- ¿El nombre de la hoja a leer es siempre el mismo o puede variar? Le pedí que tomara los datos de la primera hoja, sin importar el nombre.
- ¿Quieres una columna extra que indique de qué archivo vino cada fila? Le dije que sí. Esto me gustó porque se parece mucho a lo que haces en Power Query, donde puedes traer la ruta o el nombre del archivo para identificar el origen de cada fila.
Leyendo la macro antes de probarla
Aquí viene lo importante: no copiar y pegar sin entender. Lo interesante es leer lo que hace y validar que realmente esté bien.
Claude usó Application.FileDialog(msoFileDialogFolderPicker) para abrir el selector de carpetas, el mismo cuadro de diálogo integrado que usa Excel cuando guardas un archivo o cuando en mi complemento EXCELeINFO eliges una carpeta para guardar hojas en archivos separados.
La macro:
- Crea un libro nuevo llamado “Consolidado” (tú decides después dónde y con qué nombre guardarlo).
- Recorre todos los archivos .xlsx de la carpeta con
Dir. - Toma los encabezados del primer archivo encontrado y los usa solo una vez.
- Copia los datos desde la fila dos en adelante de cada archivo, siempre de la misma hoja.
- Agrega una columna extra llamada “archivo de origen”.
Entender el ciclo Do While fue clave. Todo lo que está entre el Do While y el Loop se ejecuta mientras el archivo sea diferente de vacío. En otras palabras: recorre cada archivo y cuando ya no encuentra ninguno, sale del ciclo.
También noté algo que no me convenció del todo: para calcular la última fila y última columna, la macro se va hasta el fondo y sube (o hasta la derecha y regresa a la izquierda). Si hay huecos en los datos, esto te puede dar una fila o columna equivocada. Es justo el tipo de detalle que solo detectas si conoces macros.
Ejecutando la macro
Copié la macro, la pegué en el editor de Visual Basic (Alt+F11), la guardé en mi libro de macros personal (para que quede disponible en cualquier archivo que abra) y la ejecuté apuntando a la carpeta con los cuatro archivos.
Funcionó. En la última columna quedó el nombre de cada archivo de origen, y con un filtro pude confirmar que los datos venían de los cuatro archivos correctos.
¿Y si algo sale mal?
Probé con una carpeta vacía y la macro terminó el proceso, pero con cero archivos consolidados, sin avisarme que no había nada que hacer. Tampoco venía con un controlador de errores.
Le pregunté a Claude por qué no lo incluyó desde el inicio. Su respuesta: primero quería mostrarme la lógica limpia, y luego corregir. Pero reconoció que un proceso que abre múltiples archivos externos sí es un riesgo dejarlo sin manejo de errores: un archivo corrupto, protegido con contraseña, abierto por otra persona en la red, o que la macro se cuelgue a la mitad y deje archivos ocultos en memoria.
Con On Error GoTo ErrorHandler la macro ahora captura esos casos y sigue con el siguiente archivo en lugar de romperse por completo.
La alternativa que Claude también propuso: sin abrir archivo por archivo
Si tuvieras cien archivos, abrir y cerrar uno por uno puede tardar bastante, sobre todo si son archivos grandes. Hace más de diez años hice una macro que se conecta a un archivo de Excel usando consultas SQL (con ODBC o ADO), sin necesidad de abrirlo. Es básicamente lo que hace Power Query por debajo: se conecta, pero no abre cada archivo uno por uno.
Ese es un siguiente paso posible: pedirle a Claude una versión más eficiente basada en consultas SQL en lugar de abrir archivo por archivo.
La macro completa
Sub ConsolidarArchivosDeCarpeta()
Dim rutaCarpeta As String
Dim archivo As String
Dim wbOrigen As Workbook
Dim wbDestino As Workbook
Dim wsOrigen As Worksheet
Dim wsDestino As Worksheet
Dim ultimaFilaOrigen As Long
Dim ultimaFilaDestino As Long
Dim ultimaColOrigen As Long
Dim encabezadosEscritos As Boolean
Dim contadorArchivos As Long
Dim archivosConError As String
contadorArchivos = 0
archivosConError = ""
' ---- 1. Elegir carpeta ----
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Selecciona la carpeta con los archivos Excel"
.AllowMultiSelect = False
If .Show <> -1 Then
MsgBox "No se seleccionó ninguna carpeta. Proceso cancelado.", vbExclamation
Exit Sub
End If
rutaCarpeta = .SelectedItems(1) & "\"
End With
archivo = Dir(rutaCarpeta & "*.xlsx")
If archivo = "" Then
MsgBox "No se encontraron archivos .xlsx en esa carpeta.", vbExclamation
Exit Sub
End If
' ---- 2. Crear el libro destino ----
Set wbDestino = Workbooks.Add
Set wsDestino = wbDestino.Sheets(1)
wsDestino.Name = "Consolidado"
ultimaFilaDestino = 1
encabezadosEscritos = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
On Error GoTo ErrorHandler
' ---- 3. Recorrer archivos .xlsx de la carpeta ----
Do While archivo <> ""
If Left(archivo, 2) <> "~$" Then
Set wbOrigen = Nothing
On Error Resume Next
Set wbOrigen = Workbooks.Open(rutaCarpeta & archivo, ReadOnly:=True)
On Error GoTo ErrorHandler
If wbOrigen Is Nothing Then
archivosConError = archivosConError & archivo & " (no se pudo abrir)" & vbNewLine
Else
Set wsOrigen = wbOrigen.Sheets(1)
ultimaFilaOrigen = wsOrigen.Cells(wsOrigen.Rows.Count, 1).End(xlUp).Row
ultimaColOrigen = wsOrigen.Cells(1, wsOrigen.Columns.Count).End(xlToLeft).Column
If ultimaColOrigen = 0 Or (ultimaColOrigen = 1 And wsOrigen.Cells(1, 1).Value = "") Then
archivosConError = archivosConError & archivo & " (hoja vacía, se omitió)" & vbNewLine
Else
If Not encabezadosEscritos Then
wsOrigen.Range(wsOrigen.Cells(1, 1), wsOrigen.Cells(1, ultimaColOrigen)).Copy _
wsDestino.Cells(1, 1)
wsDestino.Cells(1, ultimaColOrigen + 1).Value = "Archivo origen"
encabezadosEscritos = True
ultimaFilaDestino = 2
End If
If ultimaFilaOrigen >= 2 Then
wsOrigen.Range(wsOrigen.Cells(2, 1), wsOrigen.Cells(ultimaFilaOrigen, ultimaColOrigen)).Copy _
wsDestino.Cells(ultimaFilaDestino, 1)
wsDestino.Range( _
wsDestino.Cells(ultimaFilaDestino, ultimaColOrigen + 1), _
wsDestino.Cells(ultimaFilaDestino + (ultimaFilaOrigen - 2), ultimaColOrigen + 1) _
).Value = archivo
ultimaFilaDestino = ultimaFilaDestino + (ultimaFilaOrigen - 1)
End If
contadorArchivos = contadorArchivos + 1
End If
wbOrigen.Close SaveChanges:=False
Set wbOrigen = Nothing
End If
End If
archivo = Dir
Loop
GoTo Finalizar
ErrorHandler:
archivosConError = archivosConError & archivo & " (error inesperado: " & Err.Description & ")" & vbNewLine
On Error Resume Next
If Not wbOrigen Is Nothing Then wbOrigen.Close SaveChanges:=False
On Error GoTo ErrorHandler
Err.Clear
archivo = Dir
If archivo <> "" Then Resume
Finalizar:
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
If encabezadosEscritos Then
wsDestino.Columns.AutoFit
wsDestino.Rows(1).Font.Bold = True
End If
Dim mensaje As String
mensaje = "Proceso terminado." & vbNewLine & contadorArchivos & " archivo(s) consolidado(s) correctamente."
If archivosConError <> "" Then
mensaje = mensaje & vbNewLine & vbNewLine & "Archivos con problemas:" & vbNewLine & archivosConError
End If
MsgBox mensaje, IIf(archivosConError = "", vbInformation, vbExclamation)
End SubLa conclusión de todo esto
Puedes quedarte con lo que te da la IA y ya. Pero tu experiencia con Excel, con bases de datos y con el lenguaje de programación es lo que te va a permitir guiar a la IA, en lugar de que sea al revés. Claude te da la macro en segundos; entender qué hace, validarla y corregirla es lo que separa a quien solo copia y pega de quien realmente sabe automatizar.
Si quieres aprender a dominar Excel, VBA y las herramientas que hacen tu trabajo más eficiente, te espero en Deztaca.