Solved

VB6 - Open csv file in MSHFlexgrid1 as text format

Posted on 2013-01-01
24
643 Views
Last Modified: 2013-01-02
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
Comment
Question by:Wilder1626
  • 12
  • 12
24 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734683
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734700
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38734709
Hi MartinLiss

Did you saved the excel file to csv?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734715
Sorry, no I didn't. I'll do that now.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734755
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38734769
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734786
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38734791
Do you know if there is a way to convert the cvs into notepad before it upload in the grid?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734823
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38734841
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734850
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734853
oops
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 11

Author Comment

by:Wilder1626
ID: 38734868
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734879
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38734882
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38734886
Try my non-Excel code. If that doesn't work can you attach the csv file?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38734896
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38734961
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
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38735003
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38735057
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38735083
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
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38735100
Thanks again for all your help.

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

Expert Comment

by:Martin Liss
ID: 38735194
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38736274
Many thanks again

all good now
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now