Small change in Macro needed..Export tables in a Excel

[pra_kale]

Hi All,

Below attached is a macro which exports tables in a Excel sheets. But it exports only 3 tables in a 3 sheets. I tried to modify the macro to export more than 4 tables in a 4 sheets but it is not working. I think the macro only copy paste the tables in a by default available sheets i.e. 3 but it fails to create any new sheet.

I want a macro which will create sheets according to the tables which I will mention in a macro.

Can any one have to solution to this problem.

Thanks in advance.

[robert_mika]

Could you post the macro code?(I have PE at home)

[#3]

Hi Prasad,

Use the below code for adding new sheet

XLDoc.Sheets.Add.Name= "<Your sheetname>"

[pra_kale]

Hi,

Below given is the code which is working only for copy pasting 3 tables in a excel..unable to add 4th table and On..as i have mentioned I want to add almost 20 to 25 tables in a excel...

sub ExcelFile

set xlApp = createobject("Excel.Application")

XLApp.visible = true

set XLDoc = XLApp.workbooks.add

ActiveDocument.getsheetobject("CH01").copyTableToclipboard true

XLDoc.sheets(1).paste()

XLDoc.sheets(1).rows("1:3000").Entirerow.AutoFit

ActiveDocument.getsheetobject("CH02").copyTableToclipboard true

XLDoc.sheets(2).paste()

XLDoc.sheets(2).rows("1:3000").Entirerow.AutoFit

ActiveDocument.getsheetobject("CH03").copyTableToclipboard true

XLDoc.sheets(3).paste()

XLDoc.sheets(3).rows("1:3000").Entirerow.AutoFit

XLDoc.sheets(1).Name = "AddressProof"

XLDoc.sheets(2).Name = "Billchannel"

XLDoc.sheets(3).Name = "Sub_channel"

XLDoc.sheets(1).Range("A1").select

XLDoc.sheets(2).Range("A1").select

XLDoc.sheets(3).Range("A1").select

end sub

Thanks in advance.

Prasad

[pra_kale]

Hi sandeep,

Thanks for your reply..below given is the code which I am using, which only copy paste 3 tables in a excel file..can you help where I should add the code which you have given me..

sub ExcelFile

set xlApp = createobject("Excel.Application")

XLApp.visible = true

set XLDoc = XLApp.workbooks.add

ActiveDocument.getsheetobject("CH01").copyTableToclipboard true

XLDoc.sheets(1).paste()

XLDoc.sheets(1).rows("1:3000").Entirerow.AutoFit

ActiveDocument.getsheetobject("CH02").copyTableToclipboard true

XLDoc.sheets(2).paste()

XLDoc.sheets(2).rows("1:3000").Entirerow.AutoFit

ActiveDocument.getsheetobject("CH03").copyTableToclipboard true

XLDoc.sheets(3).paste()

XLDoc.sheets(3).rows("1:3000").Entirerow.AutoFit

XLDoc.sheets(1).Name = "AddressProof"

XLDoc.sheets(2).Name = "Billchannel"

XLDoc.sheets(3).Name = "Sub_channel"

XLDoc.sheets(1).Range("A1").select

XLDoc.sheets(2).Range("A1").select

XLDoc.sheets(3).Range("A1").select

end sub

[#6]

Hi Prasad,

Here you go, add it anywhere after creation of the excel workbook and then paste the contents you require to the sheet

sub ExcelFile

set xlApp = createobject("Excel.Application")

XLApp.visible = true

set XLDoc = XLApp.workbooks.add

XLDoc.Sheets.Add.Name= "New sheetname"  '<---new code here

ActiveDocument.getsheetobject("chart object number").copyTableToclipboard true '<---new code here

XLDoc.New sheetname.paste() '<---new code here

ActiveDocument.getsheetobject("CH01").copyTableToclipboard true

XLDoc.sheets(1).paste()

XLDoc.sheets(1).rows("1:3000").Entirerow.AutoFit

ActiveDocument.getsheetobject("CH02").copyTableToclipboard true

XLDoc.sheets(2).paste()

XLDoc.sheets(2).rows("1:3000").Entirerow.AutoFit

ActiveDocument.getsheetobject("CH03").copyTableToclipboard true

XLDoc.sheets(3).paste()

XLDoc.sheets(3).rows("1:3000").Entirerow.AutoFit

XLDoc.sheets(1).Name = "AddressProof"

XLDoc.sheets(2).Name = "Billchannel"

XLDoc.sheets(3).Name = "Sub_channel"

XLDoc.sheets(1).Range("A1").select

XLDoc.sheets(2).Range("A1").select

XLDoc.sheets(3).Range("A1").select

end sub

[pra_kale]

Thanks Sandeep for your help...

The given code is very helpful now given code is creating a file with the name what I have mentioned..but now problem is it is not copy pasting the table..only blank sheet get's created. Also my next codes are also not get's executed.  Below given is the new cod after addition..whether any thing is missing in the given code..pl help.

sub ExcelFile

set xlApp = createobject("Excel.Application")

XLApp.visible = true

set XLDoc = XLApp.workbooks.add

XLDoc.Sheets.Add.Name= "Channel" 'New code

ActiveDocument.getsheetobject("CH04").copyTableToclipboard true ' New Code

XLDoc.Channel.paste() ' New Code

XLDoc.Channel.rows("1:3000").Entirerow.AutoFit 'New code

ActiveDocument.getsheetobject("CH01").copyTableToclipboard true

XLDoc.sheets(1).paste()

XLDoc.sheets(1).rows("1:3000").Entirerow.AutoFit

ActiveDocument.getsheetobject("CH02").copyTableToclipboard true

XLDoc.sheets(2).paste()

XLDoc.sheets(2).rows("1:3000").Entirerow.AutoFit

ActiveDocument.getsheetobject("CH03").copyTableToclipboard true

XLDoc.sheets(3).paste()

XLDoc.sheets(3).rows("1:3000").Entirerow.AutoFit

XLDoc.sheets(1).Name = "AddressProof"

XLDoc.sheets(2).Name = "Billchannel"

XLDoc.sheets(3).Name = "Sub_channel"

XLDoc.Channel.Range("A1").select

XLDoc.sheets(1).Range("A1").select

XLDoc.sheets(2).Range("A1").select

XLDoc.sheets(3).Range("A1").select

end sub

[#8]

Unfortunately, excel on my system is not working as expected(even my old macro code,doesn't seem to work on my machine). The previous attachment is not working on my machine at the moment.

The code does look ok for me.

One tip I can suggest is the make sure the chart numbers are right(the qvw you had attached did not contain these object ids). The macros may work in another machine(try on your colleague's if possible).

Try this order

ActiveDocument.getsheetobject("CH04").copyTableToclipboard true

XLApp.Worksheets("Channel").Range("A1").Select()

XLApp.Worksheets("Channel").Paste()

XLApp.Worksheets("Channel").Cells.EntireRow.AutoFit

instead of this

XLDoc.Channel.paste() ' New Code

XLDoc.Channel.rows("1:3000").Entirerow.AutoFit 'New code

XLDoc.Channel.paste()

XLDoc.Channel.Range("A1").select

May be other community members here can guide you further.

[pra_kale]

Thanks a Ton..!!!!

Great Help...It's working absolutely fine.

Just one more thing..as I am very new to qlickview can you suggest any e-book for reference which contains Functions used while developing applications..most commonly used also fine.. also how to use set analysis, aggregate()...

Once again Thanks for your Help....