VB6 - Open csv file in MSHFlexgrid1 as text format

The workbook I used has column A formatted at Text and in cell A1 is the value "01234". When I ran your code it showed up in the grid as 01234 so I can't reproduce your problem.

I also changed the worksheet so that A1 was General, B1 Number with 2 decimals  and C1 Text and typed 01234 into each. When I ran your code all of them showed up in the grid looking exactly like they do in the spreadsheet, that is
1234
1234.00
01234

Hi MartinLiss

Did you saved the excel file to csv?

Sorry, no I didn't. I'll do that now.

When I convert the workbook that I described that contains the data in 3 different formats to a csv file, all the data is converted to 1234 so there's no way of telling from that data that you want them to look like 01234. However if you want that data to look like 01234 you can do this (ignore the fact that I hardcoded the name of the csv file). The code as written converts all the cells, but you can change it to do certain rows and/or columns and/or cells.

Private Sub Command1_Click()
Dim xlObject     As excel.Application
Dim xlWB         As excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long
Dim lngRow As Long
Dim lngCol 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("C:\temp\book1.csv")
            

            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
               
                For lngRow = 1 To NoOfRows
                    For lngCol = 1 To NoOfColumns
                        MSHFlexGrid1.TextMatrix(lngRow - 1, lngCol - 1) = Format(xlObject.ActiveWorkbook.ActiveSheet.Cells(lngRow, lngCol), "00000")
                    Next
                Next

               .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

Select allOpen in new window

The thing is that if we open the csv file with notepad, you will see the good format.

Ex: "012345",

I can do the converting in excel directly but it takes about 5 steps to convert all column using the delimiter and also every column as text instead of standard.

Now the issue i would get with the hardcoded code is that i may also see value like "03" or "00003".

The thing is that if we open the csv file with notepad, you will see the good format.

You're right. Let me see what I can do.

Do you know if there is a way to convert the cvs into notepad before it upload in the grid?

Okay, try this. (Don't forget the 'Close' at the end)

Private Sub Command1_Click()
Dim xlObject     As excel.Application
Dim xlWB         As excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long
Dim lngRow As Long
Dim lngCol As Long
Dim strLine As String
Dim strParts() As String
Dim FF As Integer

    FF = FreeFile
    Open "C:\temp\book1.csv" For Input As #FF
   
'    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("C:\temp\book1.csv")
            

            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
                For lngRow = 1 To NoOfRows
                    Line Input #FF, strLine
                    strParts = Split(strLine, ",")

                    For lngCol = 0 To NoOfColumns - 1
                        MSHFlexGrid1.TextMatrix(lngRow - 1, lngCol) = strParts(lngCol)
                    Next
                Next
                Close #FF
               .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
Close

End Sub

Select allOpen in new window

I just tried but i have this error: RUN TIME ERROR 62 : Input Past End of File

On debug, it select this part of the code:

Line Input #FF, strLine

Select allOpen in new window

Full code:

Dim xlObject     As Excel.Application
Dim xlWB         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long
Dim lngRow As Long
Dim lngCol As Long
Dim strLine As String
Dim strParts() As String
Dim FF As Integer

    FF = FreeFile
    Open "C:\Documents and Settings\jpoitra\Desktop\testcsv.csv" For Input As #FF
   
'    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("C:\Documents and Settings\jpoitra\Desktop\testcsv.csv")
            

            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
                For lngRow = 1 To NoOfRows
                    Line Input #FF, strLine
                    strParts = Split(strLine, ",")

                    For lngCol = 0 To NoOfColumns - 1
                        MSHFlexGrid1.TextMatrix(lngRow - 1, lngCol) = strParts(lngCol)
                    Next
                Next
                Close #FF
               .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
Close


End Sub

Select allOpen in new window

I don't but change lines 38 to 45 to this and see if it helps.

                For lngRow = 0 To NoOfRows - 1
                    Line Input #FF, strLine
                    strParts = Split(strLine, ",")

                    For lngCol = 0 To NoOfColumns - 1
                        MSHFlexGrid1.TextMatrix(lngRow, lngCol) = strParts(lngCol)
                    Next
                Next

Select allOpen in new window

BTW I'm working on code that doesn't use Excel at all.

oops

BTW I'm working on code that doesn't use Excel at all.

I see!!! but you are still pretty good at it :-)

I think it work.

Let me try with a huge file but i will first re activate the CommonDialog1

Okay here's code that doesn't use Excel.

    Dim lngRow As Long
    Dim lngCol As Long
    Dim strLine As String
    Dim strParts() As String
    Dim FF As Integer
    
    FF = FreeFile
    Open "C:\temp\book1.csv" For Input As #FF
   
    With MSHFlexGrid1
       .Redraw = False     'Dont draw until the end, so we avoid that flash
        lngRow = 0
        Do While Not EOF(FF)
            Line Input #FF, strLine
            strParts = Split(strLine, ",")
            For lngCol = 0 To UBound(strParts)
                If .Cols < UBound(strParts) Then
                    .Cols = UBound(strParts) + 1
                End If
                If .Rows < lngRow + 1 Then
                    .Rows = .Rows + 1
                End If
                MSHFlexGrid1.TextMatrix(lngRow, lngCol) = strParts(lngCol)
            Next
            lngRow = lngRow + 1
        Loop
        Close #FF
       .Col = 1            'Just to remove that blue selection from Flexgrid
       .Redraw = True      'Now draw
    End With
    
    Close

Select allOpen in new window

I have this runtime error: Run-time Error 52 Bad filename or number on that part of the code:

Line Input #FF, strLine

Select allOpen in new window

strLine = ""

Full code

Dim xlObject     As Excel.Application
Dim xlWB         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long
Dim lngRow As Long
Dim lngCol As Long
Dim strLine As String
Dim strParts() As String
Dim FF As Integer

    FF = FreeFile
    'Open "C:\Documents and Settings\jpoitra\Desktop\testcsv.csv" For Input As #FF
   
    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("C:\Documents and Settings\jpoitra\Desktop\testcsv.csv")
            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
                For lngRow = 1 To NoOfRows
                    Line Input #FF, strLine
                    strParts = Split(strLine, ",")

                    For lngCol = 0 To NoOfColumns - 1
                        MSHFlexGrid1.TextMatrix(lngRow - 1, lngCol) = strParts(lngCol)
                    Next
                Next
                Close #FF
               .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
Close

Select allOpen in new window

Try my non-Excel code. If that doesn't work can you attach the csv file?

I see that it is very quick but it paste all on the same row.

here is an example of the file i need to upload in the grid.

Dim lngRow As Long
    Dim lngCol As Long
    Dim strLine As String
    Dim strParts() As String
    Dim FF As Integer
    
    FF = FreeFile
    Open "C:\Documents and Settings\xxx\Desktop\test_csv.csv" For Input As #FF
   
    With MSHFlexGrid1
       .Redraw = False     'Dont draw until the end, so we avoid that flash
        lngRow = 0
        Do While Not EOF(FF)
            Line Input #FF, strLine
            strParts = Split(strLine, ",")
            For lngCol = 0 To UBound(strParts)
                If .Cols < UBound(strParts) Then
                    .Cols = UBound(strParts) + 1
                End If
                If .Rows < lngRow + 1 Then
                    .Rows = .Rows + 1
                End If
                MSHFlexGrid1.TextMatrix(lngRow, lngCol) = strParts(lngCol)
            Next
            lngRow = lngRow + 1
        Loop
        Close #FF
       .Col = 1            'Just to remove that blue selection from Flexgrid
       .Redraw = True      'Now draw
    End With
    
    Close

Select allOpen in new window

test-csv.csv

from what i see, the first row is separated by ,

But starting at row 2, all text have the " in front and at the end and then you see the ,

Ex:

Row  1 = TEST1, TEST2

Row 2 = "TEST1", "TEST2"

Hi again

yes are right, it work for the 2 records. But for a longer file, i'm getting another error VB6 Runtime error 381 subsript out of range

MSHFlexGrid1.TextMatrix(lngRow, lngCol) = strParts(lngCol)

Select allOpen in new window

Here is a larger file. Less column but still same process.

Thanks again for your help.
csv-test.zip

hummm

if i put On Error Resume Next, it do work pretty good.

Just don't know when would be the issue at one point.

Thanks again for all your help.

This will be very good to have as a tool for me.

Here's a correction. Change lines 24 to 26 above to

                    If .Rows < lngRow + 1 Then
                        Do Until .Rows = lngRow + 1
                            .Rows = .Rows + 1
                        Loop
                    End If

Select allOpen in new window

You're welcome and I'm glad I was able to help.

My profile contains links to some articles I've written that may interest you.
Marty - MVP 2009 to 2012

Many thanks again

all good now