-
Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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.
-
1 Attachment(s)
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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:
Code:
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.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
did you supply full path to file?
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
Um...I don't exactly what you mean. Is there any chance you can explain it further?
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
Hi all,
This is the code i'm using to open excel file in my MSFlexgrid:
Code:
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.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
I'll try it out and let you know.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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
And it fires off the error message
Quote:
Compile Error:
Sub or function not defined
And it highlights the word above in blue
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
Oh sorry forgot to give you this first part.
Code:
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:
Code:
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
-
1 Attachment(s)
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
I get the error message that is displayed in an attachment below.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
-
1 Attachment(s)
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
Here is an example for you.
This one work.
Let me know if you can upload into your grid.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
After, if you want to auto fit all columns,
You need to add this part:
Code:
'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:
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
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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?
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
@john, u can try the MSFlexgrid fast fill in my signature.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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?
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
@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
Code:
FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheets, NoOfRows, NoOfColumns
To
Code:
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:
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
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
Quote:
@seenu_1st, what do you mean MSFlexgrid fast fill in my signature?
it is a link in my signature area.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
just change this
Code:
.Open "Select * From [sample$]", Cn, adOpenStatic, adLockReadOnly, adCmdText
-
1 Attachment(s)
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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
Quote:
.Open App.Path & ""
I changed it to open an XLS of my choice
Code:
.Open App.Path & "\Mitsubishi_CEL.xls"
However, I changed it to
Code:
.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.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
the complete connection shuld be like this
Code:
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open App.Path & "\sample.xls"
End With
-
1 Attachment(s)
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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
Code:
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
And that is contained in the
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 :)
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
in my example RS is recordset, if u follow that then .Row is wrong, since it is a part of MSFlexgrid.
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
What would I change to make it work?
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
i dont knw what r the changes u made, better if posible attach them as a zip file.
-
1 Attachment(s)
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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,
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
Code:
.Open App.Path & "\Mitsubishi_CEL.xls"
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
r u sure it is .xls or .xlsx?
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
-
Re: Opening an Excel Worksheet within a VSFlexGrid/MSFlexgrid
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.