Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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.
gerhardl
Contributor II

macro to copy and paste long account # in excel

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.

Tags (1)
1 Solution

Accepted Solutions
mwoolf
Honored Contributor II

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

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

16 Replies
mwoolf
Honored Contributor II

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

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

gerhardl
Contributor II

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

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)"

mwoolf
Honored Contributor II

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

Try:

 

ActiveDocument.GetSheetObject("LB32").CopyTextToClipboard

gerhardl
Contributor II

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

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

mwoolf
Honored Contributor II

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

That code worked for me.

Try the attached qvw

gerhardl
Contributor II

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

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.

mwoolf
Honored Contributor II

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

 

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

mwoolf
Honored Contributor II

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

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

gerhardl
Contributor II

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

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.


Community Browser