Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid

[johndmingione]

Dear Reader,

I would like to be educated on how to open an Excel Worksheet within a VSFlexGrid OR MSFlexGrid. The reason I give both as an option is because I want to explore which option is best, so if you have input on the Pros and Cons of using either and which I should use, please give it

Anyways, I know how to open one up within the VSFlexGrid. However, the reason I'm asking this Forum is because I want this VSFlexGrid to open a Worksheet according to what the other one opened previously. So let me explain:

The original Form opens a Worksheet per whatever the User wants. Meaning, he or she can open Worksheets with a certain format. Let's call this worksheet that the User theoretically opens, WorksheetRandom.xls. So the User opens up WorksheetRandom.xls and within it is an entire Workbook. However, the VSFlexGrid on my Home Form has a Worksheet called Report. The VSFlexGrid pulls from that specific Worksheet and populates the VSFlexGrid according to the specific Columns within that Worksheet. SO...what I would like to do is essentially take that same Workbook that they ALREADY CHOSE and take a different Worksheet than the original Home Form pulls and display it on a different VS or MSFlexGrid on a different form.

If I left out any information please let me know. Otherwise, if someone can tell me how to get started it would be greatly appreciated.

[johndmingione]

So I began messing around with the code and just put a very basic version of it trying to open up Excel. Here's the code, tell me if I'm missing something, again it's very basic:

Option Explicit

Dim exApp As Object
Dim exBook As Object
Dim exSheet As Object

Private Sub Form_Activate()

Set exApp = CreateObject("Excel.Application")
Set exBook = exApp.Workbooks.Open(strROBlitZFilePath)
Set exSheet = exBook.Worksheet("Report")

'Code Goes Here :)

Set exSheet = Nothing
Set exBook = Nothing
Set exApp = Nothing

End Sub

strROBlitzFilePath is already Dim'd as a String
And assigned value as the Worksheet that the User chooses which is exactly what I'm looking for. There is an error message that pops up however. Labeled as error 1004. Attached is an image of the error. So you can take a look. If anyone can continue to help me out from here it would be greatly appreciated. I'm going to try and keep plugging away at this on my own.

[westconn1]

did you supply full path to file?

[johndmingione]

Um...I don't exactly what you mean. Is there any chance you can explain it further?

[wilder1926]

Hi all,

This is the code i'm using to open excel file in my MSFlexgrid:

Dim xlObject     As Excel.Application
Dim xlWB         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long

    On Error GoTo MyErrHandler
   
    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (xlam, xlsx, xltm, xlt, xlsm, xltx, xls, txt, csv)"
        .InitDir = "C:\Documents and Settings\all users\Desktop"
        .ShowOpen
        If Not .FileName = "" Then
            Set xlObject = New Excel.Application
            Set xlWB = xlObject.Workbooks.Open(.FileName)

            Clipboard.Clear
            xlObject.Cells.Copy     ' Copy all cells in active worksheet.
            FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheet, NoOfRows, NoOfColumns
            With MSFlexGrid1
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               .Redraw = True      'Now draw
            End With
            xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
            xlWB.Close
            xlObject.Application.Quit
            Set xlWB = Nothing
            Set xlObject = Nothing
        End If
    End With
   
    Exit Sub

MyErrHandler:
    Err.Clear

For this code, you will need a CommonDialog1 to select your excel file you want to upload in your MSFlexgrid

Let me know if it works for you.

[johndmingione]

I'll try it out and let you know.

[johndmingione]

So my code is late-bound, and I've been changing yours around to fit that, because it's early-bound. And I get to this part

FetchNoRowCol

And it fires off the error message

Compile Error:
Sub or function not defined

And it highlights the word above in blue

[wilder1926]

Oh sorry forgot to give you this first part.

Private Sub FetchNoRowCol(ws As Excel.Worksheet, ByRef NoOfRows As Long, _
        ByRef NoOfColumns As Long)
    ' Error-handling is here in case there is not any
    ' data in the worksheet.
    On Error Resume Next
   
    NoOfRows = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row
    NoOfColumns = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
            SearchOrder:=xlByColumns).Column
End Sub

In a botton:

Dim xlObject     As Excel.Application
Dim xlWB         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long

    On Error GoTo MyErrHandler
   
    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (xlam, xlsx, xltm, xlt, xlsm, xltx, xls, txt, csv)"
        .InitDir = "C:\Documents and Settings\all users\Desktop"
        .ShowOpen
        If Not .FileName = "" Then
            Set xlObject = New Excel.Application
            Set xlWB = xlObject.Workbooks.Open(.FileName)

            Clipboard.Clear
            xlObject.Cells.Copy     ' Copy all cells in active worksheet.
            FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheet, NoOfRows, NoOfColumns
            With MSFlexGrid1
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               .Redraw = True      'Now draw
            End With
            xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
            xlWB.Close
            xlObject.Application.Quit
            Set xlWB = Nothing
            Set xlObject = Nothing
        End If
    End With
   
    Exit Sub

MyErrHandler:
    Err.Clear

[johndmingione]

I get the error message that is displayed in an attachment below.

[johndmingione]

It still points to the

FetchNoRowCol

[wilder1926]

Here is an example for you.

This one work.

Let me know if you can upload into your grid.

[wilder1926]

After, if you want to auto fit all columns,

You need to add this part:

      'Auto column fit

    Dim c As Long
    Dim z As Long
    Dim cell_wid As Single
    Dim col_wid As Single

    For c = 0 To MSFlexGrid1.Cols - 1
        col_wid = 0
        For z = 0 To MSFlexGrid1.Rows - 1
            cell_wid = TextWidth(MSFlexGrid1.TextMatrix(z, c))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next z
        MSFlexGrid1.ColWidth(c) = col_wid + 120
    Next c

FULL CODE:

Option Explicit
Private Sub FetchNoRowCol(ws As Excel.Worksheet, ByRef NoOfRows As Long, _
        ByRef NoOfColumns As Long)
    ' Error-handling is here in case there is not any
    ' data in the worksheet.
    On Error Resume Next
   
    NoOfRows = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row
    NoOfColumns = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
            SearchOrder:=xlByColumns).Column
End Sub


Private Sub Command1_Click()
Dim xlObject     As Excel.Application
Dim xlWB         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long

    On Error GoTo MyErrHandler
   
    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (xlam, xlsx, xltm, xlt, xlsm, xltx, xls, txt, csv)"
        .InitDir = "C:\Documents and Settings\all users\Desktop"
        .ShowOpen
        If Not .FileName = "" Then
            Set xlObject = New Excel.Application
            Set xlWB = xlObject.Workbooks.Open(.FileName)

            Clipboard.Clear
            xlObject.Cells.Copy     ' Copy all cells in active worksheet.
            FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheet, NoOfRows, NoOfColumns
            With MSFlexGrid1
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               .Redraw = True      'Now draw
            End With
            xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
            xlWB.Close
            xlObject.Application.Quit
            Set xlWB = Nothing
            Set xlObject = Nothing
        End If
    End With
    
    
    
      'Auto column fit

    Dim c As Long
    Dim z As Long
    Dim cell_wid As Single
    Dim col_wid As Single

    For c = 0 To MSFlexGrid1.Cols - 1
        col_wid = 0
        For z = 0 To MSFlexGrid1.Rows - 1
            cell_wid = TextWidth(MSFlexGrid1.TextMatrix(z, c))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next z
        MSFlexGrid1.ColWidth(c) = col_wid + 120
    Next c
   
    Exit Sub

MyErrHandler:
    Err.Clear
End Sub

[johndmingione]

I tried to do this late-bound but it's too much of a nightmare. I added the Excel Object Library and am getting it to work soon.

[wilder1926]

If you still need help, let me know. It would be easier to send me a copy of your project.

Are you working with Microsoft Office 2007?

[johndmingione]

2010. I can't send you the project due to the rules at my company. Though yes it would be phenomenally easier. But I might be able to send you an entire Form Code. My object library is 14.0 by the way.

[wilder1926]

OK, so you would be able to add this Microsoft Excel 14.0 Object Library to the references of your project. It should work.

If you have something that i can work with, to help you, let me know. I would be happy to help you.

[seenu_1st]

@john, u can try the MSFlexgrid fast fill in my signature.

[johndmingione]

Thank you for your help I was in the hospital all this morning I am late getting into the office. I added the Object Library and am running through the code now fixing it to work. There's some things like Object Names that I still have to change and fix.

@Wilder1926, chances are I will post this entire code up here, possibly in an entire thread or send it to your email or personal message if I run into any complications. I just can't post the entire project. Hopefully, I won't need any help but thank you for your generosity in helping me.

@seenu_1st, what do you mean MSFlexgrid fast fill in my signature?

[johndmingione]

@Wilder1926, I used your code and I have changed somethings up. First off, the name of the CommonDialog Control because mine was named differently, secondly re-wrote

FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheets, NoOfRows, NoOfColumns

To

FetchNoRowCol xlObject.ActiveWorkbook.Worksheets("Report"), NoOfRows, NoOfColumns

Because inside the Workbook we allow the User to open up I only want one Worksheet to be displayed, that Worksheet's name is "Report"

However, when I click Okay nothing happens. Nothing loads.

Here's the full code:

Option Explicit

Dim xlObject        As Excel.Application
Dim xlWB            As Excel.Workbook
Dim NoOfRows        As Long
Dim NoOfColumns     As Long
Dim Pattern         As String

Private Sub closeButton_Click()
    
    frmRoView.Show
    frmViewMyTips.Visible = False
    Unload Me
    
End Sub

Private Sub FetchNoRowCol(ws As Excel.Worksheet, ByRef NoOfRows As Long, _
        ByRef NoOfColumns As Long)
    ' Error-handling is here in case there is not any
    ' data in the worksheet.
    On Error Resume Next
   
    NoOfRows = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
            SearchOrder:=xlByRows).Row
    NoOfColumns = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
            SearchOrder:=xlByColumns).Column
End Sub
Private Sub cmdLoad_Click()

    On Error GoTo MyErrHandler
    
    Pattern = "Excel(*.xls)|*.xls;*.xlsx"
    
    With cmldFlexGrid
        .CancelError = True
        .Filter = Pattern
        .InitDir = ""
        .ShowOpen
        If Not .FileName = "" Then
            Set xlObject = New Excel.Application
            Set xlWB = xlObject.Workbooks.Open(.FileName)

            Clipboard.Clear
            xlObject.Cells.Copy     ' Copy all cells in active worksheet.
            FetchNoRowCol xlObject.ActiveWorkbook.Worksheets("Report"), NoOfRows, NoOfColumns
            With MSFlexGrid1
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               .Redraw = True      'Now draw
            End With
            xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
            xlWB.Close
            xlObject.Application.Quit
            Set xlWB = Nothing
            Set xlObject = Nothing
        End If
    End With
   
    Exit Sub

MyErrHandler:
    err.Clear
End Sub

[seenu_1st]

@seenu_1st, what do you mean MSFlexgrid fast fill in my signature?

it is a link in my signature area.

[johndmingione]

Regarding your fast fill, it seems like you're pulling specific information out of that Worksheet. How do I set it up to pull information off only the Worksheet, not specific columns? I know it has something to do with the SELECT part.

[seenu_1st]

just change this

.Open "Select * From [sample$]", Cn, adOpenStatic, adLockReadOnly, adCmdText

[johndmingione]

So there's a problem with my App.Path part. Because I get the error message that is attached at the bottom and it highlights the

.Open App.Path & ""

I changed it to open an XLS of my choice

.Open App.Path & "\Mitsubishi_CEL.xls"

However, I changed it to

.Open ("C:\Users\nv2288\Desktop\Honda_2_cel.xls")

and it worked fine. So I'm not sure what I'm doing wrong in regards to the App.Path code but my error message is attached below.

[seenu_1st]

the complete connection shuld be like this

With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open App.Path & "\sample.xls"
End With

[johndmingione]

This one is having me so confused haha.

I kept getting the File Path Error but eventually it just went away on it's own? I changed it back to

App.Path & "\Mitsubishi.xls"

And one time it didn't work, now it does.

Anyways I'm getting another error message, regarding Rows:

The picture of the error is attached.

The highlighted line is

.Row = 1

And that is contained in the

With RS

Section

Any ideas on what is causing this? If I change the Row to a different number like 0 for example it only shows 1 row. That's obviously not what I need haha

[seenu_1st]

what is RS?

[johndmingione]

I'm not really sure it's from your Fast Fill example. I copied that code you used and tweaked it to fit what I wanted out of it. I'm sure it stands for something like,
-Record Set
or
-Row Selection

I'm not really sure.

[seenu_1st]

in my example RS is recordset, if u follow that then .Row is wrong, since it is a part of MSFlexgrid.

[johndmingione]

What would I change to make it work?

[seenu_1st]

i dont knw what r the changes u made, better if posible attach them as a zip file.

[johndmingione]

I can't attach the entire project as a zip and send it. I can give you the full code of the Form however. But, looking at the code again I noticed you referenced Excel 8.0. I have Excel 14.0 Object Library on my VB6 so I changed it. Now it is telling me an error about my database that it doesn't recognize it. So I'm thinking the Columns and Rows I reference might be incorrect. The error message is attached, here is the entire code,

Option Explicit

Dim Cn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim strSql As String

Private Sub Form_Load()
    Set Cn = New ADODB.Connection
    Set RS = New ADODB.Recordset
    
With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 14.0"
    .Open App.Path & "\Mitsubishi_CEL.xls"
End With

With RS
    .Open "Select * From [REPORT$]", Cn, adOpenStatic, adLockReadOnly, adCmdText
    .MoveFirst
    
    With MSFlexGrid1
        .FixedCols = 0
        .FixedRows = 0
    
        .Rows = RS.RecordCount
        .Cols = RS.Fields.Count
        .Row = 0
        .Col = 0
        .RowSel = MSFlexGrid1.Rows - 1
        .ColSel = MSFlexGrid1.Cols - 1
        .Clip = RS.GetString(adClipString, -1, Chr(9), Chr(13), vbNullString)
        .Row = 1
    End With
    
    .Close
End With

End Sub

The highlighted code it points to

.Open App.Path & "\Mitsubishi_CEL.xls"

[seenu_1st]

r u sure it is .xls or .xlsx?

[johndmingione]

XLS. Positive.

[seenu_1st]

since i hav only excel 2003, i used extended property "Excel 8.0", may be any problem with that, if u hav that version set that reference and try, so that u cud find the issue.