Usar una plantilla de Excel para insertar datos de Access

Para conseguir lo que quieres te voy a proponer un sistema, dando por sentado que:
1.- Tu archivo libro.xlsx será la plantilla, y nunca se modificará.
2.- Vas a necesitar utilizar el ShellExecute para abrir la plantilla antes de modificarla.

3.- Tu carpeta plantilla se halla en el mismo directorio donde está la BD
4.- El procedimiento va a ser:
a) El código te abre la plantilla
b) Crea una instancia a través de automatización
c) Rellena los datos
d) Te guarda el Excel con otro nombre
En el módulo de formulario debes introducir, al principio (debajo de la línea "Option Compare Database" el código del ShellExecute, que es:
...
Private Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
...
Y en el evento del control que te inicie el proceso le generas el siguiente código:
...
Private Sub...
'Declaramos las variables
Dim vNom As String, vApell As String
Dim rutaPlantilla As String
Dim nuevoExcel As String
Dim miExcel As Object
Dim miHoja As Object
'Cogemos los datos del formulario
vNom = Nz(Me.Nombre.Value, "")
vApell = Nz(Me.Apellidos.Value, "")
'Asignamos la ruta hasta la carpeta para el nuevo Excel
nuevoExcel = Application.CurrentProject.Path & "\Plantilla\"
'Cogemos la ruta de la plantilla
rutaPlantilla = Application.CurrentProject.Path & "\Plantilla\libro.xlsx"
'Abrimos la plantilla de Excel
Call ShellExecute(Me.hwnd, "Open", rutaPlantilla, "", "", 1)
'Capturamos la instancia de Excel para poder operar desde Access
Set miExcel = GetObject(rutaPlantilla)
'Cogemos la "Hoja1" de la plantilla
Set miHoja = miExcel.worksheets("Hoja1")
'Operamos sobre la hoja
With miHoja
.Range("A1").Value = "Nombre"
.Range("B1").Value = "Apellido"
.Range("A1:B1").Font.Bold = True
.Range("A2").Value = vNom
.Range("B2").Value = vApell
End With
'Guardamos el Excel con otro nombre
miExcel.SaveAs nuevoExcel & vApell & ".xlsx"
'Cerramos el Excel
miExcel.Close
End Sub
...
Un par de comentarios:
- Si utilizas esa plantilla las líneas:
.Range("A1").Value = "Last Name"
.Range("B1").Value = "First Name"
.Range("A1:B1").Font.Bold = True
No harían falta, ya que las podrías escribir y formatear directamente en la propia plantilla.
- El nuevo Excel se guarda en la misma ruta que la plantilla y con vApell como nombre de archivo (a través de la línea <<miExcel. SaveAs nuevoExcel & vApell & ".xlsx">>. Evidentemente puedes modificar el valor de la variable nuevoExcel para que se guarde en otra ruta.
- Doy por sentado que sabes que en el VBE debes añadir la referencia a "Microsoft Excel x, y object library"
Y con eso y un bizcocho... hasta mañana a las ocho :)
Espero que te sea de utilidad.