Hola:
Si tienes la conexión ya creada, cuando creas una TD escoge la opción "Fuente de Datos Externa", y selecciona la conexión. Esto te 'pasará' directamente los datos de SqlServer a la TD.
Una vez creada, puedes hacer que los datos se guarden o no en el fichero Excel, eso lo puedes cambiar en "Opciones de tabla dinámica", pestaña "Datos", opción "Guardar datos de origen con el archivo".
Si todo esto lo grabas con la grabadora de macros, tendrás el código VBA para hacerlo.
Espero que te sirva
Un saludo