Export VB6 MsFlexgrid to NotePad

[avyrex1926]

Hello,

Before, i had an excel spreadsheet that could export data to notepad and transferted data in a text file using VBA.

Now that i use VB6 and a MsFlexgrid to do the job, i want to transfert the vba Script to my VB6 project. That his to transfer the data from the MsFlexgrid to a text file using the same parameters.

 vFieldArray = Array(1, 1, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)

But i need help on that.

Could you help me please?

My VBA Script was:

Private Sub CommandButton2_Click()
 Const DELIMITER As String = "" 'Normally none
        Const PAD As String = " "   'or other character
        Dim vFieldArray As Variant
        Dim myRecord As Range
        Dim nFileNum As Long
        Dim i As Long
        Dim sOut As String
        
        'vFieldArray contains field lengths, in characters, from field 1 to N
        vFieldArray = Array(1, 1, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)
        nFileNum = FreeFile
        Open "C:\Documents and Settings\All Users\Desktop\ordtemp." For Output As #nFileNum
        For Each myRecord In Range("A8:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                For i = 0 To 41
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
                For i = 42 To UBound(vFieldArray)
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
            End With
        Next myRecord
        Close #nFileNum
        
       
        
        
 ' Open our source file
  f1 = FreeFile
  Open "C:\Documents and Settings\All Users\Desktop\ordtemp." For Input As #f1

  ' Open our target file
  f2 = FreeFile
  Open "C:\Documents and Settings\All Users\Desktop\ord." For Output As #f2

  ' Start processing till we reach the end
  Do While Not EOF(f1)
    ' Read a line from the source file
    Line Input #f1, x

    ' And if the line isn't empty, write it to the target file
    If x <> "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            " Then
      If x <> "                                                                                                                                                      " Then
      Print #f2, x
    End If
    End If
  Loop

  ' Close the two opened files
  Close #f2
  Close #f1
        
        'Delete the unecessary temp file
        strFile = "C:\Documents and Settings\All Users\Desktop\ordtemp."

        Set fs = CreateObject("Scripting.FileSystemObject")

        fs.deletefile strFile


        
         'This formats name of the text file to be ord.YYYYMMDDHHMI
        OldFile1 = "C:\Documents and Settings\All Users\Desktop\ord."
        newfile1 = Format(Now, "yyyymmddhhmmss")
        newfile1 = OldFile1 & newfile1
        Name OldFile1 As newfile1
        

        MsgBox "File Creation Complete"
End Sub

[Siddharth Rout]

I will take the same example that I gave you in the previous thread

Hope this helps...

Private Sub Form_Load()
   '~~> Declare variables.
   Dim Entry As String, i As Long
   
   MSFlexGrid1.FixedCols = 0
   MSFlexGrid1.FixedRows = 0
   
   MSFlexGrid1.Cols = 1
   '~~> To add 10 items
   For i = 1 To 10
      Entry = "This is item number : " & i
      MSFlexGrid1.AddItem Entry
   Next i
End Sub
 
Private Sub Command1_Click()
    Dim intRow As Integer, intCol As Integer
    
    '~~> Change to target File
    FileName = "C:\Test.Txt"
    
    iFreeFile = FreeFile
    
    '~~> Open File for output
    Open FileName For Output As iFreeFile
    
    '~~> Get the rows and Columns of Flex
    intCol = MSFlexGrid1.Cols
    intRow = MSFlexGrid1.Rows
 
    For i = 1 To intRow
        For j = 1 To intCol
            With MSFlexGrid1
                '~~> Write to text file
                Print #iFreeFile, .TextMatrix(i - 1, j - 1) & " "
            End With
        Next
    Next
 
    MsgBox "File Exported Successfully"
    Close #iFreeFile
End Sub

[avyrex1926]

I have an error on "FileName":

#
'~~> Change to target File
#
   FileName = "C:\Test.Txt"

I did created the file before.

[Siddharth Rout]

what is the code that you are using?
What is the error that you are getting?

if you are using Option Explicit, have you declared

Dim FileName as String

???

[avyrex1926]

I put that code:

Dim FileName As String

Now i have:

Compile error
Variable not defined

at this section of the script:

 For i = 1 To intRow

[Siddharth Rout]

I though you will dim your variables

okay do this....

Dim i as Long, j as long

[avyrex1926]

Thanks, I will try it Friday and I'll give you the results.

Thanks again.

[avyrex1926]

Hello again,

I did the modification and now theres a error on:

  '~~> Open File for output

    Open FileName For Output As iFreeFile

Run-Time error 75:
Path/File access error.

[Siddharth Rout]

Check if the file exists... If it exists, ensure that it is not "read only"

Try it with a different filename for example

FileName = "C:\avyrex1926.Txt"

If the above doesn't work then show me your complete code...

[avyrex1926]

Good

I've created a new text file and it transfer the data.

Now the only thing that I need to fix his to have all information on the same line.

I'll explain.

It have to fit these parameters.

vFieldArray = Array(1, 1, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)

So lets say that in the grid, column 1, you have the value: H
Column 2 the value: A.
Column 3: Text
Column 4: Test1.

The final result would be in notepad: HAText Test1

vFieldArray = Array(1, 1, 12, 6)

HAText Test1 :
H = use 1 space.
A = Use 1 space.
Text = Use 12 space (Text followed by space).
Test1 = Use 6 space (Test1 followed by 1 space) etc.

How can I do that.

Thanks for you precious help.

avyrex