Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.
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 With CommonDialog1 .CancelError = True .Filter = "Microsoft Excel files (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 MSHFlexGrid1 .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 End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.