Excel VBA: Copy range into DOS system

[trickshot1126]

Hello and happy Friday!

I have an excel document that the user inputs information. The information will ultimately be pasted into a dos based system that keeps records of all notes for each person.

The dos system note size is limited to 1,035 characters or 69 spaces left to right and 15 lines. What i am looking to do is use VBA code to copy the range of cells, remove all line breaks and the output would fit into the given area.

I currently use code that i can paste into the dos based system but there is a lot of unused space due to line breaks limiting the information i can move at one time.

Any help would be greatly appreciated. If i did not explain this well enough please let me know and i will attempt to explain further.

Thanks!

 

[SkipVought]

HI,

BTW, VBA questions are best addressed in forum707.

You're trying to load a SPREADSHEET into 69x15 characters. How does that map? It's almost apples to oranges???

So how many Excel columns & rows? What's in the cells? Lots of unanswered questions!

Please post an example of the range being copied and a detailed explanation of the issues in the target document.

DOS System: so is that another computer, because you're probably running a Windows Operating System, so where is the Disk Operating System?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

Good Morning,

My apologies for placing this in the incorrect forum.

I will attempt to make my request more clear. If need be please move the thread to the proper thread.

An excel document has been created in which a user inputs information throughout a call with a customer. Once completed the user will select "Copy" which triggers VBA code that copies the data into the clipboard to be pasted where needed. One program the info is being pasted is a DOS based program Attachmate.

Unfortunately, i am unable to post a screen shot of the excel document. However, the pasted data that i am looking to configure looks like this:

EFF DATE: 12/14/2015
CALLER NAME: John Doe
POLICY TYPE: Connections
NOTES: This is an example note of a sales note that will need to be pasted into Attachmate. Currently, it is pasted line by line by line.

Essentially, what i am looking to do is change the output in the clip board to better fit the Attachmate limitations. It would look more like:

EFF DATE: 12/14/2015 CALLER NAME: John Doe POLICY TYPE: Connections NO
TES: This is an example note of a sales note that will need to be past
ed into Attachmate. Currently, it is pasted line by line by line.

I hope this is a little bit more clear.

Thanks a lot for the help. It is greatly appreciated.

 

[SkipVought]

What Attachmate Program? Extra, Reflections, ???

BtW, Attachmate runs in a Windows operating system not a DOS. It is a terminal emulator, emulating what was referred to as dumb terminal. Your emulator probably is an I/O to a mainframe system.

Do you already have code for this exercise? I'd recommend using the PutString() function to put each value exactly where it is need.

So on the terminal screen, are there fields that have the [highlight #FCE94F]text[/highlight], [highlight #FCE94F]EFF DATE:[/highlight], [highlight #FCE94F]CALLER NAME:[/highlight], [highlight #FCE94F]POLICY TYPE:[/highlight] and [highlight #FCE94F]NOTES:[/highlight]?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

Hi Skip, thank you for the reply.

We use EXTRA! X-treme as our mainframe system. The page that the note needs to be pasted to does not have field names. It is just a free form section for notes. I have attached a screenshot of the Attachmate screen. I added the numbers in green to show the number for rows and columns available for text.

 

[SkipVought]

In addition to trying to help you arrive at a solution, I also want you to understand the environment you're working in.

This is what is on the Attachmate website...

Extra! X-treme
Micro Focus Extra! X-treme is a Windows-based terminal emulator that connects users to IBM, UNIX, and Linux hosts.

So your mainframe system is probably IBM, UNIX or Linux. Used to be that these computer systems had "dumb" terminals or what was referred to as "green screens" as the I/O. Today these dumb terminals have been replaced by smart PC based emulators, like the one you have.

Okay, I appreciate the screen shot that you uploaded.

Could you do this to demonstrate your problem:
1) Enter some nonsense into each cell in your sheet, 2) copy those 8 cells (and I'm assuming that you have only 8 un-merged cells) and 3) paste into your screen. Then 4) upload the screen shot of that result, please.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

Hi Skip,

The following are the links for two screen shots. The first is the Attachmate screen and second is the excel from the excel doc.

http://files.engineering.com/getfil...0b6-784a4174a5cb&file=Attachmate_PUUC_Ex1.JPG

http://files.engineering.com/getfil...4d26-ac53-e1377a5d710c&file=Excel_Example.JPG

Here is the code that copies the info into clipboard to be pasted into attachmate:

Sub CopyToCCP(strt As Integer, fnsh As Integer)
Dim paste As String, i As Integer, er As Integer
Dim DataObj As New MsForms.DataObject

    paste = ""
        With ActiveSheet
        For i = 4 To 9
            If Trim(.Cells(i, 3)) <> "" Then
                paste = paste & .Cells(i, 1) & ": " & .Cells(i, 3) & vbNewLine
            End If
        Next i
    End With
    With ActiveSheet
        For i = strt To fnsh
            If Trim(.Cells(i, 3)) <> "" Then
                paste = paste & .Cells(i, 1) & ": " & .Cells(i, 3) & vbNewLine
            End If
        Next i
    End With
    DataObj.SetText paste
    DataObj.PutInClipboard

In the above code Strt is 65 and fnsh is 65.

 

[SkipVought]

So are you saying that when you copy C65 that the only thing that ends up in your terminal screen is...
[tt]
This is an example of a note that need to be copied into Attac
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

When the VBA runs it pulls the information in rows 4 - 9 and row 65.

In this case, yes. The code does not know to add a new line to continue with the note due to the limitations in the Attachmate window.

What i may need to do is copy the text without the "VBNewline" and have a function that reviews the block of text from the copied info and moves info to a new line by character limits. I'm just not sure how it would go down in code.

 

[SkipVought]

So you need to parse your note in order to make it fit into 1) the space remaining on the first line of notes and the 2) the remaining 11 rows of 69 characters?

If that's the case, how often do your notes fill the remaining space on the screen? Can you afford to put all your notes in the 11 rows below the forth row containing NOTES:?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

Although it happens, it is not often that a note would completely fill up the space of 1,035 characters.

It happens often enough that i am not worried about solving for this scenario at the moment.

I have uploaded an additional screen shot of the format i would prefer the output to be in the clipboard. When i paste into attachmate.

 

[SkipVought]

So in your posted code, instead of vbNewLine, you would have a SPACE character.

It appears that you can't simply paste a 1035 max character string, because it will truncate at 69 characters, correct?

If that's the case, you need to insert a vbNewLine every 69 characters. I'd assemble the string, in fact, you could do it in a sheet by concatenating the 8 cells together into one long string and the another cell to line break into 15 69 character chunks that you could copy.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

You are correct. If i were to just allow it to count the 1035 characters it would not work because attachmate would just cut everything after the 69th character.

Is there a way to to this in VB rather than concantenating on another sheet?

 

[SkipVought]

Concatenate all your text into a string variable YourString, separated by a single SPACE character.

Then loop thru YourString For I = 1 To Len(YourString) Step 69, inserting a vbNewLine and concatenate that into Paste.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's what that would look like in your procedure...

Sub CopyToCCP(strt As Integer, fnsh As Integer)
    Dim sPaste As String, i As Integer, er As Integer
    Dim DataObj As New MsForms.DataObject
    Dim sTemp As String
    
    Const MAXCHR = 69

    With ActiveSheet
        For i = 4 To 9
            If Trim(.Cells(i, 3)) <> "" Then
                sTemp = sTemp & .Cells(i, 1) & ": " & .Cells(i, 3) & " "
            End If
        Next i
        
        For i = strt To fnsh
            If Trim(.Cells(i, 3)) <> "" Then
                sTemp = sTemp & .Cells(i, 1) & ": " & .Cells(i, 3) & " "
            End If
        Next i
'[b]parse the sTemp string into 69 character segments        
        For i = 1 To Len(sTemp) Step MAXCHR
            sPaste = sPaste & Mid(sTemp, i, i + MAXCHR - 1) & vbNewLine
        Next
'[/b]        
    End With
    DataObj.SetText paste
    DataObj.PutInClipboard
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

Thank you for that code...It's in the right direction.

This is the string of text i am getting in the debug window:

EFF DATE: 12/14/2015 CALLER NAME: John Doe POLICY TYPE: Connections N
OTES: This is an example of a note that needs to be copied into Attachmate. Currently the VBA copies each row indvidually rather than inse
hmate. Currently the VBA copies each row indvidually rather than inserts a new line at character 69.
rts a new line at character 69.

Thoughts?

 

[SkipVought]

What string are you viewing in the Immediate window from Debug.Print?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

Yes. And when i paste the info into attachmate it does not work.

The first line is cut properly but the second line does not cut at the 69th character like the first.

 

[SkipVought]

What does the second line look like?

Screen shot maybe?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[trickshot1126]

I have attached a screen shot showing the immediate window in excel, the clipboard pasted into notepad and also pasted into the attachmate window.