Hi ,

  I am having problem in prining the flex grid with the grid in it.

I am writing these commands . It is only prining one page of the grid rest of the pages don't print. Please help.

With Printer
   .Orientation = 2
   .PaintPicture MSFlexGrid1.Picture, 0, 0, 15000, 18000
   .NewPage
   .EndDoc
End With

Thanks in advance

What other pages are there? Is it only the grid that you're trying to print?

[Edited by ~Bean~ on 17-04-2003 at 08:58 AM GMT]

____________________________
Eggheads unite! You have nothing to lose but your yolks.

Hi

I believe that is because you are only going to be able to print out what is visible on the form.

Try the following code, I used it and it works ok.

Public Function PrintMSFlexgrid(title As String, orientation As Integer, grdName As MSFlexGrid, Ptr As Printer, line_spaces As Integer) ' Local declares Dim lRowCount As Long Dim iColCount As Integer Dim lRowLoop As Long Dim iColLoop As Integer Dim iTabPos As Integer Dim found_legend As Boolean Dim print_line As Boolean Dim top As Integer Dim outstr As String Printer.orientation = orientation ' print the title if one has been  entered. If title <> "" Then     Ptr.FontName = grdName.Font     Ptr.FontName = "Comic Sans MS"     Ptr.FontSize = 14     Ptr.Print title     Ptr.Print ""     Ptr.Print "" End If 'loop through the cells and print them iTabPos = 0 ' Start function lRowCount = grdName.Rows - 1 iColCount = grdName.Cols - 1 For lRowLoop = 0 To lRowCount     grdName.Row = lRowLoop     For iColLoop = 0 To iColCount         If grdName.ColWidth(iColLoop) <> 0 Then             grdName.Col = iColLoop             ' Grab the flexgrid font properties.             Ptr.FontName = "Comic Sans MS" 'grdName.CellFontName             Ptr.FontSize = 8 'grdName.CellFontSize             Ptr.FontBold = grdName.CellFontBold             Ptr.FontItalic = grdName.CellFontItalic             Ptr.FontUnderline = grdName.CellFontUnderline             Ptr.ForeColor = grdName.CellForeColor                              Ptr.Print Tab(iTabPos + 1); grdName.Text;             iTabPos = iTabPos + (grdName.CellWidth / 60) '           Printer.NewPage         End If     Next iColLoop     Printer.Print ""     For i = 1 To line_spaces         Printer.Print ""     Next i     iTabPos = 0 Next lRowLoop Ptr.EndDoc End Function

[Edited by vbgen on 30-07-2003 at 07:17 PM GMT]

Try to use MSHFlexGrid instead of MSFlexGrid.
The advantage is we can assign it directly to
a recordset, back and forth:

flexgrid = rs
rs = flexgrid

Next step, use Excel as the printing medium.
Excel has the capability of CopyFromRecordset.

Here's my code and how to use it.
It'll do both printing via excel
and exporting to excel only.

Try to read thru the code first.
You'll get the simple idea.

To use:

MSHFlexGrid2Excel MSHFlexGrid1, 1  ' export only to excel
MSHFlexGrid2Excel MSHFlexGrid1, 2  ' print via excel

The code:

Public Sub MSHFlexGrid2Excel(ctlFlexGrid As MSHFlexGrid, mode As Integer)     On Error Resume Next          Dim xlApp As New Excel.Application     Dim xlWb As New Excel.Workbook     Dim xlWs As New Excel.Worksheet          Dim nRow As Integer     Dim nCol As Integer          Dim nRows As Integer     Dim nCols As Integer          nRows = ctlFlexGrid.Rows     nCols = ctlFlexGrid.Cols          'open Excel     If mode = 1 Then         'export to excel         xlApp.Visible = True     Else         'print to printer via excel         xlApp.Visible = False     End If          Set xlWb = xlApp.Workbooks.Add     Set xlWs = xlApp.ActiveSheet          'assign MSHFLexGrid into a recordset     'don't forget to add MsADO in your Project/Reference     Dim rs As ADODB.Recordset     Set rs = ctlFlexGrid.DataSource            'fill the whole XL body at once, starting from cell A1     xlWs.Cells(1, 1).CopyFromRecordset rs     'autofit the XL column         For nCol = 1 To nCols         xlWs.Columns(nCol).AutoFit     Next     'the actual printing         If mode = 2 Then 'to printer         xlWs.PrintOut         xlApp.DisplayAlerts = False         xlWb.Close (False)         xlApp.Quit     'else         'do nothing. exporting won't need closing XL.       End If     'cleaning up     Set xlApp = Nothing         Set rs = Nothing End Sub

Hope you find it useful.
Please correct me if you find any bugs.
Thanks!



[Edited by ivosetyadi on 30-07-2003 at 03:59 PM GMT]

[Edited by vbgen on 30-07-2003 at 07:15 PM GMT]

Turns out that it doesn't need the rs after all. Apology.
It is possible to assign flex.DataSource into XL directly.

Here's the modified code, minus the rs:

Public Sub MSHFlexGrid2Excel(ctlFlexGrid As MSHFlexGrid, mode As Integer)
    On Error Resume Next
    
    Dim xlApp As New Excel.Application
    Dim xlWb As New Excel.Workbook
    Dim xlWs As New Excel.Worksheet
    
    Dim nRow As Integer
    Dim nCol As Integer
    
    Dim nRows As Integer
    Dim nCols As Integer
    
    nRows = ctlFlexGrid.Rows
    nCols = ctlFlexGrid.Cols
    
    'open Excel
    If mode = 1 Then
        'export to excel
        xlApp.Visible = True
    Else
        'print to printer via excel
        xlApp.Visible = False
    End If
    
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlApp.ActiveSheet
    
    'fill the whole XL body all at once, starting from cell A1
    xlWs.Cells(1, 1).CopyFromRecordset ctlFlexGrid.DataSource  

    'autofit the XL column    
    For nCol = 1 To nCols
        xlWs.Columns(nCol).AutoFit
    Next

    'the actual printing    
    If mode = 2 Then 'to printer
        xlWs.PrintOut

        xlApp.DisplayAlerts = False
        xlWb.Close (False)
        xlApp.Quit
    'else
        'do nothing. exporting won't need closing XL.  
    End If

    'cleaning up
    Set xlApp = Nothing    

End Sub

I got an email asking why his code (referring to my code above) is not functioning.

Here's several things i should have mentioned earlier in order to avoid confusions:

*. i am using vb6 sp5
*. working on win2000 prof
*. using excel2000

Several things you might wanna check:

*. Search "CopyFromRecordset" in http://msdn.microsoft.com and read about it
*. Check your vb Project-References for the needed object library (in this case Microsoft Excel Object Library)

Hope this helps

I got another email. The code resulted a blank excel sheet, it said. I did some checking, and offer several issues for the matter:

(1)
The MSHFlexGrid to Excel exporting will work,
only if the MSHFlexGrid.DataSource is initialized
with a recordset with values in it.
Usually the source of the recordset would be from database.

For example:

  '----------------------------------------
  Dim con As New ADODB.Connection
  Dim rs As New ADODB.Recordset

  con.Open "dsn=HRD"
  rs.CursorLocation = adUseClient

  rs.Open "SELECT * FROM employee", con, , , 1
  Set flex.DataSource = rs
  '----------------------------------------

Now, you can assign flex.DataSource to Excel using:

  xlWs.Cells(1, 1).CopyFromRecordset flex.DataSource


(2)
If your MSHFlexGrid's DataSource is not initialized with
another rs, then you can substitute the line

  xlWs.Cells(1, 1).CopyFromRecordset flex.DataSource

with

  For nRow = 1 To nRows
      For nCol = 1 To nCols
         With flex
              xlWs.Cells(nRow, nCol).Value = _
                 .TextMatrix(nRow - 1, nCol - 1) & " "
          End With
     Next
  Next

Please be advised that this loop will run in
a very long time if your grid's data is huge.

Doing .CopyFromRecordset is aimed for the speed
of the transfer between VB and Excel ...


(3)
... and that gives me an idea that you can also do this:

(a) first, initialize the grid, not by writing the cells,
    but by putting values into the .DataSource.
    I haven't tried it yet, but if you can figure it out,
    then (b) will run speedy.

(b) do the (speedy) .CopyFromRecordset flex.DataSource

Hi gurus and co-worriers..
i am a new face here ..and very impressed by kind of support given here. gr8 work !
now comg to my doubt.. whats the value thats being expected for the printer datatype ? help !!
thanx in advance..

quote:

---

steve_w wrote:
Hi

I believe that is because you are only going to be able to print out what is visible on the form.

Try the following code, I used it and it works ok.....

Public Function PrintMSFlexgrid(title As String, orientation As Integer, grdName As MSFlexGrid, Ptr As Printer, line_spaces As Integer


!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1

Hi Anitha and welcome to the forum,

When calling the function just pass in the word printer. This a the printer object currenlty being used by your pc. I think thats what you mean, post back if not.


Cheers Steve  

Thank you Good Sir... You made my day.
cool site  

I have had some limited success with the code posted above.  I did get an output to Excel, except that I only have the last row of the datagrid in Excel.  One of my queries generates 206 rows and the other generates 90 rows.  

What could be the reason that I do not get all of the rows to copy to Excel?

I am using VB6 SP6.
Excel 2002 v10 SP1
Windows 200 Pro

If you need to see the same code yet again, I can post it.  I didn't see the reason to repost the same code.

As near as I can figure out, my problem lies with the following code:
   'fill the whole XL body all at once, starting from cell A1
   xlWs.Cells(1, 1).CopyFromRecordset ctlFlexGrid.DataSource  

What is happening at this point?  I figured out that the code is placed in cell A1, but I cannot get all the rows to print.  I did try the workaround, but that did not produce any results.

Thanks,
-TJ

[Edited by TJL1962 on 04-06-2004 at 01:40 PM GMT]

Hi,
Could you please tell me how to implement this code!
I tried, it is just not working for me. I could not print my Flexgrid data!
Thanks in advance.
Kimo.

quote:

---

steve_w wrote:
Hi

I believe that is because you are only going to be able to print out what is visible on the form.

Try the following code, I used it and it works ok.

Public Function PrintMSFlexgrid(title As String, orientation As Integer, grdName As MSFlexGrid, Ptr As Printer, line_spaces As Integer) ' Local declares Dim lRowCount As Long Dim iColCount As Integer Dim lRowLoop As Long Dim iColLoop As Integer Dim iTabPos As Integer Dim found_legend As Boolean Dim print_line As Boolean Dim top As Integer Dim outstr As String Printer.orientation = orientation ' print the title if one has been  entered. If title <> "" Then     Ptr.FontName = grdName.Font     Ptr.FontName = "Comic Sans MS"     Ptr.FontSize = 14     Ptr.Print title     Ptr.Print ""     Ptr.Print "" End If 'loop through the cells and print them iTabPos = 0 ' Start function lRowCount = grdName.Rows - 1 iColCount = grdName.Cols - 1 For lRowLoop = 0 To lRowCount     grdName.Row = lRowLoop     For iColLoop = 0 To iColCount         If grdName.ColWidth(iColLoop) <> 0 Then             grdName.Col = iColLoop             ' Grab the flexgrid font properties.             Ptr.FontName = "Comic Sans MS" 'grdName.CellFontName             Ptr.FontSize = 8 'grdName.CellFontSize             Ptr.FontBold = grdName.CellFontBold             Ptr.FontItalic = grdName.CellFontItalic             Ptr.FontUnderline = grdName.CellFontUnderline             Ptr.ForeColor = grdName.CellForeColor                              Ptr.Print Tab(iTabPos + 1); grdName.Text;             iTabPos = iTabPos + (grdName.CellWidth / 60) '           Printer.NewPage         End If     Next iColLoop     Printer.Print ""     For i = 1 To line_spaces         Printer.Print ""     Next i     iTabPos = 0 Next lRowLoop Ptr.EndDoc End Function

[Edited by vbgen on 30-07-2003 at 07:17 PM GMT]

---

Hi

Whats not working?,
are you getting an error?
do you have a default printer setup?
are you using the the MSFlexgrid?

Post back and i'll try and help

Cheers Steve  

Hi steve,
you are too good. Thanks in advance.

Let me explaing the scenario in length.

I have a MSFlexgrid in my xl app. that gets populated with data when the user clicks a button. This works well. I want to give the ability to user to print the content from the flexgrid. I tried to use Printer.Print , it says the object not found. I think it is looking for the printer object - I am blank here... May be not possible in VBA!

I tried the one I found here. [Public Function PrintMSFlexgrid], which is very interesting!

I declared the locals like, printer name, orientation...etc...
AND TRIED TO CALL THE FUNCTION THRU THE OTHER MODULE. It flopped!

May be I am not implementing this properly! I need some help here. May be an example of realtime implemenation of this code would help me better understand how this will work.

Any help in this is greatly appreciated. If i shud give u more info, plz let me know.

Thanks again.
KIMO

quote:

---

steve_w wrote:
Hi

Whats not working?,
are you getting an error?
do you have a default printer setup?
are you using the the MSFlexgrid?

Post back and i'll try and help

Cheers Steve  

---

Hai
I am sorry to cross post here.

Yet try this code and this will print u r flexgrid in A3 size.Heresx ur code.

Private Sub Command1_Click() Dim d As Integer     Printer.ScaleMode = vbTwips     d = Printer.Width / Me.MSHFlexGrid1.Width     Printer.PaperSize = vbPRPSA3     Printer.Orientation = vbPRORLandscape          Printer.PaintPicture MSHFlexGrid1.Picture, 0, 0     Printer.EndDoc End Sub