topic Re: macro to copy and paste long account # in excel in QlikView

macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 13:23:12 GMT

Hi,

I use a macro to create a report. Part of it is to copy possible values from a list box ("Account no") and paste it in Excel.

The Account Numbers can be between 16 and 20 characters long.

After running the macro it changes a number like 10201128030000086054 to this: 10201128030000000000.

I have tried formatting after the paste, like this:

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"

And I have tried the text() function when loading the field in QV - but no luck.

Any ideas how I can get the full Account Number in excel, and not limit it to 15 characters + zeroes? It works when I click on the XL (export listbox) button, but due to the nature of my macro I have to copy and paste.

Re: macro to copy and paste long account # in excel

m_woolf — Tue, 18 Sep 2012 13:41:13 GMT

Change the cell formating in Excel to Text before pasting in the value.

Re: macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 13:43:52 GMT

That is what I am trying (even tried vefore AND after):

ActiveDocument.RecallDocBookmark "Dormant5"

ActiveDocument.GetSheetObject("LB32").CopyTableToClipboard true

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"

XLSheet1.Paste XLSheet1.Range("A1")

XLSheet1.Range("B2").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"

Re: macro to copy and paste long account # in excel

m_woolf — Tue, 18 Sep 2012 14:08:40 GMT

Try:

ActiveDocument.GetSheetObject("LB32").CopyTextToClipboard

Re: macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 14:37:43 GMT

No, that doesn't seem to work either - it then doesn't copy the values from the list box - it copies whatever was on my clipboard.

In this case I copied your above command and pasted it in my macro and then ran the macro - so it pasted that command in excel (see image).

Re: macro to copy and paste long account # in excel

m_woolf — Tue, 18 Sep 2012 14:41:57 GMT

That code worked for me.

Try the attached qvw

Re: macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 14:47:51 GMT

Sorry for the trouble but can you maybe post the macro here?

I have QV Personal Edition so can't open your file.

I tried the below but it definitely does not work my side:

ActiveDocument.RecallDocBookmark "Dormant6"

ActiveDocument.GetSheetObject("LB32").CopyTextToClipboard

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"

XLSheet1.Paste XLSheet1.Range("A1")

XLSheet1.Range("C2").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"

Not sure if the fact that I use an expression in my list-box (and not a normal field) has anything to do with it?

That is what you helped me with earlier in THIS discussion.

Re: macro to copy and paste long account # in excel

m_woolf — Tue, 18 Sep 2012 14:49:44 GMT

Set XLApp = CreateObject("Excel.Application")
XLApp.visible = true
Set XLWorkbook = XLApp.Workbooks.Add
Set XLSheet = XLWorkbook.sheets("Sheet1")
XLSheet.Range("A1:A1000").NumberFormat = "@"
ActiveDocument.GetSheetObject("LB01").CopyTextToClipboard
XLSheet.Range("A1").Select
XLSheet.paste

Re: macro to copy and paste long account # in excel

m_woolf — Tue, 18 Sep 2012 14:52:54 GMT

I tried it with the aggr expression in my LB and it still worked.

Re: macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 14:57:35 GMT

Yes, the issue seems to be with the selections that I make first.

I commented out this command now:

ActiveDocument.RecallDocBookmark "Dormant6"

And then it works perfectly.

I need to make those selections first though.

Re: macro to copy and paste long account # in excel

m_woolf — Tue, 18 Sep 2012 15:07:57 GMT

Cal you make those selections in code and not i a bookmark?

Re: macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 15:10:54 GMT

I would REALLY prefer not to. The whole point of this macro is to create an excel list of randomly select accounts, each based on multiple selections.

I am going to have a few hundred different bookmarks at the end, which makes coding this easy because I can copy and paste and just change a few things. Coding in the individual selections will take me all year - plus they may change at a later stage, in which case it would be better to just update the bookmarks...

Re: macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 15:25:11 GMT

In any case it looks like that won't work - I just tried, making just one selection, and it has the same problem.

Only when nothing is selected does the .CopyTextToClipboard command seem to work....

Re: macro to copy and paste long account # in excel

m_woolf — Tue, 18 Sep 2012 16:00:57 GMT

It will be messier, but this code should get each individual value from the listbox. You coe would need to write boxvalues(i) into a cell and then move down to the next cell.

set LB = ActiveDocument.GetSheetObject("LB01")

boxvalues=LB.GetPossibleValues

for i = lbound(boxvalues) to ubound(boxvalues)

msgbox(boxvalues(i))

Re: macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 16:06:16 GMT

I saw something like that in a different post and API guide, but could not figure it out.

Will you be able to integrate that to the section of my macro I posted? It makes a bookmark selection then pastes all possible values from the list box in cell A1.

I don;t understand what the "box" commands do exactly and how to use them. If is selects "PossibleValues" do I still need the LB with aggr expression?

ActiveDocument.RecallDocBookmark "Dormant6"

ActiveDocument.GetSheetObject("LB32").CopyTextToClipboard

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"

XLSheet1.Paste XLSheet1.Range("A1")

Thank you so much for all the trouble you're going through.

Re: macro to copy and paste long account # in excel

m_woolf — Tue, 18 Sep 2012 16:38:17 GMT

Activedocument.RecallDocBookmark "Dormant6"
XLSheet1.Range("A1:A500000").NumberFormat = "@"
set LB = ActiveDocument.GetSheetObject("LB01")

boxvalues=LB.GetPossibleValues

for i = lbound(boxvalues) to ubound(boxvalues)
XLSheet.cells(i+1,1).Value = boxvalues(i)
next

Re: macro to copy and paste long account # in excel

gerhardl — Tue, 18 Sep 2012 17:30:08 GMT

Beautiful.

Thank you