Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filename Capture (VBA/MS Excel/97)

    I have written a macro to name a file that the user has created based on a date range in the format 'dd-ddmmyyyyy-xxx' where 'xxx' is a category that is applied to the file name from a choice the user has made from a userform.

    Temporarily, I store the filename in a hidden file that contains the code then remove the filename from the hidden file when the user closes the created file that has used the stored filename.

    The above is the procedure for when the user creates a new file from a CSV file.

    My question is how can I store the filename when the user does not create a new file but uses an existing named file to create their 'new' file? For example, in a previous session, they may have created a file named '07-13032002-ALL' (meaning a file with ALL categories for the 7th to the 13th of March 2002).

    I cannot determine how to capture the opened file name (e.g. '07-13032002-ALL' so that they can do some filtering of the content and then save it as '07-13032002-CAT2') The user needs to keep both files with their filenames.

    Any suggestions?

  2. #2
    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: Filename Capture (VBA/MS Excel/97)

    Is the question a problem of timing

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Capture (VBA/MS Excel/97)

    Hi Jefferson

    No, it was not a matter of timing, my problem was how to capture the user's filename. A search of the Net came up with the following solution. (This same site also had an answer to a corrupted XL workbook problem I experienced last month.)

    http://www.erlandsendata.no/english/vba/fo...efoldername.htm

    With suitable modifications to the code I was able to get it to do exactly what I needed including break it up into two components (date and category). One of the modifications was to the reference to 'ThisWorkbook'. This had to become 'Workbooks(2)' so that the filename was not of the hidden file that contains the VBA code.

    Thanks, Leigh

  4. #4
    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: Filename Capture (VBA/MS Excel/97)

    Oh, duh. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Well, maybe I can be of some use to you. Just in case hardcoding "2" turns out not to be flexible enough, in Excel 2000 (don't have 97, sorry) you also could use these:

    <pre>ActiveWorkbook.Name 'file name of "active" workbook

    Workbooks(Workbooks.Count).Name 'file name of last workbook opened/created</pre>


  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Capture (VBA/MS Excel/97)

    Totally off track - have you thought of creating the time-stamp in the format yyyy-mm-dd instead? It just means that things actually sit in date order when you're looking for them with windows explorer, as opposed to all the 13th's sitting next to each other.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Capture (VBA/MS Excel/97)

    Brooke, the users are fairly traditional and I would have had a lot of resistance to changing the date format to anything other than 'ddmmyyyy' style. However, I appreciate your suggestion regarding the date order when viewed in Windows Explorer but I will have to save it for another time.

    In the same vein, I am encouraging users to consider the most recently used (date) order when the File | Open command is used. This way they will have their most recently used files sitting either at or near the top of the file list.

    Thanks for your suggestion, Leigh

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Capture (VBA/MS Excel/97)

    Thanks for the options you offered. However, I was not able to use a reference to 'ActiveWorkbook' as it would have referenced the hidden workbook with the VBA code and I would also be unable to use the 'Workbooks(Workbooks.Count).Name' option because the user had not named the file at this stage of the code.

    I have been able to incorporate the reference to the active workbook later in the code when switching between them to enable temporary filename storage in the hidden file.

    Thanks, Leigh

  8. #8
    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: Filename Capture (VBA/MS Excel/97)

    I've concluded that I don't understand your application at all. But that's okay, I'm not an Excel person!

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filename Capture (VBA/MS Excel/97)

    Basically what happens is that a user can access data that has been created in CSV format by the IT department from various sources. This original data file is either filtered to show a range of items of interest to the user for QA evaluation (medical tests) or a specific test together with categories of times (eg Cat 1, Cat 2, etc.). The user creates a file that they name and save with the date range (typically, for the previous week) and type of test and category (eg 24-30032002-ALL-B1.xls). This data file is saved ready for reuse when the user needs to check the QA for another test type and the category that it matched. It allows the users to re-evaluate data again and again until they are satisfied that all relevant QA matters are considered.

    The code that I developed is now working very well thanks to Woody's Lounge and its contributors.

    So don't feel too badly about not understanding what the application was trying to achieve. It needed a lot of explaining obviously and I had resolved the other issues but needed specific help with a couple of parts that had me stumped. Like the problems that VBA expects the US date format to be used, and my difficulty in handling an unnamed file programmatically.

    Those issues aside, all seems OK now. BTW, I sometimes think I would rather be dabbling in MS Word's VBA.

Posting Permissions

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