Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Please see my code below for a macro. The macro runs but it only copies and pastes the data into one sheet to excel. The macro does create 5 sheets but doesn't paste data or change the name of any of them except the first one (like it doesn't loop through, but as you'll see I'm referring to the Sheets(SheetCounter) to tell it to move on).
The final output should have 5 tabs in the excel document with the name of the "Hold_Resp." Each of their tabs should have their data filtered by their name. Please let me know why this isn't working. Thanks!
sub SmartExport
Path = "C:\..."
FileName = "Extract" & ActiveDocument.Variables("vToday").GetContent.String & ".xlsx"
ActiveDocument.Fields("Hold_Resp").Clear
set val=ActiveDocument.Fields("Hold_Resp").GetPossibleValues
set XLApp = CreateObject("Excel.Application")
XLApp.DisplayAlerts = False
XLApp.Visible = False
set XLDoc = XLApp.Workbooks.Add
SheetCounter = 1
'for i=0 to val.Count-1
for i=0 to 5
ActiveDocument.Fields("Hold_Resp").Select val.Item(i).Text
ActiveDocument.GetSheetObject("RawData").CopyTableToClipboard true
XLDoc.Sheets.Add
XLDoc.Sheets(SheetCounter).Select
XLDoc.Sheets(SheetCounter).Paste()
XLDoc.Sheets(SheetCounter).Name = Replace(val.Item(i).Text,"/","-")
SheetCounter = SheetCounter + 1
next
XLDoc.SaveAs Path & FileName
XLDoc.Close
XLDoc.DisplayAlerts = true
end sub
Perhaps this does what you want.
I know it's not a macro, but the NPrinting application does this - looping through values in a field and creating an Excel worksheet for each one, containing the data associated with that value of that field. It does lots of other things too of course!
Might be worth a look if you're looking for something robust and more usable & extendable than a macro...