More problems retrieving text from Excel (Excel/VBA)

Topic

New Reply

Hi guys

My next problem is trying to copy to the clipboard the used range inside a sheet.
I can do this with the following:

oSheet.UsedRange.Rows.Copy

The problem that I have is if the amount of data is huge. In this case, I would like to make the copy in several steps.

For example, if oSheet.UsedRange.Count > 10000

oSheet.UsedRange.Rows.Copy (for 1 -> 100 )
‘ Some processing
oSheet.UsedRange.Rows.Copy (for 101 -> 200 )
‘ Some processing
…

How can I do this?
I have been trying to do this with this sentence
oSheet.UsedRange(“1:100”).Copy
but I got the “Type mismatch” error

Any hints?

Thanks a lot

Reply | Quote

Replies

I’m not familiar with how to do this in Excel, but I have done it in Word. Dimension a new range variable…

Dim rngTemp as Range

and then set it to the desired part of oSheet and copy from that. Repeat as needed.

Does this work?

Reply | Quote

UsedRange.Rows(“1:100”)

Reply | Quote

Thanks Sammy, that what I need

Reply | Quote

Sorry, Andrew I didn’t notice of your post. I’m going to try it right now. Thanks a lot

Reply | Quote

Yep, Andrew was much nicer to you!

Reply | Quote

Something like the following

    Sub CopyByInstallments()
    Dim lCols As Long
    Dim lRows As Long
    Dim i As Long
        lRows = ActiveSheet.UsedRange.Rows.Count
        lCols = ActiveSheet.UsedRange.Columns.Count
        For i = 1 To lRows Step 100
            If Cells(i, 1).End(xlDown).Row > i + 99 Then
                Range(Cells(i, 1), Cells(i + 99, lCols)).Copy
            Else
                Range(Cells(i, 1), Cells(i, lCols).End(xlDown)).Copy
            End If
            'Process copied cells 
        Next
    End Sub 

Andrew C

Reply | Quote

Andrew, what is xlDown?

Does this code work if I have a sheet with data only in the middle (instead of from the beginning)?

Reply | Quote

Sorry, I didn’t notice that xlDown is predefined.

Thanks for your post too, Sammy! Very direct and concise!

Reply | Quote

> Does this code work if I have a sheet with data only in the middle
Nope, but only takes a minor mod. When you use the Cells method on a range as below, then Cells(1,1) referes to the upper left-hand corner. HTH –Sam

Option Explicit

Sub CopyByInstallments()
Dim lCols As Long
Dim lRows As Long
Dim i As Long
    With ActiveSheet.UsedRange
        lRows = .Rows.Count
        lCols = .Columns.Count
        For i = 1 To lRows Step 100
            If .Cells(i, 1).End(xlDown).Row > i + 99 Then
                .Range(.Cells(i, 1), .Cells(i + 99, lCols)).Copy
            Else
                .Range(Cells(i, 1), .Cells(i, lCols).End(xlDown)).Copy
            End If
            'Process copied cells
        Next i
    End With
End Sub

Reply | Quote

Sam, something has to be wrong with that code…

If I have a excel doc with 130 rows, in the first iteration it always goes to the else condition.
I mean… It goes to .Range(Cells(i, 1), .Cells(i, lCols).End(xlDown)).Copy

Shoudn’t it go to the first?

I process a whole sheet with 130 rows, and in the second iteration it always get some text from the first

I’ll let you know if I guess something…

Reply | Quote

Hi,
I think you need to change the Else section from:

               .Range(Cells(i, 1), .Cells(i, lCols).End(xlDown)).Copy

to:

               .Range(.Cells(i, 1), .Cells(i, lCols).End(xlDown)).Copy

otherwise you’re going from Cells(i,1) of the sheet, not of the UsedRange.
Hope that helps.

Reply | Quote

Thanks for you reply, Rory.

Actually, I already change that. I think that the problem is in the condition. For some reason, it always goes to the else section…

Reply | Quote

, that code is garbage. Remind me never to post code without trying it out first. I left out a period in front of Cells and shouldn’t have put periods in front of Range. Must be a spiritual lesson here, something about jots and tittles? Anyway, here is the code that works. BTW, why do you want to do this? –Sam

Option Explicit
Sub CopyByInstallments()
Dim lCols As Long
Dim lRows As Long
Dim i As Long
    With ActiveSheet.UsedRange
        lRows = .Rows.Count
        lCols = .Columns.Count
        For i = 1 To lRows Step 100
            If .Cells(i, 1).End(xlDown).Row > i + 99 Then
                .Cells(i, 1).Select
                .Cells(i + 99, lCols).Select
                Range(.Cells(i, 1), .Cells(i + 99, lCols)).Copy
            Else
                Range(.Cells(i, 1), .Cells(i, lCols).End(xlDown)).Copy
            End If
        'Process copied cells
        Next i
    End With
End Sub

Reply | Quote

No way, Sam
It seems to work only with certain files.
Check it with the attached file that I’m posting. You’ll see that it makes two iterations. In the first one it copies the whole file, and in the second one, it copies part of end of the file that was already copied in the first iteration.

By the way…. you already helped me a lot to do the same with Powerpoint! Do you remember? I am writing an application that
stores data from thousands of powerpoint, word and excel files, in order to make queries (find a property, charts, text inside, …)
I thank you all your help!

Reply | Quote

> Do you remember?
Yes.

Also wondering if you are going to be running your macro with XL 97. If so, the UsedRange has a bug in it. If you have entered data in the sheet, then deleted it, the UsedRange still includes the cells that were cleared, so you’ll get extra blank cells at the end. If this is a problem, then we can fix it by recalculating lRows & lColumns with the End method. –Sam

Reply | Quote

Don’t know why we used the End method. It was killing us on workbooks like yours that didn’t have contigous data. Try this. Sorry for so much bad code. –Sam

Sub CopyByInstallments()
Dim lCols As Long
Dim lRows As Long
Dim i As Long
    With ActiveSheet.UsedRange
        lRows = .Rows.Count
        lCols = .Columns.Count
        For i = 1 To lRows Step 100
            If i + 100 < lRows Then
                 Range(.Cells(i, 1), .Cells(i + 99, lCols)).Copy
            Else
                Range(.Cells(i, 1), .Cells(lRows, lCols)).Copy
            End If
        'Process copied cells
        Next i
    End With
End Sub

Edited to remove YAB (yet another bug), actually just deleted the two lines that ended with .Select

Reply | Quote

I hate to say this…. but it still doesn’t work

In this line:
.Cells(i, 1).Select
I get the error “Select method or Range class failed”

I’m so sorry for such a stupid problems, but I’m very newbie with VBA

By the way, I’m not using this with a macro. Actually, I need to translate all this code to C++ to make it work with my program,
which is really funny!

Thanks again and sorry for all this

PS: I’m using VB6 with Excel 2000

Reply | Quote

Sorry, the two lines that ended with .Select were just a part of my checking to see if the correct area was being copied. However, they shouldn’t have caused a error, so we’re probably not done yet. I’ve edited the last code post. –Sam

Reply | Quote

I changed the following because I got the error “Method ‘range’ of object ‘_global’ failed.

Sub CopyByInstallments()
Dim lCols As Long
Dim lRows As Long
Dim i As Long
With ActiveSheet.UsedRange
lRows = .Rows.Count
lCols = .Columns.Count
For i = 1 To lRows Step 100
If i + 100 < lRows Then
ActiveSheet.Range(.Cells(i, 1), .Cells(i + 99, lCols)).Copy
Else
ActiveSheet.Range(.Cells(i, 1), .Cells(lRows, lCols)).Copy
End If
‘Process copied cells
Next i
End With
End Sub

I thought that just putting a . would work, but it doesn’t.
I hope that’s correct…

Reply | Quote

Since you are running Excel via automation, you don’t want no stinking ActiveSheet. You have to create a monster like this. Notice that I changed the Copy to Select and added a MsgBox so you can see that the cells are being selected correctly. You will want to change the Select to Copy and also remove the MsgBox. HTH –Sam

Option Explicit

Private Sub Command1_Click()
' Use Project, References to add Microsoft Excel Object Library
Dim xlApp   As New Excel.Application
Dim xlBook  As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim lCols As Long
Dim lRows As Long
Dim i As Long
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Open("c:wopr.xls")
    For Each xlSheet In xlBook.Worksheets
        xlSheet.Activate
        With xlSheet.UsedRange
            lRows = .Rows.Count
            lCols = .Columns.Count
            For i = 1 To lRows Step 100
                If i + 100 < lRows Then
                    Range(.Cells(i, 1), .Cells(i + 99, lCols)).Select
                Else
                    Range(.Cells(i, 1), .Cells(lRows, lCols)).Select
                End If
                MsgBox "well"
            'Process copied cells
            Next i
        End With
    Next xlSheet
    xlBook.Saved = True
    xlApp.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

Reply | Quote

Yes, Sam, I was using the automation in the same way that you say.

The only difference was that instead of using ActiveSheet, I was using

Set oSheet = oDoc.Sheets.Item(SheetNumber)

Maybe that’s the reason I got that error. This is part of the code I was using:

….
Set oSheet = oDoc.Sheets.Item(3) ‘ Sheet 3 for instance

Dim lCols As Long
Dim lRows As Long
Dim i As Long
With oSheet.UsedRange
lRows = .Rows.Count
lCols = .Columns.Count
For i = 1 To lRows Step 100
If i + 100 < lRows Then
oSheet.Range(.Cells(i, 1), .Cells(i + 99, lCols)).Copy
Else
oSheet.Range(.Cells(i, 1), .Cells(lRows, lCols)).Copy
End If
'Process copied cells
Next i
End With

Thanks again for your help. It really helped

Reply | Quote

The for each loop that I used is probably better. Notice, also, that I used the Worksheets collection, but you used the Sheets collection. The Sheets collection also includes ChartSheets — there’s no ranges/cells there. Not sure how you want to deal with Charts. –Sam

PS, just a nit, you don’t need the item property, oDoc.Worksheets(3) is more standard.

Reply | Quote

Thanks for those suggestions. I’ll keep that in mind

Reply | Quote

uhmm… one more thing!!

I forgot that the text inside a textbox (see attached file) is not retrieved with this method.
In order to do that, I used this:

Dim a As Excel.Shape

For f = 1 To oSheet.Shapes.Count
Set a = oSheet.Shapes(f)
MsgBox a.TextFrame.Characters.Text
Next f

This works fine, but I’m having a lot of problems converting this to C++ ( I get error “Member not found” when I try the get a shape form the shapes collection)

I’m wondering if there is a workaround to get this text… maybe without accessing the shapes or other way…

If somebody knows… will receive all my gratitude!

Thanks

Reply | Quote

oopss… here is attached file

Reply | Quote

Sam! I finally got it. It was a Microsoft bug. It seems to have a problem between Excel versions.
If you want to take a look, the bug is this one:

http://support.microsoft.com/default.aspx?…b;en-us;Q165273%5B/url%5D

I was doing this
vShape = xlSheet.OlePropertyGet(“Shapes”).OlePropertyGet(“Item”, 1);

However, I had to do this:
vShape = xlSheet.OlePropertyGet(“Shapes”).OleFunction(“Item”, 1);

Weird, eh?

Everything seems to work fine now… however (this happens with VB too), I don’t get the whole text inside the textbox.
You can see that with the example that I’m posting. I’m gonna keep researching to try to find the problem. If I find it,
I’ll post it here.

Reply | Quote

To get > 255 characters, you must use Characters method. See Q14881

Reply | Quote

Sam, once again…

THANKS A LOT !!!!!!

uffff… I didn’t know it was so tricky to use VBA. It’s hard to do the simplest thing!

Reply | Quote

How can you say that! VBA is kids compared to using C++ for automation. C++ is V E R Y U G L Y

What did you do with text on chart sheets? Now you can go back to PoPo and get all of the shapes that are really OLE containers!? Have fun! –Sam

Reply | Quote

Just a wild guess, because I don’t do C++, but are you sure that you are getting the Excel Shapes collection. Shapes is common to many different object models and they all have different properties. –Sam

Reply | Quote

You are probably beyond these examples, but look at Q184663 and here

Reply | Quote

I think that we posted at the same time

Reply | Quote