I have an issue to upload excel data in my MSHFlexgrid3 using the code bellow.
In my form, i have a Text3 that have the link of the excel file, i also have a Combo3 that have all the sheets names from that excel file.
Then, when i click on upload, i don't see any data in the grid, just empty cells.
how can i fix this issue?
Thanks
General Declaration:
Option ExplicitPrivate Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As LongPrivate Type OPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long lpstrFilter As String lpstrCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long lpstrFile As String nMaxFile As Long lpstrFileTitle As String nMaxFileTitle As Long lpstrInitialDir As String lpstrTitle As String flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As StringEnd Type
Form8.Show (False) Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 2) 'On Error GoTo step_error Dim XLS As New Excel.Application Dim WRK As Excel.Workbook Dim SHT As Excel.Worksheet Dim RNG As Excel.Range Dim ArrayCells() As Variant If Combo3.ListIndex <> -1 Then 'Create a new instance of Excel Set XLS = CreateObject("Excel.Application") 'Open the XLS file. The two parameters representes, UpdateLink = False and ReadOnly = True. These parameters have this setting to dont occur any error on broken links and allready opened XLS file. Set WRK = XLS.Workbooks.Open(Text3.Text, False, True) 'Set the SHT variable to selected worksheet Set SHT = WRK.Worksheets(Combo3.List(Combo3.ListIndex)) 'Get the used range of current worksheet Set RNG = SHT.UsedRange 'Change the dimensions of array to fit the used range of worksheet ReDim ArrayCells(1 To RNG.Rows.Count, 1 To RNG.Columns.Count) 'Close worksheet WRK.Close False 'Quit the MS Excel XLS.Quit 'Release variables Set XLS = Nothing Set WRK = Nothing Set SHT = Nothing Set RNG = Nothing 'Configure the flexgrid to display data MSHFlexGrid3.Redraw = False MSHFlexGrid3.FixedCols = 0 MSHFlexGrid3.FixedRows = 1 MSHFlexGrid3.Rows = UBound(ArrayCells, 1) MSHFlexGrid3.Cols = UBound(ArrayCells, 2) Dim r As Integer Dim c As Integer For r = 0 To UBound(ArrayCells, 1) - 1 For c = 0 To UBound(ArrayCells, 2) - 1 MSHFlexGrid3.TextMatrix(r, c) = CStr(ArrayCells(r + 1, c + 1)) Next Next MSHFlexGrid3.Redraw = True Else MsgBox "Select the worksheet!", vbCritical Combo3.SetFocus End If 'Auto column fit Dim cell_wid As Single Dim col_wid As Single For c = 0 To MSHFlexGrid3.Cols - 1 col_wid = 0 For r = 0 To MSHFlexGrid3.Rows - 1 cell_wid = TextWidth(MSHFlexGrid3.TextMatrix(r, c)) If col_wid < cell_wid Then col_wid = cell_wid Next r MSHFlexGrid3.ColWidth(c) = col_wid + 120 Next c 'Count records Dim z As Long, txt As String, total1 As Long For z = 1 To MSHFlexGrid3.Rows - 1 If Len(MSHFlexGrid3.TextMatrix(z, 3)) Then total1 = total1 + 1 Next z lblTotalrecord3 = CStr(total1) '---------- Align MSHFlexgrid ---------------- With MSHFlexGrid3 Dim k As Long For k = 0 To .Cols - 1 .ColAlignment(k) = flexAlignLeftCenter Next End With Unload Form8 Call MsgBox("File now uploaded in grid", vbInformation Or vbSystemModal Or vbMsgBoxRight Or vbMsgBoxRtlReading, "Excel file uploaded") Exit Substep_error: MsgBox Err.Number & " - " & Err.Description Resume Next
So true. Thanks again for your help. Now it work