topic Copy Excel sheet (VB Macro) in QlikView

Copy Excel sheet (VB Macro)

Tue, 04 Aug 2009 19:22:02 GMT

Hi everybody,

I got a VB Macro in my QV-Application (8.5) which prepares data for a report. I need to copy, for example, Sheet "Sheet1" and rename it to "Revenue".

The Excel Macro Recorder gives me this operation:

Sheets("Sheet1").Copy After:=Sheets(2)

Normally I know hot wo convert these operations into a language QV understands it, but in this case I dont.

ExcelDoc.Sheets("Sheet1").Copy After:=Sheets(2)

- Could anynone tell me how to replace the "After:=Sheets(2) "

And in general: Hod do i find out how to convert such operations into the VBS Syntax?

Thanks in advance and KindRegards!

Copy Excel sheet (VB Macro)

Tue, 04 Aug 2009 20:50:26 GMT

I'm assuming that you know that Copy is a function and VB expects it to look like: .Copy(...). If you go into the Macro Editor within Excel and click F2, you will get the Object Browser. The Object Browser gives you all the info you need about the Excel API (similar to QlikView's API Guide).

The Copy function on Sheets takes two parameters, Before and After. Unfortunately, I couldn't figure out how to only pass the After. In order to pass Sheets(2), you need to properly reference it as an Excel object.

This will make that line work, but you may need to adjust the position the sheet is pasted in:

ExcelDoc.Sheets("Sheet1").Copy(ExcelDoc.Sheets(2))

Copy Excel sheet (VB Macro)

Tue, 04 Aug 2009 21:10:36 GMT

Hi NMiller,

thanks for the verification because I found the soultion by myself this minute 😉 But more by try&and error than using the objectbrowser...

Neither did I found out how to place it properly with something similar to before/after, in this case it doesnt matter so the problem is solved for me.

Thanks for the solution and the tip with the objectbrowser!

Kind Regards