Export grid to excel

I have a part of it done but still, i have an issue or detail that i can't fix.

This is the text part that i fix:
NumberFormat = "@"

But if , lets say that the text was "9999", i want a text value but at 5 digits.

So the text "9999" will come "09999".

Thanks again

see the Format function in VBA reference.

this converts a number (or other formats too) to a string , with an output format that can be controlled

dim text1 as string

text1 = Format(y, "00000")

Select allOpen in new window

>> .Paste  'Paste clipboard contents

If you can avoid using the clipboard, it would make your code a better 'playmate' with other Windows applications.

===============
What does your question have to do with grid exporting?

===============
.Range("A1:B65536").NumberFormat = "00000"

.Range("A:B").NumberFormat = "00000"

This would be a simpler and more reliable addressing reference to columns A and B

Hello all, and thanks for your help.

.Range("A:B").NumberFormat = "00000" = Numeric value and not text.

If i have for example in my grid the value "9999",  and now, i have to export it to excel, if i put a numeric value, it will go in the right part of the cell "09999".

But if it's a text value, all text value are going at the left part of the cell.


9999.JPG

since 09999 are both numeric, what differentiates the two values you are bringing into Excel?  In other words, why is Excel interpreting column C as text and not numeric?

The excel spreadsheet is then import into a software. Column A and B needs to be in Text. If it's numeric, right part of the cell, there's an error on the import. Not when in Text format.

please explain the 'grid' in your question title.

I import into the grid an excel spreadsheet, spreadsheet of an export from a software.

In column B, i have text value already ex: 09999.

But if, after modification, i export back into the excel spreadsheet, it does not stay with a text value.

If i put a text format, it remove the 0 at the front, and if i put the format "00000", it turn out to be a numeric value, not a text value.


untitled.JPG

What kind of 'grid' are you importing into?

Inside what kind of application does this 'grid' exist?

How are you transferring data between Excel and your 'grid'?

============
Please note that formatting a cell in Excel does not change the contents of the cell, only how those contents are displayed.

This is a VB6 MSFlexgrid.

I importe with that code:

MSFlexGrid2.Rows = txtNumberOfData2.Text + 1
MSFlexGrid2.Cols = 8

Dim xlObject    As Excel.Application
Dim xlWB        As Excel.Workbook
On Error GoTo MyErrHandler

    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (xlam, xlsx, xltm, xlt, xlsm, xltx, xls, txt, csv)"
        '.InitDir = App.Path
        .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
 
   With xlObject.ActiveWorkbook.ActiveSheet
       .Range("A1:Bj10000").Copy 'Set selection to Copy
   End With
   With MSFlexGrid2
       .Redraw = False     'Dont draw until the end, so we avoid that flash
       .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
   'Close Excel
   xlWB.Close
   xlObject.Application.Quit
   Set xlWB = Nothing
    Set xlObject = Nothing
   
     End If
    End With
   
    Exit Sub



At the start, in excel, the Text is for example "09999". After in my grid, i see"09999".

But the problem is when i want to go back in excel with all data in my MSFlexgrid.
   


MyErrHandler:
    Err.Clear
   
    End If

Do you also use the clipboard to transfer data back into the Excel worksheet?

To transfer back into excel, here is the code:

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 MSFlexGrid3
        '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
        .Range("A:A").NumberFormat = "@"
        .Range("B:B").NumberFormat = "00000"

    End With
   
    ' This makes Excel visible
    xlObject.Visible = True

Select allOpen in new window

Have you tried a direct data transfer to Excel?

Take a look at my article on pushing data into Excel directly from an array:
http:/A_2253.html

In your case, you might need to use a Variant array to hold your data.

It looks like you have the requisite VB skills to try this, but I'm here to help you.

Ok, i'm not to pro in VB since lots of person help me to do it.

I will try to start understanding the link that you gave me and i will let you know after.

Thanks

If you need me to help work out the code, then post a copy of the workbook.

Your are talking about the VB6 project?

I think I can incorporate the code you've posted into a VB project.  I asked for the Excel workbook, since that would be the most difficult to replicate.

Well the code that i gave you is the code from a VB6 project.

When i export in the excel spreadsheet, it start with a new spreadsheet.

So there's no vba.

It need's to be fix from my VB6 project only.

Is that possible you think?

The Excel worksheet cell formatting will affect how the data appears.

Ok.

So i wont add any macro to the excel spreadsheet.

I think i'm gona leave it that way then.

I don't see any need for spreadsheet macros.

event for cell formatting because it's a new spreadsheet every time that i export to excel.

You could do all of the cell formatting in your VB program, through the Excel automation object.

Since the data starts in Excel, it is one more reason to look at what the data looks like.

this is how it look's from the start
test.xls

thanks.  I'll try to recreate your environment.

I already noticed that your data has an apostrophe prefix, which forces the data into a text format.  Is it possible that something is happening with the grid editing that removes the apostrophe?

Thank you so much for your help.

I've just tried it and i have an issue with this part:

For lngRow = LBound(vXFER, 1) To UBound(vXFER, 1)

It sais type incompatible, error 13.

Do you know what it is?

1. Do you have Option Explicit in the General Declarations section?
2. Is the vXFER variable defined in the General Declarations section?
3. What event is this?

Yes for the option explicit and also for the vXFER.

For this part: For lngRow = LBound(vXFER, 1) To UBound(vXFER, 1)



 'Clipboard.Clear 'Clear the Clipboard
    With MSFlexGrid2
        '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
        For lngRow = LBound(vXFER, 1) To UBound(vXFER, 1)
            For lngCol = LBound(vXFER, 2) To UBound(vXFER, 2)
                vXFER(lngRow, lngCol) = .TextMatrix(lngRow, lngCol)
            Next
        Next
    End With

have you selected the same workbook as the one you posted earlier?

yes i did.

what are the fixedrows and fixedcolumns property values?

In property, general tab,

Fixed Rows: 1
Fixed Cols: 1

Since I didn't have your error handling code, I commented the On Error statement.  That is the only thing I can think of at this point since the Import routine code works on my system.

When the error occurs in the For lngRows statement, please take a look at the variables and let me know what are their values.

==========
One additional note:
* your original export routine referenced a different msflexgrid than your import routine.  I reference the same grid control for both routines.

Ok

For that part of the code: For lngRow = LBound(vXFER, 1) To UBound(vXFER, 1)


lngRow = 0

LBound = Type incompatible
vXFER = Empty

UBound = Type incompatible
vXFER = Empty

set a breakpoint on the xlWB.Close statement
do another import
when the breakpoint is hit, do the following in the immediate window

?xlWB.UsedRange.Address

?xlWB.name

Select allOpen in new window

sorry, but how do i set a breakpoint on the xlWB.Close statement?

place the cursor on the line and press F9 key

or

click in the grey left margin on that line

ok, like this?
untitled.bmp

yes.  you will see [break] on the title bar

My VB6 is in french, but i see it.

Is it ok where i have entered the code?

See picture above.

Press Ctrl+G

Type the two commands in the immediate window.  Post the results in this thread.

oh, i see.

Here is the result:

99  03224                       20002    
99  03225                       20002    
99  03228                       20002    
99  03229                       20002    
99  03230                       20002    
99  03265                       20002

i have this also
Classeur12

please double check the two commands I asked you to type against what you actually typed.

?xlWB.UsedRange.Address

?xlWB.name
Classeur14

Is there any data in Classeur14?

it doesn't even open

if you are still in break mode, you need to let the event complete, freeing up the Excel automation objects.

It does not want to open.

It stoping at the end of this code:
Dim xlObject    As Excel.Application
    Dim xlWB        As Excel.Workbook
    Dim lngRow As Long
    Dim lngCol As Long
     
    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 MSFlexGrid2
        '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
       

        For lngRow = LBound(vXFER, 1) To UBound(vXFER, 1)

are you in design mode, run mode, or break mode?

This is also a new question for me. I should be in design mode

what is on the VB title bar?

Design mode


bar.JPG

does the error occur with any other workbook?

something may be hanging as a result of our testing.  after a reboot, see if you can open the workbook directly with Excel (without error) and then with the code.

Ok, i will reboot now.

I compiled the project and when i run it, it say Run-Time error 13, Type mismatch.

where do you stand with this problem?

have you rebooted your PC?

can you open the workbook with Excel?

is there anything in the workbook?

I have reboot my pc, the excel spreadsheet still does not open so nothing in it also.

I realy dont know what what it is.

Then that is a condition that will need to be trapped in the code.  Currently, the code expects that the workbooks are not broken and not empty.

Continue your testing with good data.

i will do some test and i will let you know the result.

Thanks again for all the time

ok, now, the error is not at the same place, more at the end of the code.

 .Range(Cells(LBound(vXFER, 1), LBound(vXFER, 2)), Cells(UBound(vXFER, 1), UBound(vXFER, 2))).Value = vXFER

I have this error

Run-time error '1004': Application-defined or object-defined error

1. set a breakpoint on this line
With xlObject.ActiveWorkbook.ActiveSheet

2. in the immediate window (when in break mode), issue the following an post the results

============
so now you are executing the export code.  does that mean that the import code is now working?

?LBound(vXFER, 1), LBound(vXFER, 2)

?UBound(vXFER, 1), UBound(vXFER, 2)

Select allOpen in new window

Now, the import is good.

For the other part, here is the result:

?LBound(vXFER, 1), LBound(vXFER, 2)

?UBound(vXFER, 1), UBound(vXFER, 2)
 6             9

Did you get that result after you pressed the Enter key on both lines?

If so, WOW!
If so, do you have all the service patches applied to your VB6 product?

Yes, I get these result after click OK to these 2 keys.

About the service patches, where do I have to look to give you the information?

What does " 6             9 " refers to?

>>...after click OK to these 2 keys
I don't think there should be any OK or mouse clicking involved

Place the cursor on each (?) line in the Immediate window and press the Enter key.

=================
>>"6             9"
These are the upper bounds of the vXFER array dimensions.  I expected to see your vXFER size as six rows and nine columns.  On my system, the lower bounds for both of these dimensions is 1.

=================
Service patch information is displayed on the Help About form.

ok, it look's like i will never get over that one, to complicated for me. I really don't understand what is the issue.

So, you pressed the Enter key on both lines in the Immediate Window?

What is too complicated?

Still having thesame result:

?LBound(vXFER, 1), LBound(vXFER, 2)

?UBound(vXFER, 1), UBound(vXFER, 2)
 6             9

for the time being, replace the Lbound() expressions with 1

the lower bounds of both dimensions should be 1

Thanks for your time.