Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More problems retrieving text from Excel (Excel/VBA)

    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

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

    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?

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More problems retrieving text from Excel (Excel/VBA)

    UsedRange.Rows("1:100")
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

    Something like the following <pre> 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 </pre>

    Andrew C

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

    Thanks Sammy, that what I need [img]/forums/images/smilies/smile.gif[/img]

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

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

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More problems retrieving text from Excel (Excel/VBA)

    Yep, Andrew was much nicer to you! <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Star Lounger
    Join Date
    Apr 2002
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

    Andrew, what is xlDown?

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

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

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

    Thanks for your post too, Sammy! Very direct and concise! [img]/forums/images/smilies/biggrin.gif[/img]

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More problems retrieving text from Excel (Excel/VBA)

    > 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
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Star Lounger
    Join Date
    Apr 2002
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

    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 [img]/forums/images/smilies/sad.gif[/img]

    I'll let you know if I guess something...

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

    Hi,
    I think you need to change the Else section from:
    <pre> .Range(Cells(i, 1), .Cells(i, lCols).End(xlDown)).Copy
    </pre>

    to:
    <pre> .Range(<big><font color=red>.</font color=red></big>Cells(i, 1), .Cells(i, lCols).End(xlDown)).Copy
    </pre>

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

    Microsoft MVP - Excel

  13. #13
    Star Lounger
    Join Date
    Apr 2002
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

    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...

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More problems retrieving text from Excel (Excel/VBA)

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15>, 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
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  15. #15
    Star Lounger
    Join Date
    Apr 2002
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More problems retrieving text from Excel (Excel/VBA)

    No way, Sam [img]/forums/images/smilies/sad.gif[/img]
    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, ...)
    [img]/forums/images/smilies/smile.gif[/img] I thank you all your help!
    Attached Files Attached Files

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •