Export to Excel or creat a txt.file

[wilder1926]

Hello everybody,

In my small program, I'm exporting data from a MSFlexgrid to Excel.

Private Sub Command7_Click()

Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
     
    Set xlObject = New Excel.Application

    'This Adds a new woorkbook, you could open the workbook from file also
    Set xlWB = xlObject.Workbooks.Add
               
    Clipboard.Clear 'Clear the Clipboard
    With MSFlexGrid1
        'Select Full Contents (You could also select partial content)
        .Col = 0               'From first column
        .Row = 0               'From first Row (header)
        .ColSel = .Cols - 1    'Select all columns
        .RowSel = .Rows - 1    'Select all rows
        Clipboard.SetText .Clip 'Send to Clipboard
    End With
           
    With xlObject.ActiveWorkbook.ActiveSheet
        .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
        .Paste             'Paste clipboard contents
    End With
   
    ' This makes Excel visible
    xlObject.Visible = True
     MsgBox "File Creation Complete"
End Sub

But in column J - K - L and M, instead of exporting the number in the real format like that for example: "200908250700" it writes: 2.01E+11.

What can I do to prevent that?

Thanks for your help.

[RhinoBull]

... instead of exporting the number in the real format like that for example: "200908250700" it writes: 2.01E+11...

That's hardly an export issue - rather formatting.
After you copy data to excel you may format entire column using [if I am not mistaken] FormatNumber property:

xlObject.ActiveWorkbook.ActiveSheet.Columns("A").FormatNumber = "whatever" <<< check actual value directly in Excel.

[wilder1926]

Can I export that in a txt file instead of excel?

[Siddharth Rout]

Yes you can. But for a simple solution why do you want to recode????

Try this...

Range("A1").Value = "'" & Range("A1").Value

The above is a very basic example of how to change the value of the cell A1. Please amend it as per your requirement.

Basically I am storing 200908250700 as '200908250700 in the cell. The ' will store the number as string...

[si_the_geek]

You can indeed, but note that it wont be easy to read (as there is no formatting, so how it is displayed is up to the program that shows it, including whatever font options the user has selected).

For information on how to write to a text file, see the "Files" section of our Classic VB FAQs (in the FAQ forum)

In this particular case the data you want to write is .Clip

[wilder1926]

I did something in VBA about the same thing. It transfert an excel sheet in a txt file. That text file, i can upload the data into another program in a specific format.

I would like to do the same thing with VB6, starting from the MSFlexgrid. By doing this, i have one step less to do.

Here is my vba code:

 'vFieldArray contains field lengths, in characters, from field 1 to BH
        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

[RhinoBull]

Basically I am storing 200908250700 as '200908250700 in the cell. The ' will store the number as string...

You really don't want to do that - Excel may not be be able to perform calculations.
We had this issue not long ago when someone did just that same mistake and it took some time to troubleshoot it.

[si_the_geek]

I did something in VBA about the same thing. It transfert an excel sheet in a txt file. That text file, i can upload the data into another program in a specific format.

I would like to do the same thing with VB6, starting from the MSFlexgrid. By doing this, i have one step less to do.

Here is my vba code:

That would be a good idea, and you can use almost exactly the same code.

You just need to change the For/Next loop to use row numbers (.FixedRows To .Rows-1 ) instead of a Range, remove the With/EndWith, and change .Offset(0, i).Text to use MSFlexGrid1.TextMatrix with apt parameters.

[Siddharth Rout]

You really don't want to do that - Excel may not be be able to perform calculations.
We had this issue not long ago when someone did just that same mistake and it took some time to troubleshoot it.

It's not the case Rhino. You can still perform calculations...

Here is a sample sheet with a very basic calculation...

[RhinoBull]

It is the case - when your spreadsheet has tons of numbers (especially currency) formatted as text Excel will malfunction.
Trust me.

[RhinoBull]

... and btw, your basic spreadsheet already shows warning message:

"The number in this cell is formatted as text or preceded by an apostrophe"

and that is a huge problem for any accountant or anyone who deals with numbers in Excel.

[wilder1926]

You just need to change the For/Next loop to use row numbers (.FixedRows To .Rows-1 ) instead of a Range, remove the With/EndWith, and change .Offset(0, i).Text to use MSFlexGrid1.TextMatrix with apt parameters.

Ok, I will try to understand all this. Since I'm new to VB6, it's really not easy.

Thanks again for you help everybody.

[wilder1926]

You just need to change the For/Next loop to use row numbers (.FixedRows To .Rows-1 ) instead of a Range, remove the With/EndWith, and change .Offset(0, i).Text to use MSFlexGrid1.TextMatrix with apt parameters.

I'm reading, again an again, and I don’t understand that part.

Could you start me so I would understand more please?

I'm using MSDN, but still. That one is more that tuff.

Thanks again.

[si_the_geek]

Here's a start... Change this:

        For Each myRecord In Range("A8:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                For i = 0 To 41
                ...
                sOut = Empty
            End With
        Next myRecord

to this:

Dim lngRow as Long
        For lngRow = MSFlexGrid1.FixedRows to MSFlexGrid1.Rows - 1
                For i = 0 To 41
                ...
                sOut = Empty
        Next lngRow

You still have this part to do:

and change .Offset(0, i).Text to use MSFlexGrid1.TextMatrix with apt parameters.

Note that lngRow should be one of those parameters.

[wilder1926]

Thanks again

After a few hours of work and reading,
Here the update:

Dim MyFreeFile As Integer
Dim icol As Integer
Dim irow As Integer
Dim MyString As String
MyFreeFile = FreeFile
Open "C:\Documents and Settings\All Users\Desktop\ord" For Output As MyFreeFile
For irow = 1 To MSFlexGrid1.Rows - 1    'Start from top to bottom
    For icol = 0 To MSFlexGrid1.Cols - 1    'Start from left to Right
        MyString = MyString & MSFlexGrid1.TextMatrix(irow, icol) & _
            IIf((icol = MSFlexGrid1.Cols - 1), "", ",")     'Add value in mystring for each column in Flexgrid
    Next
    Print #MyFreeFile, MyString  'Print to notepad
    MyString = ""   'Reset MyString
Next

Close MyFreeFile

MsgBox "File Creation Complete"

I'm still trying to introduce that part instead of ",":

 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)

It his where all columns must start (Position) with the value.

[si_the_geek]

For irow = 1 To MSFlexGrid1.Rows - 1    'Start from top to bottom
    For icol = 0 To MSFlexGrid1.Cols - 1    'Start from left to Right

Rather than starting at specific row/column numbers, it is better to use FixedRows and FixedCols as I did - that way it takes into account the amount of header rows and columns that you have at the time (so if you ever change that, you don't need to change this code).


As for the code inside the loop, you wanted the same as you had before - so you should only have changed where the data is coming from (TextMatrix instead of Offset).

[wilder1926]

So if I understand good, the first part look like that:

Dim vFieldArray As Variant
       
         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
        Dim lngrow As Long






'vFieldArray contains field lengths, in characters, from field 1 to BH
        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 lngrow = MSFlexGrid1.FixedRows To MSFlexGrid1.Rows - 1
                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
        Next lngrow
        Close #nFileNum

Now i have to change this part:

  ' 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

Is that write?

[si_the_geek]

so you should only have changed where the data is coming from (TextMatrix instead of Offset).

                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))

[RhinoBull]

Rather than starting at specific row/column numbers, it is better to use FixedRows and FixedCols as I did...

That increases the risk of loosing a whole lot of data - not always we use fixed rows/cols to present headers, they often hold actual data.
So, in my opinion it is much safer to specify row/col index from which you want to start.

[si_the_geek]

If you use fixed rows/cols for data then I agree with using hard-coded numbers, but in other cases (which seems to be the vast majority) starting your loops at FixedRows and FixedCols makes more sense - because it means that if the amount of fixed rows/cols changes later (but still don't contain actual data), even if you don't change the code you wont lose (or 'gain') data.

I don't see how there is any increased risk, because you should know if the fixed rows/cols have data in them when you write the loops - and if that changes, the code would need to be checked (and probably changed) in either case.

[RhinoBull]

I don't get the idea of what you're saying - maybe you can refrase yourself in fewer words.

[si_the_geek]

If your fixed rows/cols contain data, using numbers is better - because it means you get all of the data.

If they don't (which seems to usually be the case), using FixedRows/FixedCols is better - because it means you get all of the data (and no headers), even if the amount of FixedRows/FixedCols changes at a later date.


So, if your fixed cols contain data but your fixed rows don't, using FixedRows and a column number is best.

Whichever way it is done, if the situation changes you should clearly be checking the code anyway, and changing it if needed.

[RhinoBull]

If your fixed rows/cols contain data, using numbers is better - because it means you get all of the data.

If they don't (which seems to usually be the case), using FixedRows/FixedCols is better...

In my world they most often do - headers could be anything from invoice_id to [say] week_of kind od data and you can't afford to loose any of it.
Which is why I said "it is safer to use row/col index"...
As you know people get into a habit reusing their code and if you use FixedCols/Rows once then... well, you the rest.
I always tried to avoid using those properties when exporting/transfering data unless it was absolutely necessary.

[si_the_geek]

I understand it in your situation, but that is not the same situation that is usually seen on the forums (which is why most experienced people recommend FixedRows/FixedCols).


When it comes to code re-use, it doesn't make a difference which of the methods you use - neither of them are correct for all situations.

If the re-use is copy+paste, you should obviously always be checking that the loops are apt anyway. When it comes to subs/functions/etc, it should either be made clear in the usage notes at the top of the routine(s), or have a parameter/property to specify.

For my generic flexgrid routines, I have optional parameters for starting row/col numbers, with the default being -1 (to indicate that FixedRows/FixedCols should be used).

[RhinoBull]

I understand it in your situation, but that is not the same situation that is usually seen on the forums (which is why most experienced people recommend FixedRows/FixedCols)...

No intension to argue any further (pointless) so I’m just curios – who are those people?

[si_the_geek]

No intension to argue any further (pointless)

Indeed, because there is no 'right' answer.. it depends on the situation.

so I’m just curios – who are those people?

It is hard to find examples, because usage of it in code rarely shows up in a search (presumably due to the . confusing our search engine), but I clearly remember two people and have managed to find examples for them: brucevde and jcis

[wilder1926]

You are loosing me guys.

Now, I don’t understand anymore.

[RhinoBull]

Lol, which part don't you?

[wilder1926]

Everything.

I just wanted to add that function to a program that was made. IT's not that I'm working in VB6 all the time.

It's to hard to make just for a one time job.

Thanks

[seenu_1st]

Hello everybody,

In my small program, I'm exporting data from a MSFlexgrid to Excel.

Private Sub Command7_Click()

Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
     
    Set xlObject = New Excel.Application

    'This Adds a new woorkbook, you could open the workbook from file also
    Set xlWB = xlObject.Workbooks.Add
               
    Clipboard.Clear 'Clear the Clipboard
    With MSFlexGrid1
        'Select Full Contents (You could also select partial content)
        .Col = 0               'From first column
        .Row = 0               'From first Row (header)
        .ColSel = .Cols - 1    'Select all columns
        .RowSel = .Rows - 1    'Select all rows
        Clipboard.SetText .Clip 'Send to Clipboard
    End With
           
    With xlObject.ActiveWorkbook.ActiveSheet
        .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
        .Paste             'Paste clipboard contents
    End With
   
    ' This makes Excel visible
    xlObject.Visible = True
     MsgBox "File Creation Complete"
End Sub

But in column J - K - L and M, instead of exporting the number in the real format like that for example: "200908250700" it writes: 2.01E+11.

What can I do to prevent that?

Thanks for your help.

if the purpose of these numbers not to be calculated and only for refrence or printing purpose then u can use single quote in front of the numbers like '1236475775848477, and set those columns autofit then it wil be considered as text and u can view them fully.

[wilder1926]

Thanks for your code for excel.

At this point, I was more focusing on the txt file.

But I will keep the code just in case.

Thanks seenu_1st

[wilder1926]

Hello again,

I will show you a few image of my MSFlexgrid, and also 2 text files that I did manually of what it's suppose to look like at the end.

Maybe it will help.

Thanks again.