macro to copy and paste long account # in excel

[gerhardl]

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.

[m_woolf]

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

[gerhardl]

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

[gerhardl]

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

[m_woolf]

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

[gerhardl]

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.

[m_woolf · Accepted solution]

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

[gerhardl]

Beautiful.

Thank you