Copying and moving files in excel using access

[MaDoyle]

i hope some once can help

We have 5 excel reports automatically saved overnight for us all the files are called Bch_report_inbopund (and then they have the time stamp and computer and and server number they have been run on added top the name field). i was using the file copy funtioon to copy the files and move them to another location. the problem i am having is the files are no longer run at the same time and on the same server on a daily basis so this is changing the file name which is stoping my file copy from working.

I was wonderining if it is possible to use the like function and the file copy funtion together..

thanks

 

[DCrake]

Simple Software Solutions

How are the Excel files saved automatically?

If this is done within Excel using code or VBA for example why not get that functionality to move the newly created files to a central location which your app can import.

CodeMaster:

 

[MaDoyle]

Sorry i may not have been clear its not that i can't access the folder where the files are saved its more that the nameing of each individual file changes on a daily basis and this is whats stoping me qutomating the import of the files

 

[DCrake]

Simple Software Solutions

For arguments sake post the names of of yesterday's and today's file names of one particular file for comparison.

CodeMaster:

 

[MaDoyle]

Thanks for you help

the 2 filenames are

Branch_Report - server4 6-17-08 6'08'10 AM
Branch_Report - server2 6-17-08 5'15'10 AM

 

[DCrake]

Simple Software Solutions

My first concern is the existance of the single quote marks in the file names. Even though Windows allows this character trying to parse it will become difficult.

The common factor is the "Branch_Report - Server" element of the file name.

The next question is there less than 10 servers?

if so then we know that the date element appears between positions x & y.

So we can parse this out using the Mid() function

fDate = Mid(FielName,x,y)

It would be easier if the dates had 01,02 instead of 1,2 for the day elements

We can do the same for the time element.

I assume you have some sort of log that keeps the details of which files have been imported and when they were imported. If so you can now make a comparion to check if you have already imported the file.

What happens to the files after they have been imported in Access?

CodeMaster:

 

[MaDoyle]

i run a number of queries on the data once i have importted it ..

is that not a way i can write some code to say if the file is like BranchName* then import the data, sorry im new to access so may be in a bit over my head

 

[DCrake]

Simple Software Solutions

Wher you state BranchName is this the actual name of a branch or the words BranchName?

I assume the latter.

On the OnClick of you import button get access to enumerate through the directory and if the file name begins with "BranchName" then pass the full name of the file to you import code as a variable. If you are running the import from a macro then convert your macro to VB and look how it breaks the different elements down.


David