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]

Here is sort of a breakdown since no one has replied:

What I would need is like a form window to open...

Inside are three fields basically
----------------------------------------------------------

Agent ID: [________________]
Date: [__/__/_____]

File: ______________ [Browse]

[IMPORT NOW]
----------------------------------------------------------
What that would do, if possible, is import the .xls spreadsheet. Now because the spreadsheet does not have the Agent ID field or Date field, the Agent ID and Date put above would auto-fill so to speak in the black cells of their respective columns, along with the data you just imported.

Make sense?

 

[bat17]

Nice easy questions for a change

Adding Date/Agent ID should not be too much of a challange as a normal update query should be able to filter out records with empty fields and update them.

Importing the xls sheet should be OK as long as it is the same format all of the time.

Getting the file name will be more of a challange as Access VBA does not have a open file dialog box! (at least A97, not sure about other versions)

What Version of Access are you using and do the xls files have a standard format?

Peter

 

[Maven4Champ]

Nice easy questions for a change

Adding Date/Agent ID should not be too much of a challange as a normal update query should be able to filter out records with empty fields and update them.

Importing the xls sheet should be OK as long as it is the same format all of the time.

Getting the file name will be more of a challange as Access VBA does not have a open file dialog box! (at least A97, not sure about other versions)

What Version of Access are you using and do the xls files have a standard format?

Peter

Access 2000. The spreadsheets format is mmddyy.xls. Each eagent will save his/her daily .xls sheets in their own folder. I would then import from the respective folder. This process, of course, could be changed if need be.

And I was thinking of a listbox for the Agent ID so that eleminates the risk of error of someone else doing it and putting in the wrong ID.

 

[mdmilner]

File Open dialog Box

http://www.mrexcel.com/board2/viewtopic.php?t=85521&highlight=file+open

Think original link is at (which is a great resource btw)

http://www.mvps.org/access/_vti_bin/shtml.exe/search.htm

What you'll want to do is alter a few lines to match your needs. This opens up a dialog at a default folder location. You'll probably want to open elsewhere.

A better approach might be this:

Create a table used as a list of file locations to import. Open the file and extract the path to the location and then insert that into a DoCmd.TransferSpreadsheet function.

Mike

 

[mdmilner]

Take a look at the help for TransferSpreadsheet.
That should help you figure out which field is what.
This happens to Access2K/Excel2K

My calls to this look like:

ImportExport("acImport", "tablename", strTLoc)

Public Function ImportExport(ByVal Ltype As String, ByVal Tname As String, _
                                 ByVal TLoc As String) As Long
Select Case Ltype:
    Case "acImport":  Ltype = 0
      'DoCmd.TransferSpreadsheet acImport, 8, Tname, TLoc, True, ""
    Case "acExport":  Ltype = 1
      'DoCmd.TransferSpreadsheet acExport, 8, Tname, TLoc, True, ""
    Case "acLink":    Ltype = 2
      'DoCmd.TransferSpreadsheet acLink, 8, Tname, TLoc, True, ""
End Select
DoCmd.TransferSpreadsheet " " & Ltype, 8, Tname, TLoc, True, ""

End Function

 

[bat17]

If the date you enter on the form is the same as the date on the file name you can use that to create the path to the file.

Add the path to each agents directory to the table with their ID's and then you can put it in a combo box as a hidden column.

As the spreadsheet wont have all of the required columns for your main table you will need to transfer to a temp table and then use an append query to add the data plus ID and date to your main table.

Peter

 

[Maven4Champ]

Thanks so much for your help guys...

I'm just a little lost as to how I start on this - do I just paste this code into a new module and then save?

And when needing to import for the next day, do I do it over again?

I've gotten this far and I don't want to give up on it now but I guess maybe someone with some patience might give me some step by step on how to proceed. I modified some of the code from that original thread to fit what I am doing but I might have missed something on it - not really sure.

Can anyone give a detailed walk-thru on this and how it will actually work within my existing table.

Here is what my code looks like: http://mavenhuffman.com/michael/newmodule.txt I was wanting it to import only .xls spreadsheets - not sure if that is the right way to do it or not. I didn't change the Filter settings beacuse I wasn't sure what to change to specifically. Also, how does this know where to import the information to? I didn't see it specify a table name, etc.

Is it just easier to skip that and do it another way or am I getting close?

 

[mdmilner]

The FileOpen Dialog gives you an interface to navigate to a given file. The Function returns the full path to the given file - and you can use that to initiate another action, such as a Custom TransferSpreadsheet function.

Try this:

Create a form and put this under the OnClick Event
And use the above functions

Sub btnDoIt_Click
Dim strFile, tblname As String

strFile = RecallFileLocation
tblname = Right(strFile,len(strFile) - InStrRev(strFile,"\"))
tblname = Left(tblname,InStr(tblname,".")-1) 
Call ImportExport("acImport", tblname, strFile)


End Sub

Tips, the code you already have - particularly the class declarations always must be at the top of a module. So leave them first. I have customized my own version of RecallFileLocation slightly.

Like this:

This uses a table to search for a value in a given field to figure out where to start my default location.

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

The result of the function call FindDefaults("DefaultOpenLocation") is this:

S:\AssignmentList\

For me. This (below) is the FindDefaults Function.
It uses a table called 'tblDefaults' and searchs in the field called "TypeOfDefault" and returns the value in "DefaultInfo"

If you make a similar table and name the fields appropriately, this will work.
You could put anything in there.

Public Function FindDefaults(ByVal MyDefaults As String) As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from tblDefaults", dbOpenSnapshot)

With rst
    .FindFirst "TypeOfDefault='" & MyDefaults & "'"
    If !TypeOfDefault = MyDefaults Then
        FindDefaults = !DefaultInfo
    Else
        MsgBox "Information Not Found"
    End If
End With

Set rst = Nothing
Set dbs = Nothing

End Function

 

[Maven4Champ]

The FileOpen Dialog gives you an interface to navigate to a given file. The Function returns the full path to the given file - and you can use that to initiate another action, such as a Custom TransferSpreadsheet function.

Try this:

Create a form and put this under the OnClick Event
And use the above functions

Sub btnDoIt_Click
Dim strFile, tblname As String

strFile = RecallFileLocation
tblname = Right(strFile,len(strFile) - InStrRev(strFile,"\"))
tblname = Left(tblname,InStr(tblname,".")-1) 
Call ImportExport("acImport", tblname, strFile)


End Sub

Ok I created a new form. Right clicked and went to BUILD EVENT. I chose EXPRESSION BUILDER. In the third pane, I double clicked "ONCLICK" then pasted the code you just gave me. I hit ok and it says "The Expression You Entered Contains Invalid Syntax. You may have entered an operand without an operator.