Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

Tags (2)
2 Replies

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

Perhaps this does what you want.


talk is cheap, supply exceeds demand
AlexOmetis
Contributor II

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

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...

Community Browser