Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums
 |
•
•
Join Date: Mar 2005
Posts: 32
Reputation: 
Rep Power: 4
Solved Threads: 0
Light Poster
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?
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?
•
•
Join Date: Mar 2005
Posts: 32
Reputation:
Rep Power: 4
Solved Threads: 0
Light Poster
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.
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.
•
•
Join Date: Mar 2005
Posts: 32
Reputation:
Rep Power: 4
Solved Threads: 0
Light Poster
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!
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!
•
•
Join Date: Mar 2005
Posts: 32
Reputation:
Rep Power: 4
Solved Threads: 0
Light Poster
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.
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.
•
•
Join Date: Jul 2004
Location: Adelaide, Australia
Posts: 191
Reputation:
Rep Power: 5
Solved Threads: 1
Junior Poster
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
Mark Nemtsas
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
•
•
Join Date: Mar 2005
Posts: 32
Reputation:
Rep Power: 4
Solved Threads: 0
Light Poster
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.
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.
•
•
Join Date: Jul 2004
Location: Adelaide, Australia
Posts: 191
Reputation:
Rep Power: 5
Solved Threads: 1
Junior Poster
•
•
•
•
Most of my clients want the report to go to MS Excel. Maybe there is a way to do that without using the clipboard??
Get Excel and all of this stuffing about with the clipboard will be fixed.
Mark Nemtsas
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
|
Similar Threads
Other Threads in the Visual Basic 4 / 5 / 6 Forum
Other Threads in the Visual Basic 4 / 5 / 6 Forum
- Previous Thread: Setting Timer/Clock Precision
- Next Thread: selction formula
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
