• Filename Capture (VBA/MS Excel/97)

    Author
    Topic
    #369126

    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?

    Viewing 0 reply threads
    Author
    Replies
    • #580400

      Is the question a problem of timing

      • #580586

        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%5B/url%5D

        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

        • #580640

          Oh, duh. smile 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:

          ActiveWorkbook.Name              'file name of "active" workbook
           
          Workbooks(Workbooks.Count).Name  'file name of last workbook opened/created
          • #580913

            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

            • #580918

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

            • #580920

              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.

        • #580804

          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.

          • #580909

            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

    Viewing 0 reply threads
    Reply To: Filename Capture (VBA/MS Excel/97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: