Print and delete excel-file

[Guest]

I need help to shorten the worktime here.

I have a form that has a subform in it.
The main form contains the shipment-ID
In the subform there are several ordernumbers that comes in the shipment.
Most of the ordernumbers have an Excel-file containing information about the
order.
Since there are ALOT of Excel-files, I wonder if there is a way to find the
Excel-files regarding the ordernumbers shown in the subform in Access.
The files are always in a specific path. (W:\Folder1\Folder2\Supplier\
Every supplier has its own folder, but since a supplier can have more than
one name (I know this sounds funny, but its a fact) it complicates everything.
The name of the Excel-file contains both suppliername and ordernumber, so it
can not search for exact filename. (The search would be something like *1234*)

Since the order at this time have been delivered to the warehouse, I need
this file printed on paper.
After the paper comes out, the user can have the option to delete the file
since it is no longer useful.

The reason I need to automate this function, is because the files arent
always in the right folder, (different suppliernames) and this forces the
user to search for it.
Since there are many orders, this takes to long time.

I´m thinking there can be a button in the main form.
This finds the files and opens the searchresults in another form.
In that form the user can select the files and then have the options to
print out selected files or deleting selected files.

I know that this is an advanced question, and this is perhaps impossible to
do, but I´m hoping.

If you have any other suggestions on how to solve this, I would be grateful
for this.

BR
Claes

 

[TC]

Claes D wrote:

I have a form that has a subform in it.
The main form contains the shipment-ID
In the subform there are several ordernumbers that comes in the shipment.

Ok, so a Shipment can have many Orders.

Most of the ordernumbers have an Excel-file containing information about the order.

So you need a predictable naming scheme for the Excel files. Given an
Order record in the subform, you code needs to be able to determine the
exact name that is used for the corresponding Excel file if any. That
is not a difficult problem.

Since there are ALOT of Excel-files,

The number of Excel files is irrelevant, if you can handle them
automatically through code. Is that what you're aiming for?

I wonder if there is a way to find the Excel-files regarding the ordernumbers shown in the subform in Access.

Sure. You just need a predictable naming scheme. For example, your rule
might be, that OrderNumber 1234 has a corresponding Excel file
"C:\TheFiles\Order_1234.xls". That correspondence would be easy to
program.

The files are always in a specific path. (W:\Folder1\Folder2\Supplier\

Good. If they were /not/ in a predictable path, your code wouldn';t be
able to find them quickly, would it?

Every supplier has its own folder, but since a supplier can have more than
one name (I know this sounds funny, but its a fact) it complicates everything.

That sounds like an error in your database table design. Each supplier
should have a unique identifier, ie. a Supplier ID. You should /not/ be
identifying suppliers, by their name. Their name should be a "non-key
attribute" of their unique Supplier ID. What is the structure of your
Suppliers table?

The name of the Excel-file contains both suppliername and ordernumber, soit
can not search for exact filename. (The search would be something like *1234*)

Sure it can. If it knows the name, and the number, and the rule for how
to put them together, it just puts them together according to that
rule, and checks for the resultant name. For example, the rule might be
to join the name & the number with an underscore (_), and append the
".xls" extension. For example: "Smith And Company_1234.xls"

/BUT/, it's a bad idea to use the supplier name in a filename. You
shouldn't distinguish suppliers, by name. You should use a unique ID of
some kind. Also, supplier names could easily include characters that
can't be used in Windows filenames. You need to change this part of
your process.

Since the order at this time have been delivered to the warehouse, I need
this file printed on paper. After the paper comes out, the user can have the
option to delete the file since it is no longer useful.

You can delete files with the VBA "Kill" statement. Look that up in F1
help.

The reason I need to automate this function, is because the files arent
always in the right folder, (different suppliernames) and this forces the
user to search for it.

Then the process is wrong. You need to change the process to get the
files in the right place! If your workers are putting the 3" bolts in
the 1" bin, you do not try & adapt to deal with that. You tell them to
put each bolt in the right bin!

Since there are many orders, this takes to long time.

So you need to automate it throgh your program code. I guess that's
what you're trying to achieve, with some help from this group. I'm sure
it is achievable.

I´m thinking there can be a button in the main form.
This finds the files and opens the searchresults in another form.
In that form the user can select the files and then have the options to
print out selected files or deleting selected files.

I suspect that your program code should be able to find the right files
without the user having to search for them.

I know that this is an advanced question, and this is perhaps impossible to
do, but I´m hoping.

Um, it is far from advanced, and it is almost certainly possible!

HTH,
TC

 

[TC]

Hmm, on rereading your post, and my reply, I think that I should
emphasize this:

The key problem is the fact that you are identifying suppliers by name.
Inevitably, you are getting different names (Smith And Sone, Smith &
Sons, etc.), and so the files are going in various folders & the users
are not sure what those folders are called. Hence the need to do a
wildcard search.

Toi fix this, you need to identify each supplier by a unique, probably
numeric, Supplier ID, as I suggested before. Then you need to review
the business proces by which the Excel files are generated. You need to
name those files with the relevant supplier's Supplier ID - /not/ with
their name.

Then, you could write code to identify each Excel file and process it
as necessary.

HTH,
TC

 

[Guest]

Thanks for the long reply.

I will explain our routine a little better.
The buyer lays an order in one system.

Another person creates an Excelfile containing alot of mathematical
calculations.
She then saves it in a folder (C:\Supplier\Suppliername_1234.xls)
The suppliername is usually the name stated with the order, but not always.

I put in all information about the shipment in Access.
This information is given to me by the supplier.
For this reason, some "mix-ups" can occur.

Access do not generate these Excelfiles or determine where they should be
placed.
This is done by the one who creates them.
I guess I can make her change the filenames to what I need.

In Access, I have a main suppliername, and an aliasname for those suppliers
who use 2 or 3 names.
Say we place an order at companyname 1.
The invoice states companyname 2.
The freight manifest states companyname 3.
This is due to quotas in the country we buy from.
In Access they do have a unique ID.
This ID is not known by the person creating the Excelfiles.

When the goods arrive, I need to print out the Excelfile.

I want Access to find these Excelfiles (Based on the ordernumbers in the
subform), Show the filenames in another form, allow the user to select the
files for print, and then delete if wanted.

Since this is actually a very big company with over 100+ shops, I have just
created my ADB to handle the working duties for my dept.
It has helped me and my workingfriends alot since the company never would
allow changes in our "real" DB for luxury things like this.

BR
Claes

"TC" skrev: