Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format Code (Excel XP)

    Hello,

    I need to convert the data imported in from a text file (see "From this") into a data table (see "to this). I created a macro using Edit, Paste Special, Transpose and then inserting rows and using offset, but I cant' put it all together. Attached is a zip file with the two jpegs.

    I'd appreciate any suggestions

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

    Re: Format Code (Excel XP)

    Could you also post the "From this" spreadsheet? That would give Loungers something to work on.

  3. #3
    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: Format Code (Excel XP)

    An alternative to using the Paste Special method would be to use VBA to read the text file. I guess that's a fallback if the method you currently use turns out to be a nightmare to program. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Format Code (Excel XP)

    It would be much better if you post both sheets in one workbook rather than a graphic; it saves Loungers the trouble of having to recreate the sample data. I assumed that for each 5 rows of imported data relating to one record, that column F respectively contains the total spots, average cost, minimum cost, maximum cost, and the total spots times the average rate, derived from columns C, D & E.

    However, if so, the actual data for the column F doesn't map to the what I would expect in the desired format; for example in the first record only one of the values is the same:<table border=1><td align=right></td><td align=center>Source</td><td align=center>Target</td><td>spots/demo</td><td align=right>7258</td><td align=right>7478</td><td>cost/spot</td><td align=right>478.56</td><td align=right>496.63</td><td>hi rate</td><td align=right>5000</td><td align=right>7500</td><td>lo rate</td><td align=right>10</td><td align=right>10</td><td>gross</td><td align=right>3476147</td><td align=right>3691350</td></table>Assuming that the data in the desired layout sample is sample data and is not correct, the attached basic Excel macro will create a new worksheet within the workbook and convert the imported text data into the new worksheet. Edit the titles as needed. As Jefferson notes, it would be even better to convert directly from the text file, but until you could post a sample text file the coding would be guesswork.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Code (Excel XP)

    John,

    All apologies!!! For some reason, our email system marked the responses to my email as spam and I have just now realized it. I was thinking I had breached some sort of etiquette, since I did not get any responses as I normally do.

    I will have a look at your solution and carry on.

    Thank you for getting back to me.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Code (Excel XP)

    Hans,

    I will post it tomorrow. Thanks.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Code (Excel XP)

    jscher2000,

    All apologies!!! For some reason, our email system marked the responses to my email as spam and I have just now realized it. I was thinking I had breached some sort of etiquette, since I did not get any responses as I normally do.

    That text file was a monster in itself that had to be tamed so that it could even come into Excel.

    Still, I'm curious

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Format Code (Excel XP)

    Post back with specific error lines or reformatting errors if my code doesn't work correctly, and I'll also look for your post with an attached Excel workbook with the 'before and after'. If you want to post a sample text file, you could cut it down to just the first 10 records or so. (In either Excel or text, you might want to censor or disguise the data for privacy reasons.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    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: Format Code (Excel XP)

    > Still, I'm curious

    Depending on the format of your text file, you have several options. For CSV files, you can address them as databases using the ADO methods normally applied to other databases. For tab-delimited files, ADO is less convenient, because you have to set some configuration setting in an INI file somewhere, or something like that. However, tab delimited files can be read line by line (using either VB or FileSystemObject methods) and populated into a data structure, whether an array (e.g., using the Split() method) or a user defined type. In fact, there are so many ways to do this, that it's quite difficult to recommend a single one best way. It depends on your source data format, the types of manipulations you need to do before populating your worksheet, and what is most efficient for you to code and debug.

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Code (Excel XP)

    Hello everyone,

    Thank you for all of your help and suggestions. To give you a better idea of what I'm trying to accomplish, I've taken the advice to produce the before and after sheets into a spreadsheet. Please look at the attached. Do not worry that the numbers are different. I used 2 different files (unwittingly) to show you the before and after. It's the format I'm trying to change.

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

    Re: Format Code (Excel XP)

    The attached text file contains a version of JohnBF's code that has been modified only slightly to work with the spreadsheet you posted.

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Code (Excel XP)

    Hello,

    I have downloaded and will now look and insert the code. I'm beginning to understand this language. I got a Step-by -Step Excel VBA book from Microsoft and am studying it. I hope pretty soon to be a 2-star lounger and give back!!! ;-)

    Thanks to all for your help.

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

    Re: Format Code (Excel XP)

    You are already a 2StarLounger, now the rest <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Format Code (Excel XP)

    Gene, one liberty that the code, as corrected by HansV, takes, is converting the GROSS_DOLLARS into a calculated amount instead of an imported value. Just my prejudice to having any calculated field be a calculation. If you'd rather have it just be a value, it's an easy fix, just ask (and HansV will again beat me to the punch and fix it! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>).

    I was also thinking, with a kind of belt-&suspenders mentality, that replacing this line:

    Set rngSource = ActiveSheet.[A1].CurrentRegion

    with this slightly redundant one

    Set rngSource = ActiveSheet.UsedRange.Cells(1, 1).CurrentRegion

    would handle the import of the text file into the sheet starting at a cell other than A1.

    Personally, as an intermediate Excel coder, I really like Julitta Korol's Excel 2000 VBA book. Search this Forum and the Excel Forum for various Excel VBA book discussions and opinions.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Code (Excel XP)

    Holy code, Batman!!! I just noticed that I was bumped up.

    Now, there was one little glitch in the code (I think). On the line Set wksTarget = .Worksheets.Add, as I stepped through the code, the Converted worksheet wasn't being created and the code stopped. I took out the period before Worksheets. Add and now the spreadsheet got created and everything seems to be working fine.

    What a solution!!! I'll be studying this for a while.

    Thanks

Page 1 of 2 12 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
  •