I use the following code in many programs. It should be fairly easy to customize for your use. If you want to do multiple files, just repeat the code, changing the chart identifier CH01 in the following (and of course, creating a unique file name for each subsequent file)
ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard
SUB SmartExport
' This module allows the user to export to a specified file name and path from a single button.
' It saves a lot of time compared to hitting the XL export button, and having to save the resulting
' file to a new path and filename.
'
' The macro builds the save file name by getting the month/day figures from the parameters entered by the user.
' It uses the standard prefix 'VGConsBill', followed by start and end dates
'
'
' Build the date string:
set m1 = ActiveDocument.Variables("vMonthStart")
ms = m1.GetContent.String
set d1 = ActiveDocument.Variables("vDayStart")
ds = d1.GetContent.String
set m2 = ActiveDocument.Variables("vMonthEnd")
' Have to use "mne" instead of "me" because "me" is a keyword
mne = m2.GetContent.String
set d2 = ActiveDocument.Variables("vDayEnd")
de = d2.GetContent.String
' Now, concatenate all of these to make datestr
datestr = ms & "-" & ds & "-" & mne & "-" & de
' Standard name for the report is "VGConsBill-ms-ds-mne-de"
v = "VGConsBill"
' Create the appropriate filename
FileName = v & datestr & ".xlsx"
' User has specified the path. Alternately, in the future,
' Path could picked up from an input box on the sheet where user enters desired path
Path = "S:\Billing Reports\VG\"
' Prep the Excel application
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False
set XLDoc = XLApp.Workbooks.Add
' CH01 is the table containing the VG billing data
' CH01 is never more than a one sheet file
ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard TRUE
XLDoc.Sheets(1).Paste()
' Export the file
XLDoc.Sheets(1).Name = "Export"
XLDoc.SaveAs Path & FileName
XLDoc.Close
end sub
