Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Byte Count Using VBA? (Excel 2003)

    The task is formatting a longish Access report in Plain Text using Excel - for viewing in a PDA.

    Anything >4KB is truncated by the PDA display. Any known way of calculating the 4096th byte in code - so all processing can be done inside Excel (rather than switching over to a text editor for the final file splitting)?
    Gre

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Byte Count Using VBA? (Excel 2003)

    Where is your Access report? As long as it's in Access, or exported to Excel, 'byte count' is rather meaningless. I think you'll have to export it to a text file and process that.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Byte Count Using VBA? (Excel 2003)

    The finished product is a Plain Text file in Excel. (The data is imported from Access as a Query.)

    You seem to be confirming that Excel's "supersonic" code can't parse something as basic as where the 4096th byte in a file is - whereas a text editor can. To quote the Firesign Theater: "Just as we thought!"

    (The stupidest questions are the ones that aren't asked.)
    Gre

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Byte Count Using VBA? (Excel 2003)

    You can use either the "classic" Basic text file operations or Scripting.TextStream in Excel code, but you'll be processing a text file, not a workbook.

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

    Re: Byte Count Using VBA? (Excel 2003)

    That's not quite correct. The problem is that you have not defined what you want to parse. If the data/text is in a VBA string, lets call it strLongString, then you can very easily get the first 4096 bytes like this:

    <code>
    strFirst4096 = Left(strLongString,4096)
    </code>

    The problem is, if you import the text into Excel, then the string is going to be a bunch of string in cells. You have to tell us what it is you want to parse.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Byte Count Using VBA? (Excel 2003)

    Thanks, Legare. It appears that <code>Len()</code> will get me most of the way there.

    FWIW, in a 6 column set of values, <code>=LEN(A1&B1&C1&D1&E1&F1)+7</code> seems to produce the number of bytes up to the start of the second line. Why the supplemental value of 7? 1 per column divider + 1 for the carriage return.

    Now back to coding a little loop that iterates down to the last CR before 4096 (or thereabouts)! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Gre

Posting Permissions

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