Is it possible to create an .xls import form in Access?

[Maven4Champ]

Ok, Problem #4 on my database!

Each .xls speadsheet I import is metrics/statistics of one call rep on one certain day. Currently, I have no way to set this up where the Agent ID and the Date are automatically assigned to content I import.

I have to do one of two things as it stands now:

1. Open the .xls spreadsheet. Create two columns (one for Agent ID and one for Date). Then I enter the initials of the agent in the first row of the Agent ID column and drag down to copy. I do the same for the date. I then save and import into Access.

2. I import the data from the .xls spreadsheet - then open the Table. I find the rows of content w/out an Agent ID and Date (because we did not assign one in the .xls) and I then input them that way.

As it stands though, I might import up to 30 records for 1 agent for 1 day. So for four agents, five days a week - thats at least 450 records I would have to do that with.

Is there a way to create a form or module or something that does the following.

• Has an Agent ID field where you can input the ID.
• Has a Date field where you can input the date you are about to import records for
• Has an import button or get external data button or even just a browse button to locate the .xls document on your system.

Then a submit or import button that imports those records (how ever many there are) and assigns that Agent ID and Date you entered with the import earlier?

Is this possible or am I just dreaming?

 

[Maven4Champ]

That's Access 2002 - is that what you have?
I've modified my functions (so far) to allow you to specify a single folder and look for all folders within it -- and then open all that it finds.

This would allow for starting with this path:

S:\31630 Business Technology\Access-VB Projects\CentreVu\

And it would find:

S:\31630 Business Technology\Access-VB Projects\CentreVu\ALG\
S:\31630 Business Technology\Access-VB Projects\CentreVu\AW\
S:\31630 Business Technology\Access-VB Projects\CentreVu\MAK\
S:\31630 Business Technology\Access-VB Projects\CentreVu\MJM\

And then create a list of all files within those folders.

It would also find ANY/ALL other folders and files under the original CentreVu folder path.

At this point it does not discriminate between files - it just gets them all.


Mike

That sounds incredible...
At that point would we need a List Box next to it to allow you to select the files your about to import, or are we going to apply an .xls filter to it beforehand?

Actually I have Microsoft Access 2000 / running on Win2kPro.

I've continued to go back in and modify what I thought was wrong with the code and it gets worse and worse... I swear if this was Java or HTML or XML for that matter - I would be much better off. I am a webdesign and code - so my process carries over into this but clearly HTML does not translate into VisualBasic!!! lol

It's funny though because Microsoft's KB article states: Advanced: Requires expert coding, interoperability, and multiuser skills.

Guess I am out of my league?

O'well. I have until the 25th pretty much to finish it but I pray it doesn't take me that long.

 

[mdmilner]

Most recent code sample - all functions - sent to maven4champ & bat17 via PM.

Sorry guys, it's getting long and much of it is repeats of prior posts.
While I did test this, I pasted everything into WORD first to get everything together and it's possible that a syntax error crept into it.

As mentioned above. It walks thru a folder looking for subfolders. It then extracts all xls from those subfolders. Limiting to a subset of name (dates within file names) or even using the filesystemobject to only import spreadsheets changed recently is possible.

There's also the UPDATE query portion that bat17 was going to submit....which is why I sent him a copy if he'd like to collaborate and write something into the functions.

I have a feeling I just submitted a lot more than I should have but I guess it's sorta become a point of personal pride to submit a working answer. I might do more if I find time but I'm about to get slammed by the employer currently paying the bills.

Mike