+ Reply to Thread
Results 1 to 2 of 2

Changing file name for database reports

  1. #1
    Registered User
    Join Date
    03-22-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Changing file name for database reports

    Hi Everyone,

    I run a small non-profit medical facility with high turnover. We track patients in a database program that can export a report of the current census of the unit in .csv format. Since we do not have medical staff on 24/7 I have to rely on orderlies to track the current census and update it in the system and on handwritten forms when clients leave. Since the turnover is so high reconciling the computer system and handwritten forms can be a confusing task.

    To help prevent confusion, I spent the past week writing a comprehensive macro that formats the .csv reports into printed forms that the orderlies can use in place of the printed forms. Since the orderlies do not have very good computer skills i have written very specific instructions on how to run the reports in the database software (which automatically opens the .csv file in excel). Then they simply need to hit the command I have set up to run the macro and it automatically prints. This routine is simple enough that all of the orderlies can understand and run it and that part of it works great.

    The problem I'm running into is that the reports need to be manually retitled in the database program each time it is run, because the default file name is "CLTLST." I tried using a macro that does a save as, and automatically retitles the resulting file, but I am still stuck with the original called "CLTLST". If there is a file called "CLTLST" existing in the folder I am saving the .csv file to, the program will not replace it, and it will not allow the file to be saved.

    Is there an automatic way to rename or delete the original .csv file called CLTLST? I do not trust the orderlies to know to delete it or change the file name in the software program. If I was able to remove or change the original .csv file name each time the macro is run it would ensure that the orderlies do not need to manually enter a unique file name each time and substantially reduce the possibility of errors.

    I hope this is clear enough to understand.

    Thanks in advance for any help!

  2. #2
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Changing file name for database reports

    hi,

    Welcome to the Forum

    Quote Originally Posted by mhig9000 View Post
    ...
    Is there an automatic way to rename or delete the original .csv file called CLTLST? I do not trust the orderlies to know to delete it or change the file name in the software program. If I was able to remove or change the original .csv file name each time the macro is run it would ensure that the orderlies do not need to manually enter a unique file name each time and substantially reduce the possibility of errors.
    Yes, there are a few ways:
    - Look up "Directories and Files Keyword Summary" in the VBE Help Files & then check out the "Name" statement (+ the other listed items).
    - As a last resort, look up "Kill" in the VBE Help files. NOTE, "kill" may not (or does not?) move the deleted file to your Recycle Bin, so be very certain that you want to delete it.

    To create a unique file name you could use a time stamp. Here is a code snippet that you may be able to modify...

    Please Login or Register  to view this content.
    If you need further help, I recommend posting a sample workbook with your current code.

    hth
    Rob
    Last edited by broro183; 04-20-2010 at 05:10 PM. Reason: add suggestion
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1