I have developed hospital managment system. I have rich text box in userform. I save it in access database as .textrtf. It is working fine. I have designed command to generated word file with required patient data. I wish to insert rtf data into word file. I have vb6 as developing tool and ms office 2007 for word document. part of cord given below. Please guide me.
Fairly easy if you can afford to trash the user's clipboard in the process:
Code:
Option Explicit
Private Sub Command1_Click()
With RichTextBox1
.SelStart = 0
.SelLength = &H7FFFFFFF
.SelLength = .SelLength - Len(vbNewLine) 'Back up over trailing paragraph mark.
Clipboard.Clear
Clipboard.SetText .SelRTF, vbCFRTF
.SelLength = 0
End With
With CreateObject("Word.Application")
With .Documents
With .Open(App.Path & "\Document.doc")
.Bookmarks("Lab").Range.Paste
.Save
End With
.Close
End With
.Quit
End With
Command1.Enabled = False
End Sub
Private Sub Form_Load()
ChDir App.Path
ChDrive App.Path
On Error Resume Next
Kill "Document.doc"
On Error GoTo 0
FileCopy "Doc Orig.doc", "Document.doc"
RichTextBox1.LoadFile "Document.rtf", rtfRTF
End Sub
That's normally a poor idea.
However even though you can copy to an IDataObject with the RichTextBox (via TOM) instead of using the user's clipboard I don't see any way to paste from IDataObject using Word Automation.
You'd be far better off not using Word for this AT ALL. It is easy to end up with dangling invisible orphaned MS Word instances running, if not hung on dialogs. Word really should never be used in such a manner.
My guess is that this goes back to shambling attempts to print reports containing RTF text. I remember a thread where you were awkwardly bashing at a DataReport to print a page of data by stuffing things into RptLabel controls.
There are perfectly good ways to print to printers, and that includes printing RTF text. The trickiest part is proper pagination, but even that can be managed.
Here's an example of printing an RTF document, in 2 columns spanning multiple pages with page numbering.
A programmer should be able to borrow from this to write a specific program with different requirements. It's not a trivial process nor is it template based, but it really isn't that hard.
It's an obsolete hack nobody should be using. Better to create/alter RTF documents. All of the problems described in that ancient MS KB article apply to desktop application programs as well as server-side code.
Last edited by dilettante; Jan 9th, 2021 at 02:01 PM.
Personally, I'm not seeing the big issue here...if the data is already stored in a table as rtf information, simply retrieve it, put it into an RTB, and then create the word document with the other information you want and then add a line similar to the line I showed in post 2.
What am "I" missing here? Is it because you want the FORMAT of what was in the RTB to be copied into the Word doc?
Yes, his issue is that he has stored rich text in the database ands wants to print rich text. I suspect that Word is only here as a middleman because he doesn't know how to print from VB6.
Well I have table in RTB and I wish to print the same. If I use oDoc.Bookmarks.Item("LABS").Range.Text = RichTextBox1.Text then I get plain text without table.
Fairly easy if you can afford to trash the user's clipboard in the process:
Code:
Option Explicit
Private Sub Command1_Click()
With RichTextBox1
.SelStart = 0
.SelLength = &H7FFFFFFF
.SelLength = .SelLength - Len(vbNewLine) 'Back up over trailing paragraph mark.
Clipboard.Clear
Clipboard.SetText .SelRTF, vbCFRTF
.SelLength = 0
End With
With CreateObject("Word.Application")
With .Documents
With .Open(App.Path & "\Document.doc")
.Bookmarks("Lab").Range.Paste
.Save
End With
.Close
End With
.Quit
End With
Command1.Enabled = False
End Sub
Private Sub Form_Load()
ChDir App.Path
ChDrive App.Path
On Error Resume Next
Kill "Document.doc"
On Error GoTo 0
FileCopy "Doc Orig.doc", "Document.doc"
RichTextBox1.LoadFile "Document.rtf", rtfRTF
End Sub
That's normally a poor idea.
However even though you can copy to an IDataObject with the RichTextBox (via TOM) instead of using the user's clipboard I don't see any way to paste from IDataObject using Word Automation.
You'd be far better off not using Word for this AT ALL. It is easy to end up with dangling invisible orphaned MS Word instances running, if not hung on dialogs. Word really should never be used in such a manner.
My guess is that this goes back to shambling attempts to print reports containing RTF text. I remember a thread where you were awkwardly bashing at a DataReport to print a page of data by stuffing things into RptLabel controls.
There are perfectly good ways to print to printers, and that includes printing RTF text. The trickiest part is proper pagination, but even that can be managed.
Thanks for help. I used clipboard and it worked well. I have used it in a slightly different way(code given below). I wish to use same thing for excel but then paste method fails. Is there any way to paste table saved as rtf data into excel cell? Please guide me.
Code:
If Len(rst.Fields("Lab").Value) > 0 Then
Dim sRTF As String
sRTF = rst.Fields("Lab").Value
Dim lSuccess As Long
Dim lRTF As Long
Dim hGlobal As Long
Dim lpString As Long
lSuccess = OpenClipboard(Me.hwnd)
lRTF = RegisterClipboardFormat("Rich Text Format")
lSuccess = EmptyClipboard
hGlobal = GlobalAlloc(GMEM_MOVEABLE Or GMEM_DDESHARE, Len(sRTF))
lpString = GlobalLock(hGlobal)
CopyMemory lpString, ByVal sRTF, Len(sRTF)
GlobalUnlock hGlobal
SetClipboardData lRTF, hGlobal
CloseClipboard
GlobalFree hGlobal
oDoc.Bookmarks("Lab").Select
oWord.Selection.Paste
Else
oDoc.Bookmarks("Lab1").Range.Text = ""
oDoc.Bookmarks("Lab").Range.Text = ""
End If
Personally, I'm not seeing the big issue here...if the data is already stored in a table as rtf information, simply retrieve it, put it into an RTB, and then create the word document with the other information you want and then add a line similar to the line I showed in post 2.
What am "I" missing here? Is it because you want the FORMAT of what was in the RTB to be copied into the Word doc?
Yes actually I want a table to be printed in word file which is saved as rtf data in database.
I can't find anything on pasting RTF in an Excel Cell.
I'm pretty sure Excel doesn't use RTF for formatted text in cells.
You can use the plain text, but not the formatted text.
Personally, I'm not seeing the big issue here...if the data is already stored in a table as rtf information, simply retrieve it, put it into an RTB, and then create the word document with the other information you want and then add a line similar to the line I showed in post 2.
What am "I" missing here? Is it because you want the FORMAT of what was in the RTB to be copied into the Word doc?
Yes actually I want a table to be printed in word file which is saved as rtf data in database.
Yes, his issue is that he has stored rich text in the database ands wants to print rich text. I suspect that Word is only here as a middleman because he doesn't know how to print from VB6.
yes you are right. I have used word just to print data. Previously I was using datareport. Everything was ok except printing table. So I created word file to print table only. Thanks
Finally word file generation and table printing problem solved using clipboard copy and then paste.
Now I wish to use similar method to print table into excel file but it shows error as "paste method failed" with error at .paste line. Please guide me.
Thanks
Show your actual code for pasting the data in Excel.
Please not, as I stated before, Excel does not accept RTF for formatted text in cell.
My code is given below
Code:
Sub printdischarge()
'On Error GoTo ErrorHandler
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
Dim qry As String, i As Integer
Dim n As Long
Dim wb As Excel.Workbook
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "\\192.168.1.120\d\d\Database.accdb"
qry = "SELECT * FROM IPD WHERE [IP No] = '" + Me.TextBox25.Text + "'"
rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
Set wb = objExcel.Workbooks.Add("\\192.168.1.120\d\d\Discharge.xlsx")
With wb.Worksheets("Sheet1")
.Range("A5") = "Patient Name: " & rst.Fields("Name").Value
.Range("A4") = "Reg No.: " & rst.Fields("Reg No").Value
.Range("B5") = "Age/Sex: " & rst.Fields("Age").Value & "/" & rst.Fields("Sex").Value
.Range("A6") = "Date of admission: " & Format(rst.Fields("Admitdate").Value, "dd/mm/yyyy")
.Range("B4") = "IPD No.: " & rst.Fields("Ip No").Value
.Range("B6") = "Date of discharge: " & Format(rst.Fields("Dischargedate").Value, "dd/mm/yyyy")
.Range("A10") = rst.Fields("Diagnosis").Value
.Range("A13") = rst.Fields("History").Value
.Range("A16") = rst.Fields("Examination").Value
.Range("A19") = rst.Fields("Hospital course").Value
.Range("A22") = rst.Fields("Investigations").Value
.Range("A28") = rst.Fields("Treatment").Value
.Range("A31") = rst.Fields("Advise").Value
.Range("A34") = rst.Fields("Condition").Value
If rst.Fields("Operation").Value <> "" Then
.Range("A25") = "Operation performed: " & vbNewLine & rst.Fields("Operation").Value
Else
.Range("A25") = ""
End If
End With
If Len(rst.Fields("Lab").Value) > 0 Then
Dim sRTF As String
sRTF = rtb.TextRTF
Dim lSuccess As Long
Dim lRTF As Long
Dim hGlobal As Long
Dim lpString As Long
lSuccess = OpenClipboard(Me.hwnd)
lRTF = RegisterClipboardFormat("Rich Text Format")
lSuccess = EmptyClipboard
hGlobal = GlobalAlloc(GMEM_MOVEABLE Or GMEM_DDESHARE, Len(sRTF))
lpString = GlobalLock(hGlobal)
CopyMemory lpString, ByVal sRTF, Len(sRTF)
GlobalUnlock hGlobal
SetClipboardData lRTF, hGlobal
CloseClipboard
GlobalFree hGlobal
wb.Worksheets("Sheet2").Range("A1").Select
wb.Worksheets("Sheet2").Paste
'This is the error line
End If
objExcel.Visible = True
wb.SaveAs ("\\192.168.1.120\d\d\Discharge\") & rst.Fields("Ip No").Value
wb.Close
objExcel.Application.Quit
VBA.Shell "explorer \\192.168.1.120\d\d\Discharge\" & rst.Fields("Ip No").Value & ".xlsx", vbNormalFocus
rst.Close
cnn.Close
Exit Sub
ErrorHandler:
MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description, vbCritical, "Error"
Exit Sub
End Sub
I want to paste table into excel file and I have table saved in RTB as rtf data and same in database. If I use RTB.text then plain text is pasted not the table. Please guide me.
You have some rtf text. You need tables for layouting the data I assume.
You use Word to create a printable report and now you want all the same stuff also in Excel?
Maybe some pictures could help understand what your final goal is.
You have some rtf text. You need tables for layouting the data I assume.
You use Word to create a printable report and now you want all the same stuff also in Excel?
Maybe some pictures could help understand what your final goal is.
Yes, I want word, PDF and excel files to be generated as most of the software provide. Please help me to solve it.
Thanks
But I still don't understand what you want to do with Excel
Show a picture or an Excel document which shows what you need
At present I want to learn. Initially I created softwere as excel VBA only. So I have all tempalets in excel for printing. But then I had limitation of excel cell as it has fixed maximum hight and so large data are stored but when we print some of them are not on print due to restrictions of cell hight. So I shifted to VB6. I used dataprint for printing discharge card. Here there is no option of .textrtf data. So I created word document with bookmarks. It works well. I wish to learn how to paste same data in excel as .textrtf. I am able to paste rtb.text as plain text but unable to paste as table. Please guide me. Thanks