• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

VB6 - Open csv file in MSHFlexgrid1 as text format

Hello all

I have this code bellow that upload a csv file in my MSHFlexgrid1.

The problem is that i would like to upload the file in in grid as a TEXT format.

The reason is that in my csv file, i may see text like 012345 but once in the grid, it convert the text to 12345 by removing the 0 in front.

How can i do that?

Thanks again for your help

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

Open in new window

0
Wilder1626
Asked:
Wilder1626
  • 12
  • 12
1 Solution
 
Martin LissRetired ProgrammerCommented:
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.
0
 
Martin LissRetired ProgrammerCommented:
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
0
 
Wilder1626Author Commented:
Hi MartinLiss

Did you saved the excel file to csv?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Martin LissRetired ProgrammerCommented:
Sorry, no I didn't. I'll do that now.
0
 
Martin LissRetired ProgrammerCommented:
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

Open in new window

0
 
Wilder1626Author Commented:
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".
0
 
Martin LissRetired ProgrammerCommented:
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.
0
 
Wilder1626Author Commented:
Do you know if there is a way to convert the cvs into notepad before it upload in the grid?
0
 
Martin LissRetired ProgrammerCommented:
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

Open in new window

0
 
Wilder1626Author Commented:
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

Open 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

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
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

Open in new window

BTW I'm working on code that doesn't use Excel at all.
0
 
Martin LissRetired ProgrammerCommented:
oops
0
 
Wilder1626Author Commented:
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
0
 
Martin LissRetired ProgrammerCommented:
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

Open in new window

0
 
Wilder1626Author Commented:
I have this runtime error: Run-time Error 52 Bad filename or number on that part of the code:
Line Input #FF, strLine

Open 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

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
Try my non-Excel code. If that doesn't work can you attach the csv file?
0
 
Wilder1626Author Commented:
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

Open in new window

test-csv.csv
0
 
Wilder1626Author Commented:
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"
0
 
Martin LissRetired ProgrammerCommented:
This works for the two line file you attached. It could have problems if the file is actually longer. If it is longer and there are problems, please post a bigger sample. BTW it would be easier for me if you put the csv file inside a zip file if you can.

    Dim lngRow As Long
    Dim lngCol As Long
    Dim strLine As String
    Dim strParts() As String
    Dim FF As Integer
    Dim strCSV() As String
    Dim lngCSV As Long
    
    FF = FreeFile
    Open "C:\temp\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
            strCSV = Split(strLine, Chr(10))
            For lngCSV = 0 To UBound(strCSV)
                strParts = Split(strCSV(lngCSV), ",")
                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)
                    ' If you want to remove the quotes do this instead of the above line
                    'MSHFlexGrid1.TextMatrix(lngRow, lngCol) = Replace(strParts(lngCol), """", "")
                Next
                lngRow = lngRow + 1
            Next
        Loop
        Close #FF
       .Col = 1            'Just to remove that blue selection from Flexgrid
       .Redraw = True      'Now draw
    End With

Open in new window

0
 
Wilder1626Author Commented:
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)

Open in new window


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

Thanks again for your help.
csv-test.zip
0
 
Wilder1626Author Commented:
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.
0
 
Wilder1626Author Commented:
Thanks again for all your help.

This will be very good to have as a tool for me.
0
 
Martin LissRetired ProgrammerCommented:
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

Open 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
0
 
Wilder1626Author Commented:
Many thanks again

all good now
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now