Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Intense macro/Solution Needed (2003 )

    Greetings,

    I have a task that seems doable however it is beyond my abilities.

    I have a file that is generated by my users, from a master. Inside the attached file I have tried to detail the areas that are not to be considered in the end solution. What I am in need of is the following.

    1) Need to generate a CSV file as detailed in the attached.
    2) Need to basically, read a Cell, then traverse to the proper area, gather the information, and then create a separate CSV file.
    3) The

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

    Re: Intense macro/Solution Needed (2003 )

    I'm sorry to say so, but this is a really horrible design. Trying to determine the relationships from the arrows is a nightmare, since the arrows are drawing objects floating in a separate layer above the worksheet.

    I, for one, am not going to even try to do something with it.

    I would tell the users to provide the data in a format resembling the end result, otherwise forget about it. Sorry to sound so harsh.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intense macro/Solution Needed (2003 )

    What about using absolute cell location, thereby disregarding the "arrows"? The basic Center section locationsdo not change, nor do the locations of the related Parents.

    Brad

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

    Re: Intense macro/Solution Needed (2003 )

    I'm afraid it won't work, at least not easily. Some children are to the left of their parent, others to the right. There are children cells in the same row as a childless "parent".

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intense macro/Solution Needed (2003 )

    Hans,
    How about if All the Children were to either the left or right, and aligned such that there were no ghildren in a childless parent?

    Thanks,
    Brad

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

    Re: Intense macro/Solution Needed (2003 )

    I don't enjoy being so contrary, but by the time you (or your users) are willing to reorganize the data, you might as well go the whole way and put them in a usable format, so that no more conversion is needed.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intense macro/Solution Needed (2003 )

    Hans,
    How about this, since the "users" will most likely hang me if I propose changing their beloved form, I could make a linkage to another sheet, putting the info into the desired state. If that occurs, could a macro be generated to open a new workbook, xfer the data and make a csv file without the user being overly involved? These guys are unwilling to accept much of any new things....

    Brad

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

    Re: Intense macro/Solution Needed (2003 )

    Once you've got the data in a reasonable format, exporting to .csv is as simple as File | Save As... You can record a macro of doing this.
    The problem is getting the data from the current format into something usable.

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intense macro/Solution Needed (2003 )

    Ok,,,,
    Got it doing what I want. Basically, I built another sheet in the workbook, with absolute cell references to the 'master' sheet. Then created the below macro. The only thing In the macro I cannot figure, is to 'replace' the book1 reference with the contents of cell G21.

    Is this possible?

    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 11/29/2006 by Brad Smallwood
    '

    '
    Sheets("Data sheet").Select
    Range("A1:E33").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
    "C:My DocumentsTool FilesBook1.csv", FileFormat:=xlCSV _
    , CreateBackup:=False
    ActiveWindow.Close
    End Sub


    Thanks,
    Brad

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

    Re: Intense macro/Solution Needed (2003 )

    Try this:

    ActiveWorkbook.SaveAs Filename:= _
    "C:My DocumentsTool Files" & Range("G21") & ".csv", FileFormat:=xlCSV _
    , CreateBackup:=False

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intense macro/Solution Needed (2003 )

    Hans,
    Code worked great, except it was reading the cell G21 from the newly created worksheet. I adjusted to read the A2 cell from the new workbook and all is well.


    Thanks
    Brad

Posting Permissions

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