Export grid to excel

Hello all,

I need to change a part of the code, but I'm not to good at this.

I want to have a text value for column A and B. How can i fix that part of the code please?

Thanks for your help.


With xlObject.ActiveWorkbook.ActiveSheet
        .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
        .Paste  'Paste clipboard contents
        .Range("A1:B65536").NumberFormat = "#"
    End With

Open in new window

LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wilder1626Author Commented:
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
0
Robberbaron (robr)Commented:
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")

Open in new window

0
aikimarkCommented:
>> .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"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

This would be a simpler and more reliable addressing reference to columns A and B
0
Wilder1626Author Commented:
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
0
aikimarkCommented:
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?
0
Wilder1626Author Commented:
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.
0
aikimarkCommented:
please explain the 'grid' in your question title.
0
Wilder1626Author Commented:
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
0
aikimarkCommented:
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.
0
Wilder1626Author Commented:
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


0
aikimarkCommented:
Do you also use the clipboard to transfer data back into the Excel worksheet?
0
Wilder1626Author Commented:
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

Open in new window

0
aikimarkCommented:
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.
0
Wilder1626Author Commented:
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
0
aikimarkCommented:
If you need me to help work out the code, then post a copy of the workbook.
0
Wilder1626Author Commented:
Your are talking about the VB6 project?
0
aikimarkCommented:
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.
0
Wilder1626Author Commented:
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?
0
aikimarkCommented:
The Excel worksheet cell formatting will affect how the data appears.
0
Wilder1626Author Commented:
Ok.

So i wont add any macro to the excel spreadsheet.

I think i'm gona leave it that way then.
0
aikimarkCommented:
I don't see any need for spreadsheet macros.
0
Wilder1626Author Commented:
event for cell formatting because it's a new spreadsheet every time that i export to excel.
0
aikimarkCommented:
You could do all of the cell formatting in your VB program, through the Excel automation object.
0
aikimarkCommented:
Since the data starts in Excel, it is one more reason to look at what the data looks like.
0
Wilder1626Author Commented:
this is how it look's from the start
test.xls
0
aikimarkCommented:
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?
0
Wilder1626Author Commented:
Thank you so much for your help.
0
aikimarkCommented:
I added a flexgrid and commondialog control to my form along with two command buttons for invoking the import and export code.

Notes:
* vXFER replaces the clipboard to transfer data
* I assume you allow the user to change the data in the flexgrid
* The size of the flexgrid is determined by the usedrange size in the Excel workbook, ignoring the textbox
* If you allow the user to increase or delete rows, you wll need to add code to ReDim vXFER and use the size of the flexgrid's .TextMatrix array for the lngRow and lngCol limits
* I'm closing the Excel automation objects as soon as I'm done with them
* avoiding the .Clip property allows me to ignore the carriage return problem

Option Explicit

Dim vXFER As Variant

Private Sub cmdExport_Click()
    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)
            For lngCol = LBound(vXFER, 2) To UBound(vXFER, 2)
                vXFER(lngRow, lngCol) = .TextMatrix(lngRow, lngCol)
            Next
        Next
    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"
        .Range(Cells(LBound(vXFER, 1), LBound(vXFER, 2)), Cells(UBound(vXFER, 1), UBound(vXFER, 2))).Value = vXFER
    End With
    
    ' This makes Excel visible
    xlObject.Visible = True
End Sub

Private Sub cmdImport_Click()
    Dim xlObject    As Excel.Application
    Dim xlWB        As Excel.Workbook
    Dim lngRow As Long
    Dim lngCol As Long
    
    '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"
         '.InitDir = "%My Documents%"
         .ShowOpen
        If .filename = "" Then
            Exit Sub
        Else
            Set xlObject = New Excel.Application
            Set xlWB = xlObject.Workbooks.Open(.filename, , True)
        End If
    End With
    
    'Clipboard.Clear
    
    With xlWB.ActiveSheet
        '.Range("A1:Bj10000").Copy 'Set selection to Copy
        vXFER = .UsedRange.Value
    End With
    
    'Close Excel
    xlWB.Close
    xlObject.Application.Quit
    Set xlWB = Nothing
    Set xlObject = Nothing
    
    MSFlexGrid2.Rows = UBound(vXFER, 1) + 1 'Val(txtNumberOfData2.Text) + 1
    MSFlexGrid2.Cols = UBound(vXFER, 2) + 1 '8
    
    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 = Clipboard.GetText  'Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
        '.Clip = vXFER
        For lngRow = LBound(vXFER, 1) To UBound(vXFER, 1)
            For lngCol = LBound(vXFER, 2) To UBound(vXFER, 2)
                .TextMatrix(lngRow, lngCol) = vXFER(lngRow, lngCol)
            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

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
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?
0
aikimarkCommented:
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?
0
Wilder1626Author Commented:
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
0
aikimarkCommented:
have you selected the same workbook as the one you posted earlier?
0
Wilder1626Author Commented:
yes i did.
0
aikimarkCommented:
what are the fixedrows and fixedcolumns property values?
0
Wilder1626Author Commented:
In property, general tab,

Fixed Rows: 1
Fixed Cols: 1

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

Open in new window

0
Wilder1626Author Commented:
sorry, but how do i set a breakpoint on the xlWB.Close statement?
0
aikimarkCommented:
place the cursor on the line and press F9 key

or

click in the grey left margin on that line
0
Wilder1626Author Commented:
ok, like this?
untitled.bmp
0
aikimarkCommented:
yes.  you will see [break] on the title bar
0
Wilder1626Author Commented:
My VB6 is in french, but i see it.

Is it ok where i have entered the code?

See picture above.
0
aikimarkCommented:
Press Ctrl+G

Type the two commands in the immediate window.  Post the results in this thread.
0
Wilder1626Author Commented:
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
0
Wilder1626Author Commented:
i have this also
Classeur12
0
aikimarkCommented:
please double check the two commands I asked you to type against what you actually typed.
0
Wilder1626Author Commented:

?xlWB.UsedRange.Address

?xlWB.name
Classeur14
0
aikimarkCommented:
Is there any data in Classeur14?
0
Wilder1626Author Commented:
it doesn't even open
0
aikimarkCommented:
if you are still in break mode, you need to let the event complete, freeing up the Excel automation objects.
0
Wilder1626Author Commented:
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)
0
aikimarkCommented:
are you in design mode, run mode, or break mode?
0
Wilder1626Author Commented:
This is also a new question for me. I should be in design mode
0
aikimarkCommented:
what is on the VB title bar?
0
Wilder1626Author Commented:
Design mode


bar.JPG
0
aikimarkCommented:
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.
0
Wilder1626Author Commented:
Ok, i will reboot now.

I compiled the project and when i run it, it say Run-Time error 13, Type mismatch.
0
aikimarkCommented:
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?
0
Wilder1626Author Commented:
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.
0
aikimarkCommented:
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.
0
Wilder1626Author Commented:
i will do some test and i will let you know the result.

Thanks again for all the time
0
Wilder1626Author Commented:
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
0
aikimarkCommented:
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)

Open in new window

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

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

What is too complicated?
0
Wilder1626Author Commented:
Still having thesame result:

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

?UBound(vXFER, 1), UBound(vXFER, 2)
 6             9
0
aikimarkCommented:
for the time being, replace the Lbound() expressions with 1

the lower bounds of both dimensions should be 1
0
Wilder1626Author Commented:
Thanks for your time.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.