What I want.. what I really really want...

[Jason]

Ok... I think I have been getting side-tracked here..

I think the combo box solution would be beneficial only for reporting or viewing the lists

What I am hoping for is to be able to scan the directory for any and all .xls files, and have them linked into the mdb so that I may query them to produce results based off even XLS files that someone "just so happens" to export from the 3rd party app. The point of the matter is that I will need to query xls files that are being exported on a regular basis "without" having to manually link them each and everytime someone at a workstation exports one.

Theoretically, this can be done by scanning a directory using a function... I can base the query off of that result right

As far as populating the combo box, I think we are getting a bit ahead of ourselves on that one, do you agree

<slapping forehead in frustration> hehe.. thanks again.

 

[Guest]

Jason, I have about the same conflict as you, as I too
need to scan a directory and link/import them into
database for manipulation, viewing, and reporting. I also
need to check for changed files that are exported by 3rd
party app. If I find a solution I will be sure to post.
Good Luck in your search...

-----Original Message-----
Ok... I think I have been getting side-tracked here...

I think the combo box solution would be beneficial only

for reporting or viewing the lists.

What I am hoping for is to be able to scan the directory

for any and all .xls files, and have them linked into the
mdb so that I may query them to produce results based off
even XLS files that someone "just so happens" to export
from the 3rd party app. The point of the matter is that I
will need to query xls files that are being exported on a
regular basis "without" having to manually link them each
and everytime someone at a workstation exports one.

Theoretically, this can be done by scanning a directory

using a function... I can base the query off of that
result right?

As far as populating the combo box, I think we are

getting a bit ahead of ourselves on that one, do you agree?

 

[Jason]

I think I am close.. you might want to monitor the following post as well.. seems Toby is in need of a similiar solution. I am, however, very close.. and becoming MUCH more proficient at access programming. :sigh: Thanks again.<waiting for John!>

 

[Toby]

Jason check out my new posts. I think i have code in there
you can use.

 

[Jason]

~Hey hey.. I managed to locate the posts.. but I need some of the code to be checked for syntax, as its not working for me. =((

 

[John Nurick]

Hi Jason,

Linking to large numbers of arbitrary workbooks is guaranteed to cause
problems and confusion

If the data in these .xls files is of lasting importance and you want to
have access to it, you should import it into a table in the database
(with additional fields if required to record the file it came from or
the date it refers to or whatever).

Would you like to step back a bit and explain the situation - and what
you need to do with the data - in a bit more detail?

 

[Jason]

Hello John...

I could not agree with you more on the linking issue. Currently, (and since I cannot get the problem solved yet) I am only linking to one .xl file and basing queries off of that. I *really* want to be able to import data from several .xls files into one table... as opposed to merely linking them. There will end up being wayyyy too many linked tables if I don't!

Basically this is what happens now:

...xls files are exported from a 3rd party app. These files are exported at any given time during the day, each one has a unique filename (123-1234.xls, 133-1234.xls, 542-1243.xls, etc) and are exported to the directory of my choosing.

The data basically includes a jobID, partID, Description, and more.. these values never change, as the data goes from the 3rd party app to the .xls file via Crystal Reports. That data is then queried and a "master list" is generated via a report.

I am using an input device (barcode scanner) that allows us to scan items and bounce those scanned items off of the "master list" to produce 2 more reports: a "scanned" report (items on the master that *have* been scanned) and an "unscanned" report (items on the master that have yet to be scanned). The unique field in these operations are the JobID and ItemID fields.

I have this running fabulously fine as we speak. No problems whatsoever, with the exception of the applicatin being limited with only ONE .xls file. The way things are now, I have to have people export the .xls file to a unique filename so that the Access mdb can link the info. We need to be able to export an infinate amount of .xls files to work with, as we need to be able to work on more than one JobID at a time, as opposed to the current (1).

I want to be able to scan the said directory of all xls files and maybe append all of their data into one table. The query operations should not be a factor, as the jobID will forever be unique, and the reports could be generated simply based on that value alone.

I understand that I cannot simply "link" hundreds of xls files to the mdb programmatically, but I have absolutely NO idea on how to get the data from multiple files into one table without manually importing the data. Doing so is just NOT an option, as we have to allow for any given workstation to export a new dataset at any given moment. Only one person is using this access tool.

~I hope this explains everything adequately. I am so seriously stumped and pressed for time here, I think I am going to start bleeding out my ears. hehe. In any case I do appreciate any and ALL help on this matter. Thanks again!

-jason
928.855.4164

 

[Ted]

Hi Jason,

I think I have a better feel for what you are trying to
do now. I'm not sure if you have been able to get the
Application.Filesearch to work yet, but I think that will
be very important to do what you want to do (if anyone
knows of another way to generate a list of files in a
folder please let us know). I'm not sure if you saw my
other post, but check in the VB code window under
Tools|References to see if Microsoft Office Library is
checked - I think it needs to be to use
Application.Filesearch.

Anyway, if you can get this working, you could do what
you want to do as follows.

Run Application.Filesearch to generate the collection of
files meeting your criteria.

Loop through each of the files to do the following:
1. Import to a temporary table
2. Run an append query to append the record(s) to your
main table.
3. Go to the next file in the loop.

The Application.Filesearch should be the only part that
is somewhat tricky if all of your excel files that you
will be importing are in the same format (which it sounds
like they are). And, from looking at the earlier code
that you had posted, it looks like you have written most
of the code for it, if you can only figure out what is
causing your error.

I'm not sure how well you know the debugging tools in VB,
but they often help me figure out what is wrong. You
could set a breakpoint just before your error line, and
look at all of the values of the variables at that point
in the locals window, that may help locate the problem.

Maybe others will have some other ideas, but I thought I
would pass this along.

-Ted

-----Original Message-----
Hello John...

I could not agree with you more on the linking issue.

Currently, (and since I cannot get the problem solved
yet) I am only linking to one .xl file and basing queries
off of that. I *really* want to be able to import data
from several .xls files into one table... as opposed to
merely linking them. There will end up being wayyyy too
many linked tables if I don't!

Basically this is what happens now:

..xls files are exported from a 3rd party app. These

files are exported at any given time during the day, each
one has a unique filename (123-1234.xls, 133-1234.xls,
542-1243.xls, etc) and are exported to the directory of
my choosing.

The data basically includes a jobID, partID,

Description, and more.. these values never change, as the
data goes from the 3rd party app to the .xls file via
Crystal Reports. That data is then queried and a "master
list" is generated via a report.

I am using an input device (barcode scanner) that allows

us to scan items and bounce those scanned items off of
the "master list" to produce 2 more reports: a "scanned"
report (items on the master that *have* been scanned) and
an "unscanned" report (items on the master that have yet
to be scanned). The unique field in these operations are
the JobID and ItemID fields.

I have this running fabulously fine as we speak. No

problems whatsoever, with the exception of the applicatin
being limited with only ONE .xls file. The way things are
now, I have to have people export the .xls file to a
unique filename so that the Access mdb can link the info.
We need to be able to export an infinate amount of .xls
files to work with, as we need to be able to work on more
than one JobID at a time, as opposed to the current (1).

I want to be able to scan the said directory of all xls

files and maybe append all of their data into one table.
The query operations should not be a factor, as the jobID
will forever be unique, and the reports could be
generated simply based on that value alone.

I understand that I cannot simply "link" hundreds of xls

files to the mdb programmatically, but I have absolutely
NO idea on how to get the data from multiple files into
one table without manually importing the data. Doing so
is just NOT an option, as we have to allow for any given
workstation to export a new dataset at any given moment.
Only one person is using this access tool.

~I hope this explains everything adequately. I am so

seriously stumped and pressed for time here, I think I am
going to start bleeding out my ears. hehe. In any case I
do appreciate any and ALL help on this matter. Thanks
again!

 

[John Nurick]

Hi Ted and Jason,

If you know what folder the files to import are in - and I get the
impression that all Jason's xls files are being put into the one folder
- you don't need Application.Filesearch, which IMO is a pain anyway.
Just use the Dir() function to get the names as per the little sample I
posted in the "Jason & Ted - Linking" thread.

 

[Jason]

You are correct about the location to my xls files. I can simply *make* people export them in whatever directory I want. At this point, they are under my pointed finger. I was hoping you could give me your code snippet "for dummies" I am so beyond being able to think properly at this point. (Thank god for Ibuprofen) I mean, could you please have mercy on me and back up a bit to show me how to implement your solution
~thanks a ton

~jason

 

[Jason]

Yes, I need to MOVE these files to a subdirectory named "archives". Could someone please show me how to do this

Thanks again for all of your help.