How can I insert tabs in a number with a macro?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rhon101 - 13 Aug 2005 23:36 GMT

I need to create a macro to paste a list of numbers while inserting tabs
within the numbers. I work in Reflection for IBM (sessions) and Office 2000,
soon to be Office 2003. I need to get a list of ICN's (example 0204123456090)
out of a stats table located on our intranet. Those ICN's, (claim numbers)
need pasted onto a list fetch screen. This screen allows 1 to 100 claim
numbers to be keyed onto the screen so they can be retrieved for processing.
I have already made the macro to extract the numbers from the table on our
intranet and then displays the numbers in a column like; 0204123456000
0204123456060 in a work document.
I can't figure out how to make the macro paste this list of numbers by
inserting tabs in them. The screen that they will be posted to has the
following format;
02 04123 456 000 02 04123 456 060
This is what the screen looks like
00 02 04123 456 000 01 02 04123 456 060 02 02 04123 457 780
it has 00...99 to indicate how many ICN's are pasted. Also, it will not
always be the same amount of numbers that needs to be pasted. I hope I have
explained this clear enough.

Reply to this Message

Doug Robbins - 14 Aug 2005 07:47 GMT

It would be easier to help you is you pasted the code from your macro into
the body of a message that you post back here.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I need to create a macro to paste a list of numbers while inserting tabs
> within the numbers. I work in Reflection for IBM (sessions) and Office
[quoted text clipped - 18 lines]
> have
> explained this clear enough.

Reply to this Message

rhon101 - 16 Aug 2005 02:19 GMT

Sub PasteICNs()
'
' getPasteICNs Macro
' Macro recorded 7/27/2005 by Administrator
'

Dim Word As Word.Application
Set Word = CreateObject("Word.Application")

'Make Word visible and create a new document
Word.Visible = True
Word.Documents.Add

Selection.Paste
Selection.WholeStory
Selection.Font.Size = 8
CommandBars("Database").Visible = True
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCell
Selection.Cells.Split NumRows:=1, NumColumns:=2, MergeBeforeSplit:=False
Selection.MoveRight Unit:=wdCharacter, Count:=5, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=2, Extend:=wdExtend
Selection.Columns.Delete
Selection.Rows.ConvertToText Separator:=wdSeparateByParagraphs, _
NestedTables:=True
Selection.WholeStory
Selection.Copy
End Sub

> It would be easier to help you is you pasted the code from your macro into
> the body of a message that you post back here.
[quoted text clipped - 21 lines]
> > have
> > explained this clear enough.

Reply to this Message

Doug Robbins - 16 Aug 2005 05:07 GMT

After the Selection.Paste, what do you have in the document?

Is it a list of numbers that looks like

000204123456000¶
010204123456060¶
020204123457780¶

If that is the case, at that point, I would use the following code

Dim i as Long, myrange as Range
With ActiveDocument
For i = 1 to .Paragraphs.Count
Set myrange = .Paragraphs(i).Range
myrange.Text = Left(myrange, 2) & vbTab & Mid(myrange, 3, 2) & vbTab
& Mid(myrange, 5, 6) & vbTab & Mid(myrange, 10, 3) & vbTab & Right(myrange,
3)
Next i
End With

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Sub PasteICNs()
> '
[quoted text clipped - 53 lines]
>> > have
>> > explained this clear enough.

Reply to this Message

rhon101 - 17 Aug 2005 02:44 GMT

Yes, except it don't have the first two numbers on the list. The 00-99 I was
refering to is on the screen that I want to paste to. Those numbers are just
representations of the number of ICN that are put on the page. They are
protected and can not be changed. A good reference tool because if you know
you have 20 INC's that you need to retrieve then if you are not on the number
19 after you have entered all the ICN's (because the first ICN will be in the
placeholder number 00) then you have made a mistake by either skipping or
duplicating an ICN. The screen that I want to paste to has 00-02 on the first
row left to right and continues down to 99. For each ICN to be pasted it
needs to seperate the ICN into 4 parts, for example between 00 and 01 the
numbers (ICN's) need to paste like:
-- ----- --- --- after that ICN is entered it automatically tabs to the
next field to paste another ICN. The program does not recognize any other
fields on this screen, it only tabs to the fields where you enter the ICN's.
I will try what you suggested when I get to work tomorrow. Thank you and I
hope it works too.

> After the Selection.Paste, what do you have in the document?
>
[quoted text clipped - 73 lines]
> >> > have
> >> > explained this clear enough.

Reply to this Message

Doug Robbins - 17 Aug 2005 05:14 GMT

So even though you don't paste the 00-99, after you do paste, what appears
is

000204123456000¶
010204123456060¶
020204123457780¶

If that is the case, the code that I gave you will insert the necessary
tabs.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Yes, except it don't have the first two numbers on the list. The 00-99 I
> was
[quoted text clipped - 110 lines]
>> >> > have
>> >> > explained this clear enough.

Reply to this Message