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.
Cal you make those selections in code and not i a bookmark?
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...
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....
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))
next
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.
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
Beautiful.
Thank you