sending results to Excel - possibly a common problem?!? help needed!!!

[#1]

Hi guys,

I have a problem when extracting a tablebox from Qlikview to excel. The table box has numerous tables, and some have a large amount of text. However, when i extract to excel, the number of characters is limited to 255 characters. I know this is a limit from excel but can you think of a workaround for this? I can't really think of one at present but would love some input on this as the person requesting this wants it as quickly as possible!

Thanks in advance 🙂

bc

[Anonymous · Accepted solution]

This is not Excel limit, it's a limit of the "Send to Excel" QV command. If you copy table to clipboard and paste to excel, it will work. I'm doing it with a macro button. Another macro that could be used is ExportHTML..

[disqr_rm]

It is an Excel limitation.

http://support.microsoft.com/kb/213841

All I can think for now is dealing this with a macto where you split the long field to multiple fields and then send to excel from macro after manipulations.

So, you would put a button somewhere. When it is clicked, a Macro is called which will get the data from your table, split the long fields to smalled fields of 255 characters, and then send the new table to Excel.

Good luck. Please don't forget to post your final solution here.

[#4]

Excellent! Saved me some annoying changes to my script!

Thanks 🙂

[Anonymous]

How come it is ecxle limitation when it works for me? I just cheked again - there are cells with thousands characters.
Here is the exact macro (it contains also some formatting, not relevant for the question):

sub CopyToExcel
 ActiveDocument.GetSheetObject("CH21").CopyTableToClipboard true
 set XLApp = CreateObject("Excel.Application")
 XLApp.Visible = True
 set XLDoc = XLApp.Workbooks.Add
 set XLSheet = XLDoc.Worksheets(1)
 XLSheet.Paste XLSheet.Range("A1")
 set Selection = XLSheet.Cells
 With Selection
 .VerticalAlignment = 1
 .WrapText = True
 .Borders.ColorIndex = 0
 End With
end sub

[disqr_rm]

You are correct Michael. This is not a limitation when you are exporting or saving as Excel Workbook, but only when you are saving as Microsoft Excel 97-2000 & 5.0/95 Workbook. When you right click on a table and choose "Send to Excel" I think QlikView internally is exporting as Microsoft Excel 97-2000 & 5.0/95 Workbook.

Try right clicking on the table in enclosed and choose "Send to Excel".

This site also talks about it: http://www.dnzone.com/go?234

[Anonymous]

Yes, I'm mostly using Excel 2002 and sometimes 2007. Right-clik and "Send to Excel" cuts to 255, while copy/paste returns the full data.
I guess I was lucky and never had to deal with the long strings before using 2002.

[#8]

Hi Michael

Sorry to barge in on this thread so late but I've just come across this one.

I'm interested to know where I would find details on what other property values for Excel that I can set using these macros, are you just looking at an MSN website to find things such as ExcelDoc.Sheets(1).Rows("2:2").Font.Bold = True

It is possible that I have a client that may want to use this type of technology to build a very long, drawn out macro that would copy/paste and then fully format an entire excel workbook.

Thanks for any help.

[Anonymous]

Hi Nigel,
(sorry for delay)

It is certainly possible to format excel from QV macros. As for the specifics, I can't tell if and where you can find all you need. Usually I have to search and try. Recently I was looking how to set landscape format in Excel, tried "normal"
ActiveSheet.PageSetup.Orientation=xLandscape
which didn't work, asked here, and got this:
ActiveSheet.PageSetup.Orientation = 2
Other examples I've used:

XlSheet.Range("A2:A2").Select
XLSheet.Paste
XlSheet.Columns("A:A").ColumnWIdth=30

set Selection = XLSheet.Cells
With Selection
.VerticalAlignment = 1
.WrapText = True
.Borders.ColorIndex = 0 'black border
End With

XlSheet.Range("A1:M1").Interior.ColorIndex = 20 ' background color, try different numbres

XlSheet.Name="sheet name here"

I'm sure there is more examples here on forum, but hard to find...

[#10]

Hi guys,

Is it possible to search and replace a string within an exported excel worksheet via a macro ?

Thanks,

Puneet.