Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto-open-sort When a Target file is saved

    I have a worksheet that is linked to a sheet maintained in a separate file by a different department. The target sheet has project names, production size (# items), and delivery dates. Because of the irregularities in their table (duplicated information, columns that could have multiple dates in certain rows, I have to set up conditional if statements to assign certain rows to "AAdummy" as the project name. Actually, I import the table as is and do the conditional exclusions with "AAdummy" in a linked worksheet. This later sheet is then used, in a series of projection worksheets, to group projects by type (worker-skill groups and Export dates) for planning FTEs and daily production requirements. When rows are added or deleted from the base table, the date-Project matchups in my other applications all fail. When I finally see the problem and deduce its cause, I return to and resort my "AAdummy" sheet and the problems disappear. I desparately need a way to have my "AAdummy" sheet open and re-sort automatically every time the base workbook (which has the name GRID1.XLS) is saved.
    I am in the VBA learning phase and would appreciate it someone could lay out the code or the approach I should take.
    Thanks in advance.
    Stephen


    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

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

    Re: Auto-open-sort When a Target file is saved

    How about putting a BeforeSave (or Before Close) event in place in GRID1.xls to open "AAdummy", and then in "AAdummy" having a Workbook_Open event to sort the required range.

    You could put the following in GRID1.xls (in the Workbook object, and not in a module)
    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Workbooks.Open Filename:="DocumentsExcelAAdummy.xls"
    End Sub</pre>

    suitably adapted to match the path of your own file.

    In the workbook you want sorted (AADummy) you would need something like the following in the workbook object :
    <pre>Private Sub Workbook_Open()
    procedure to sort required range
    End Sub</pre>

    If you are not sure of the code to use to sort your data, record a macro of an actual sort of the range involved and use that as the code in above. (the bit between Sub...() and End Sub)

    Hope that can get you started at least.

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-open-sort When a Target file is saved

    Andrew, Thank you foryour response. I will definitely be able to use your suggestions for another similar problem but for this one I need a little twist. There are two catches. a) I do not want the person who saves the GRID workbook to have the one with AADummy fields (which is named SSGRID.xls) to open on their computer; and
    [img]/forums/images/smilies/cool.gif[/img] If I happen to be in SSGRID while they are saving changes what would happen.
    I think what I need is something that will do the following:
    1) when I open the file linked to SSGRID (Prod_Monitor.xls), I need to have a macro warn me if the last save date for GRID1 is later than the last save date for SSGRID.xls or simply update & sort & save SSGRID.xls as part of the opening of the Prod_Monitor.xls; and
    2)When I save Prod_Monitor.xls, it needs to check again to see if the save date-times are out of sequence; and last
    3) I need to have a macro that I can run manually to check the date-time save sequence, if I see anything suspicious with equations in Prod_Monitor.xls.
    Sorry I did not spec this out at first but it all did not surface until after I saw your solution.
    Thanks for the help you have given me thus far.
    Stephen

  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: Auto-open-sort When a Target file is saved

    Stephen,
    Ok, there should be no problem setting things up the way you want them. But first could you just clear one or two things. Is there a possibility that when you have Prod_Monitor.xls open that SSGRID could also be open for editing by somebody else, or indeed yourself. In your second point, what do you need to do if the files are out of sequence. For the record, the VBA function FileDateTime() will return the date and time a given file was last saved. So FileDateTime("DocumentsGRID1.xls") will tell you the date and time GRID1 was last saved .

    Andrew

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-open-sort When a Target file is saved

    Andrew:
    No, I often have Prod_Monitor.xls and SSGRID open at the same time but no one else would be making entries to either file. However, they could be making changes to GRID1 (where SSGRID gets its data from) while I have both of the other files open. But, I could make it a practice to not have SSGRID and Prod_monitor open at the same time, if it would help.
    If I make a change to GRID1, which I rarely do, I would know to go to SSGRID and make the adjustments necessary to pick up the change.
    If GRID1 has had changes (additions or deletions) I have to refresh the link, so I get the new values, and, probably because of the way I designed the links and the fact that I used Lookup instead of VLookup in places, my lookups (to get data from SSGRID) will mostly fail unless I resort the linked worksheet in SSGRID so that all the rows with AADUMMY for project name go to the top.
    A short explanation of why I used this "AADUMMY " thing: The source document (GRID1) has over 400 rows with various information about our past and present projects and I only need about 40 to 50 of these for my Production planning and tracking applications. For example, a Project number might appear 5 to 10 times because it had additional orders each of 5 or 10 months. I only need the line with the final delivery date and the expected order amounts for future dates. Other projects are kept on GRID1 after the Medical data (we produce clinical data abstracted from Medical Records) has been exported because the physical records need to be forwarded back to the government (which is of no consequence to our Production Planning). So, I could end up with 300 identical AADUMMY lines, which cause my Lookups to fail if they are not sorted to the top.
    Sorry to be so wordy and thanks for your help. I could always send you a copy of the spreadsheets but I am sure they would give you a headache and I think we are already close enough to an answer.
    Regards,
    Stephen
    sstollma@juno.com

Posting Permissions

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