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

    Retrieving text from a Excel sheet (VBA/Excel)

    Hi guys

    I'm trying to retrieve text from a sheet in Excel.
    For example, if I want to get the text from the 5 first rows of an specific sheet, I do this:

    oSheet.Range("1:5")

    However, I don't know how to store the text in a variable, because if I try this

    dim MyText as String

    MyText = oSheet.Range("1:5").Value
    or
    MyText = oSheet.Range("1:5").Text

    MyText is always NULL. I know that oSheet.Range("1:5") works, because if I do oSheet.Range("1:5").Copy is stored correctly in the clipboard.

    Does anybody know how can I retrieve this text?

    Thanks

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

    Re: Retrieving text from a Excel sheet (VBA/Excel)

    What do you want to do with the range? A string variable can only contain the text from a single cell. --Sam
    <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>

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

    Re: Retrieving text from a Excel sheet (VBA/Excel)

    if
    Cell(1,1) ="hi"
    Cell(1,2) ="there"
    Cell(2,1)="see"
    Cell(2,2)="you"

    MyText = "Hi therensee you"

    Something like that... I was wondering if there is any way to do this automatically, instead of going cell by cell building my string

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

    Re: Retrieving text from a Excel sheet (VBA/Excel)

    <pre> [A11] = Split("Hi there what's new?", " ")</pre>

    would be the shortest. Anything with multiple lines like you have would require you to write a function.
    <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>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving text from a Excel sheet (VBA/Excel)

    Try:

    <pre>Dim MyText As Variant
    MyText = Worksheets("Sheet1").Range("1:5").Value
    </pre>


    Then:

    MyText(1,1)='"hi"
    MyText(1,2)="there"
    MtText(2,1)="see"
    MyText(2,2)="you"
    Legare Coleman

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

    Re: Retrieving text from a Excel sheet (VBA/Excel)

    Thanks, I will try both things

Posting Permissions

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