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]

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

[gerhardl]

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

[m_woolf]

Try:

ActiveDocument.GetSheetObject("LB32").CopyTextToClipboard

[gerhardl]

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

[m_woolf]

That code worked for me.

Try the attached qvw

[gerhardl]

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.

[m_woolf]

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

[m_woolf]

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

[gerhardl]

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.