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]

http://mavenhuffman.com/michael/Agent Call Reporting.mdb

There is the database itself. I am attaching it in hopes that someone can help me with it? Maybe I am leaving something out - so a visual would be of better help?

 

[mdmilner]

You may not have all your references set.
Did you copy all the code in your prior link into the new database?

This short snippet references all that code.
This section below is a *change* the RecallFileLocation Function

    MyDefault = FindDefaults("DefaultOpenLocation") 
      
    RecallFileLocation = ahtCommonFileOpenSave(InitialDir:=MyDefault, _ 
        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _ 
        DialogTitle:="Find File to Open!")

And you'll have to add the FindDefaults Function plus the table that it's referencing.

 

[Maven4Champ]

You may not have all your references set.
Did you copy all the code in your prior link into the new database?

This short snippet references all that code.
This section below is a *change* the RecallFileLocation Function

    MyDefault = FindDefaults("DefaultOpenLocation") 
      
    RecallFileLocation = ahtCommonFileOpenSave(InitialDir:=MyDefault, _ 
        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _ 
        DialogTitle:="Find File to Open!")

And you'll have to add the FindDefaults Function plus the table that it's referencing.

Wait do you mean the code here: http://www.mrexcel.com/board2/viewtopic.php?t=85521&highlight=file+open

 

[mdmilner]

Yes - and I just noticed that was my 'modified' version already. You just need the FindDefaults and ImportExport Functions added to it plus the click event that calls all of them.

Oh, and before I forget, the table that FindDefaults looks at to find the starting path.

I hate to do this, but, there is another approach that you could do.
If it's the same location each and every month. Make a table with the path to the files and instead of opening the FileOpen Dialog window, just have the program work thru the list one by one to import everything.

Mike

 

[bat17]

Just need to check a couple of things,

will the xls files be named after the date you will have on the form and in a directory with the agents code?

If so you should be able to open them without using the dialog.

If in your form you have agent ALG and date 5 June 2004 does this mean that you will be looking for a file

..../ALG/050604.xls where ... would be a common root path?

if so you could probably automate it to do the whole list of agents in one hit.

I don't have time to play with it tonight but will try to knock up a sample database in the morning (UK Time).

If you could put up a small sample xl file that you want importing that would help as well.

Peter

 

[Maven4Champ]

Just need to check a couple of things,

will the xls files be named after the date you will have on the form and in a directory with the agents code?

If so you should be able to open them without using the dialog.

If in your form you have agent ALG and date 5 June 2004 does this mean that you will be looking for a file

..../ALG/050604.xls where ... would be a common root path?

if so you could probably automate it to do the whole list of agents in one hit.

I don't have time to play with it tonight but will try to knock up a sample database in the morning (UK Time).

If you could put up a small sample xl file that you want importing that would help as well.

Peter

That is exactly it actually.

The following directories will be:

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 the Excel files will be named mmddyy.xls accordingly, in each directory.

I have attached the file: AW022504.xls. I put the AW infront just so you could identify which user ID it was for. The typical naming convention is as stated above. http://mavenhuffman.com/michael/AW022504.xls

Also, if you want to, download my database http://mavenhuffman.com/michael/Agent Call Reporting.mdb you can see where I am at with it. Now, I already have the module created from Mike's help but I think it has errors in it because it errors out each time - not sure why?

But doing it your way, will I still have a way where the data I import from the excel spreadsheet will also fill the Agent ID and date that way as well?

Sorry if I am making no sense - in fact I am sort of confusing myself.

I have virtually NO VB knowledge whatsoever, and created modules within Access is new to me - I usually only needed it for basic tables and databases because query-wise, it has one up on just putting the stuff in Excel. I have come along way in just a week now and can really use all the help I can get.

I can do it myself but at this point, I need like step-by-step instructions (step 1: create new module>step 2: paste the following>step 3: edit the following - replacing xxxx with your value) etc.

- Mike I was feeling your way. I put the code into a module and created a new form and put that function under the OnClick but it kept giving me the same errors no matter what. With all those functions, I am just getting myself lost.

 

[mdmilner]

You not figuring it out is just as much my fault as it is anybody's.
I didn't explain it clearly, and I volunteered information that complicated the answer unnecessarily.

I think I have samples of what you're looking for but I'm guessing I'll have to package it rather than explain the methods.

Questions we'd have about the above are:
You listed 4 sample directories where files will be found.

Are those the only directories to look in?
Will there be multiple files, only the most recent to grab (within each folder) or can you grab all files within that folder?


Good luck, not sure how much time I'll have in the next few days to offer comments.

Mike

 

[Maven4Champ]

You not figuring it out is just as much my fault as it is anybody's.
I didn't explain it clearly, and I volunteered information that complicated the answer unnecessarily.

I think I have samples of what you're looking for but I'm guessing I'll have to package it rather than explain the methods.

Questions we'd have about the above are:
You listed 4 sample directories where files will be found.

Are those the only directories to look in?
Will there be multiple files, only the most recent to grab (within each folder) or can you grab all files within that folder?


Good luck, not sure how much time I'll have in the next few days to offer comments.

Mike

Those "for now" would be the only directories that needed to be looked in. So a way to only import from them would be useful but not necessary. A simple BROWSE feature would work in this case. As far as which files to grab - initially you might have to grab files as much as 20-25 days old but once all the data is in, it would be a matter of importing the newest file that day, or the newest 5 files at the end of the week (for each user that is).

I followed with the MSKB article: http://support.microsoft.com/?kbid=824272 but I only have reference to the 10.0 Object database so it won't work. So I found another site http://forums.aspfree.com/showthread.php?t=29599 pretty much explaining your method but again I got some errors that I could not figure out.

I feel like I am on the brink of coming through on this but not quite. With a BROWSE way, a TransferSpreadsheet way, etc. its tough to know what to concentrate my efforts on when in the end, I don't know which will be best for me - not to mention even once I figure out this import problem, I still have to figure out how to basically have the Agent ID and the date they select auto-fill the blank columns for the data they just imported, whether I have to create an APPEND Query or not...

I have stayed at this till 12:00am this morning and I am hitting it hard again today since 10:00am and I never stop at anything till I figure it out - and I won't with this either...there is just this level of frustration that comes from my own ignorance really.

Now I am getting this error:

and I believe it is because I don't have the MS Office Object 11.0....only 10.0?

 

[Maven4Champ]

Ok I modified it back some more. And inserted the onclick code you told me to enter and I get the following error when I open the Form in Form View

Hmmmmmm?

 

[mdmilner]

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