Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I found and have been able to implement a macro from this thread which lets me export multiple tables and save them into dynamically named workbooks. It works great, except that I want to modify it so that it can save the data to preexisting workbooks instead of creating new ones so that I can run macros in them and spit out the data in a report.
Here's the code as I currently have it set.
sub Export
set f = activedocument.Fields("Brand")
if IsEmpty(f.GetSelectedValues) then
set pv = f.GetSelectedValues 'adds selected values if any
else
set pv = f.GetPossibleValues(1000) 'adds possible values if none selected
end if
set Array1 = f.GetNoValues 'empty array
for i = 0 to pv.count-1 'adds values from the first array
Array1.Add
Array1(i).Text = pv.item(i).Text
Array1(i).IsNumeric = false 'if text in your field
next
'Creates a path and a filename of the output xls-file in the same folder as the current document
'Path = ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '\', -1))")
Path = "C:\Users\michael.moore\Desktop\Other\" 'your static path if you dont want a dynamic path
set obj = ActiveDocument.GetSheetObject("V1P") 'the ID of the object you want to export
'Loop through each selection in your field
for i = 0 to Array1.count-1
f.Select Array1(i).Text 'Selects one value in your field at a time
'Sets the FileName to be the same as the selection in your chosen field
FileName = Array1(i).Text & ".xlsx"
' Starts Excel
set XLApp = CreateObject("Excel.Application")
' Makes it run in background
XLApp.Visible = False
set XLDoc = XLApp.Workbooks.Add
' Set the cell to start in at A1
Set rngStart = XLDoc.Sheets(1).Range("A1")
' Copies the chosen object contents to clipboard
obj.CopyTableToClipboard true
' Paste it into excel
XLDoc.Sheets(1).Paste()
' Copy 2nd, and any repeating tables to excel workbook
set obj = ActiveDocument.GetSheetObject("V3P1") 'the ID of the object you want to export
' Set the cell to start in at A1
Set rngStart = XLDoc.Sheets(2).Range("A1")
' Copies the chosen object contents to clipboard
obj.CopyTableToClipboard true
' Paste it into excel
XLDoc.Sheets(2).Paste()
' Save the excel-file with the dynamic path and filename
XLDoc.SaveAs Path & FileName
' Exits the current running Excel
XLApp.Quit
set XLApp = nothing
next 'goes to the next value in the selection in the chosen field
end sub
Any help would be greatly appreciated.
Thanks!