Results 1 to 5 of 5

Thread: MSFLex GRID to Microsoft Excel

  1. #1

    Thread Starter
    Frenzied Member joefox's Avatar
    Join Date
    Oct 2004
    Posts
    1,318

    MSFLex GRID to Microsoft Excel

    Hi,
    I am trying to get the data from my MSFlexGrid to a Excel spred sheet, i have sorta the skeleton, but I cant get it to work right.

    I wanted it to put the heads like i have in the grid, and then the data below it.

    Here are my colum headers, dont know if that helps:

    MSFlexGrid1.TextMatrix(0, 0) = " ID #" '
    MSFlexGrid1.TextMatrix(0, 1) = "Business Name"
    MSFlexGrid1.TextMatrix(0, 2) = "Address"
    MSFlexGrid1.TextMatrix(0, 3) = "Customer Name"
    MSFlexGrid1.TextMatrix(0, 4) = "Model #"
    MSFlexGrid1.TextMatrix(0, 5) = "Serial #"
    MSFlexGrid1.TextMatrix(0, 6) = "Controll #"

    VB Code:
    1. Private Sub Command5_Click()
    2.     Dim iCols  As Integer
    3.     Dim oApp As Object 'Excel.Application
    4.     Dim oWB As Object 'Excel.Workbook
    5.     Set oApp = CreateObject("Excel.Application")
    6.     Set oWB = oApp.Workbooks.Add
    7.     Label39.Caption = "Creating Excel application."
    8.  
    9.     ' Dont know what to put here
    10.  
    11.     oWB.SaveAs "C:\ExchangeTest01.xls"
    12.     oWB.Saved = True
    13.     ' Closing the instances so nothing is left in memory
    14.     oWB.Close
    15.     Set oWB = Nothing
    16.     oApp.Quit
    17.     Set oApp = Nothing
    18.     Label39.Caption = "Excel Spreed sheet completed."
    19. End Sub

  2. #2

  3. #3

    Thread Starter
    Frenzied Member joefox's Avatar
    Join Date
    Oct 2004
    Posts
    1,318

    Re: MSFLex GRID to Microsoft Excel

    Sweet post!

    I was trying to work on the excel to msflexgrid one, when i pull the values from the excel spreed sheet, i only get how many rows i did in the properties of the msflexgrid.

    So how would you make it where, say someone was updating the excel spreed sheet, and you have no idea how many rows are in the excel spreed sheet, and you want to display them all?

    this is what i have

    VB Code:
    1. Private Sub Command1_Click()
    2. Dim xlObject    As Excel.Application
    3. Dim xlWB        As Excel.Workbook
    4.        
    5.     Set xlObject = New Excel.Application
    6.     Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") 'Open your book here
    7.                
    8.     Clipboard.Clear
    9.     With xlObject.ActiveWorkbook.ActiveSheet
    10.         .Range("A1:F7").Copy 'Set selection to Copy
    11.     End With
    12.        
    13.     With MSFlexGrid1
    14.         .Redraw = False     'Dont draw until the end, so we avoid that flash
    15.         .Row = 0            'Paste from first cell
    16.         .Col = 0
    17.         .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
    18.         .ColSel = .Cols - 1
    19.         .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
    20.         .Col = 1            'Just to remove that blue selection from Flexgrid
    21.         .Redraw = True      'Now draw
    22.     End With
    23.        
    24.     xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
    25.    
    26.     'Close Excel
    27.     xlWB.Close
    28.     xlObject.Application.Quit
    29.     Set xlWB = Nothing
    30.     Set xlObject =

  4. #4
    Oi, fat-rag! bushmobile's Avatar
    Join Date
    Mar 2004
    Location
    on the poop deck
    Posts
    5,592

    Re: MSFLex GRID to Microsoft Excel

    You can use the UsedRange property:

    VB Code:
    1. Private Sub ExcelToFlexgrid()
    2.     Dim xlObject    As Excel.Application
    3.     Dim xlWB        As Excel.Workbook
    4.     Dim lCols       As Long
    5.     Dim lRows       As Long
    6.  
    7.     Set xlObject = New Excel.Application
    8.     Set xlWB = xlObject.Workbooks.Open("C:\Book1.xls") 'Open your book here
    9.                
    10.     Clipboard.Clear
    11.     With xlObject.ActiveWorkbook.ActiveSheet
    12.         .UsedRange.Copy
    13.         lCols = .UsedRange.Columns.Count
    14.         lRows = .UsedRange.Rows.Count
    15.     End With
    16.        
    17.     With MSFlexGrid1
    18.         .Redraw = False     'Dont draw until the end, so we avoid that flash
    19.         .Clear
    20.         .Rows = lRows
    21.         .Cols = lCols
    22.         .Row = 0            'Paste from first cell
    23.         .Col = 0
    24.         .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
    25.         .ColSel = .Cols - 1
    26.         .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
    27.         .Col = 1            'Just to remove that blue selection from Flexgrid
    28.         .Redraw = True      'Now draw
    29.     End With
    30.        
    31.     xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
    32.    
    33.     'Close Excel
    34.     xlWB.Close
    35.     xlObject.Application.Quit
    36.     Set xlWB = Nothing
    37.     Set xlObject = Nothing
    38. End Sub

  5. #5
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: MSFLex GRID to Microsoft Excel

    In general working with clipboard is NOT a good habit (from my point of view) - user can have something copied right before you decide to use it so what do you? ...
    For conventional (non-clipboard) method see link in my signature.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width