Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Change the cell formating in Excel to Text before pasting in the value.
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)"
Try:
ActiveDocument.GetSheetObject("LB32").CopyTextToClipboard
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).
That code worked for me.
Try the attached qvw
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.
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
I tried it with the aggr expression in my LB and it still worked.
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.