2 Replies Latest reply: May 30, 2013 12:12 PM by Alex Walker RSS

    Macro to Select item in field, paste in worksheet in excel, loop through

    Travis Russo

      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