Problem formating VB string for clipboard

[MrConfused]

I am trying to put together a string that can be sent to the clipboard and then pasted into a spreadsheet (in this case a works spreadsheet). The problem I have is the space characted seems to be being ignored when it is a the start of the next line. The part of the string is this:-

CString$ = CString$ + Chr$(13)
CString$ = CString$ + Space$(25)
CString$ = CString$ + String$(12, "=")

This goes to the next line, but the 25 spaces are ignored and so the 12 "=" characters are not 25 spaces in from the left of the column.

Any clues?

[MrConfused]

I have found the answer. Seems you need the " character first:-

CString$ = CString$ + Chr$(13)
CString$ = CString$ + """" + Space$(25)
CString$ = CString$ + String$(12, "=")

...quite why you need four " characters in a row I don't know.

[MrConfused]

Another problem. It seems that if the new line in the string you put in the clipboard and then paste into works spreadsheet is numerical (i.e it is a string, but just cosists of numbers (with no letters before) MSworks stripps the blank spaces out even if you use the """".

For example if Numb$ is "1234" and you want it to appear 10 spaces in from the left of the spreadsheet column (with no letters before it) you might think the folllowing would work

Numb$ = "1234"
CString$ = """" + Space$(10) + Numb$
Clipboard.Clear
Clipboard.SetText CString$
End

I have thought of a way around this but it is not ideal because you are laying a trap for yourself in the future.

Numb$ = "1234"
CString$ = string$(10,chr$(160)) + Numb$
Clipboard.Clear
Clipboard.SetText CString$
End

This uses the other space character chr$(160) as opposed to "normal" one chr$(32). Does anyone have a better solution to this because using chr$(160) could cause problems in the future if I forget I used it!

[MrConfused]

I'm starting to get annoyed :mad: . I've finally got one of my reports to go to the clipboard. All seemed o.k until I read down the report (now in MSWorks spreadsheet) and parts of some of the lines have jumped columns. Seems that if the spreadsheet comes accross a comma in a line of text it puts the text after the comma in the next column. Great. Wonderfull! Now what do I do?

I could strip out all the commas I suppose using some code. Surely there is another way. Most of my clients want the report to go to MS Excel. Maybe there is a way to do that without using the clipboard?? Or is there a way of altering the string that goes to the clipboard so that the spreadsheet does not mess it up. That would be ideal.

Any help welcome.

[mnemtsas]

OK here's some sample code I use to output an ADODB recordset directly to Excel. You'll need to include Excel 8.0 or better as a project reference for this to work. It should give you a good idea of how to think about outputting stuff to Excel.

'*******************************************************************************
' excelPrintRecordSet(Sub)
'
' PARAMETERS:
'
'
' RETURN VALUE:
'
'
' DESCRIPTION:
' Test function that will print out all the records from a recordset in Excel.
'*******************************************************************************
Public Sub excelPrintRecordSet(rstTmp As ADODB.Recordset)
  Dim appExcel As Excel.Application
  Dim wbkReport As Excel.Workbook
  Dim wksReport As Excel.Worksheet
  Dim intField As Integer, intRow As Integer
  
  Const PROCEDURE_NAME As String = "excelPrintRecordSet"

  On Error GoTo errorHandler

  Set appExcel = New Excel.Application
  appExcel.Visible = True
  Set wbkReport = appExcel.Workbooks.Add
  'wbkReportame = "Kilometer Report"
  Set wksReport = wbkReport.Worksheets(1)

  If rstTmp.EOF <> True Then
    rstTmp.MoveFirst
    intRow = 1
    Do
      For intField = 0 To rstTmp.Fields.Count - 1
        wksReport.Cells(intRow, intField + 1) = rstTmp.Fields(intField).Name & "=" & rstTmp.Fields(intField).Value
      Next intField
      rstTmp.MoveNext
      intRow = intRow + 1
    Loop Until rstTmp.EOF = True
  End If
  Exit Sub

errorHandler:
  frmErrorHandler.errorForm MODULE_NAME, PROCEDURE_NAME
  Err.Clear
End Sub

[MrConfused]

I don't have Excel. However the jumping columns problem is solved by putting chr$(34) before each line of text (that is when sending the text to MSWorks spreadsheet). Whether that is true for Excel I have no idea and would like to know the answer.

I had another problem. I had got my reports to send the text to a text box (rather than the list box I had used previously) because MS have done the work for you as far as copying and pasting (just select the text, right click on it etc.) goes in a text box. Trouble is the text box can only handle 64K of data. When I ran the code to fill the text box I found that for the larger reports the last x lines were missing! No error message either! :mad: List boxes don't have the "automatic" copy and paste support.

So this is what I have come up with:-

I changed the "Multiselect" property of the list box to "extended and used the following code in a command button:

CString$ = ""
For n% = 1 To ScrnGenLst.ListCount
If ScrnGenLst.Selected(n% - 1) = True Then
CString$ = CString$ + ScrnGenLst.List(n% - 1)
CString$ = CString$ + Chr$(13) + Chr$(10)
End If
Next n%

Clipboard.Clear
Clipboard.SetText CString$
CString$ = ""

The above pastes fine into MSWorks word processor providing once you paste the info you change the font to the same as the one used in the list box, and the margins are small enough so the lines of text can fit in. Presumably the same would be true for MsWord.

Now all I need to know is if text formatted as above will paste into Excel o.k as is, or do I have to add the chr$(34) character to the beginning of each line of text (as with MSWorks spreadsheet), or what other character/characters the string needs to paste o.k into Excel. To recap if you don't put chr$(34) before spaces and then a number i.e " 234.87" when pasting to MSWorks spreadsheet it strips the spaces out and assumes the text is simply the number 234.87.

[mnemtsas]

Most of my clients want the report to go to MS Excel. Maybe there is a way to do that without using the clipboard??

The code I posted is a way of doing that.

Get Excel and all of this stuffing about with the clipboard will be fixed.