asked on

Rename, Move, Run Access .BAT file

Hello,

I am having trouble with my bat file. I am trying to add today's date to a file and then move the previous days file in another folder. Example:
Yesterdays file is in a folder, I want to run a script to move that and then run my access macro. Once that file is complete then rename the file adding today's date to the end of it. Or is there a way to add today's date to the end of the file name in access? Which ever is easier? Below is what I have:

move "G:\CLSINC\Provana Queries\Daily Letters\DantomPrevDay_*.*".xlsx" "G:\CLSINC\Provana Queries\Daily Letters\Older\
"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.exe" "E:\CLS Queries\Provana Database.accdb" /x LettersDailyAudit

SETLOCAL ENABLEDELAYEDEXPANSION
@echo off

For /F "tokens=2,3,4 delims=/ " %%A in ('Date /t') do @(
Set Day=%%A
Set Month=%%B
Set Year=%%C
Set All=%%A%%B!Year:~-2!
)

Copy "G:\CLSINC\Provana Queries\Daily Letters\DantomPrevDay_.xlsx" "DantomPrevDay_!All!.xlsx"
Del "G:\CLSINC\Provana Queries\Daily Letters\DantomPrevDay_.xlsx"

ste5an

The normal approach would be using a timestamp in your tables. Then you don't need to backup your database like this. Shadow copy would be enough.

btw, what is the problem with your script?

NVIT

Missing " at end?

move "G:\CLSINC\Provana Queries\Daily Letters\DantomPrevDay_*.*".xlsx" "G:\CLSINC\Provana Queries\Daily Letters\Older

Open in new window

should be...

move "G:\CLSINC\Provana Queries\Daily Letters\DantomPrevDay_*.*".xlsx" "G:\CLSINC\Provana Queries\Daily Letters\Older"

Open in new window

Bill Prew

This path looks wrong, were you looking for one file, or many?

"G:\CLSINC\Provana Queries\Daily Letters\DantomPrevDay_*.*".xlsx"

~bp

WO2015

ASKER

ste5an, how do you use the time stamp in the tables?

WO2015

ASKER

Thank you NVIT, I will add that and see if the "move" works.

Bill Prew - the path is correct, I am looking for one file but the naming convention will vary after DantomPrevDay_ because is will have a different date every day.

ste5an

By adding a date/time column to the relevant tables.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WO2015

ASKER

ste5an, thank you, I have that. How to I make that field appear now in the export file name?

ste5an

There must be a Save or SaveAs call. Or an TransferSpreadsheet.

WO2015

ASKER

I think I am getting close (I think), Iv never heard of this to I am guessing as what to do here, can you please help? Here is what I have but its not working, I just want todays date in the export file...

My macro runs the query and then I have the TransferSpreadsheet action:
Type: Export
Spreadsheet Type: Excel
Table Name: DantomPrevDay
File Name: "G:\CLSINC\Provana Queries\Daily Letters\DantomPrevDay_"& Format(Date(), "yyyy\_mm\_dd") &".xlsx"
Has Field Names: Yes
Range: BLANK

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WO2015

ASKER

Access 2007, below is what mine looks like.

WO2015

ASKER

That worked once I edited the File Name, THANK YOU!!

WO2015

ASKER

Do I even need "Todays Date" in the table?

ste5an

Not really, when you use the Date() function.

WO2015

ASKER

Everything is working except the "move" the file is not moving its just overwriting. This is my new script:

move "G:\CLSINC\Provana Queries\Daily Letters\DantomPrevDay_*.*".xlsx" "G:\CLSINC\Provana Queries\Daily Letters\Older"
"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.exe" "E:\CLS Queries\Provana Database.accdb" /x LettersDailyAudit

Bill Prew

See my prior post http:#a40996228.

~bp

WO2015

ASKER

Bill, I tried both ways, neither moved the file.

Bill Prew

What was displayed when you executed the command, and can you post up the commands you ran as well please.

~bp

WO2015

ASKER

I got it to work after trying a few things. Thank you for everyone's help!