Data Import question

[LJG]

I have an import routine that imports files from various members, by looking
at a tblUsers, However the file now need to include a ref number.

This reference number does not belong to any table but each file will have a
different ref number and I need a way of running the import including this
number i.e.

Appointment_116_Joe_bloggs.xls
Appointment_399_Joe_bloggs.xls
Appointment_1016_Joe_bloggs.xls
Appointment_31809_Joe_bloggs.xls etc

the random number could go up to 5 digits in length

My current function is:

Function newAppointImport()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSS As String

If MsgBox("You are about to import a new appointment, " & _
"Do you wish to continue?. ", vbYesNo) = vbYes Then
DoCmd.SetWarnings False

If MsgBox("New appointment about to start, " & _
"Do you wish to continue?.", vbYesNo) = vbYes Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tblUser") 'the name of the table
rst.MoveFirst
Do Until rst.EOF
strSS = "C:\database\dataimports\Appointment_" _
& rst.Fields("User Name") & ".xls" 'the name of the field
If Dir(strSS) <> "" Then
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects01", _
strSS, True, ""
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Call MsgBox("Import Completed", vbInformation Or vbDefaultButton1,
Application.Name)
End If
End If
End Function

Any suggestions would be most welcome

TIA
Les

 

[Guest]

Will you have any way of knowing what those references are before you run
your import? Also, How will you know whether the spreadsheet has already
been imported?

Also, there is a potential problem in your function.

Set rst = db.OpenRecordset("tblUser") 'the name of the table
rst.MoveFirst
Do Until rst.EOF

As written, if there are no records in your table, you will throw an error
when you do the move first. It could also get an error moving through the
recordset if it has not had time to fully populate. I would suggest you
change to above to this:

Set rst = db.OpenRecordset("tblUser") 'the name of the table
If rst.Record Count = 0 Then
MsgBox "No Records To Process"
Exit Sub
End If
rst.MoveLast 'Causes the recordset to fully populate
rst.MoveFirst 'Goes back to the beginning to start the processing
Do Until rst.EOF

Post back with info on the reference numbers so we can take care of that.

 

[LJG]

Hey Klatuu,

No, each reference is the ID for a call note created to pass to field
members. I need these to create unique appointment spreadsheets for each
appointment. Each spread sheet is a single record for an appointment and
notes related to that appointment. For each spreadsheet I will be running a
add/update query.

Thanks for the advice on my current code, will make those changes as
suggested.

Les

 

[Guest]

I believe using the Dir function will get you what you want. VBA Help has
good examples on using this function. I have put some suggestions in your
code. They are not tested, so you may have to tweek them a bit.

Notice the change in this line:
strSS = "C:\database\dataimports\Appointment_" _
& "*" & rst.Fields("User Name") & ".xls" 'the name of the
field

You will want to put the * where ever you expect the reference number to be
in the name. This will then return all file names in the directory for the
selected user.


One other thing I noticed. Your fuction returns no value. If you do not
need to return a value, I would suggest making it a Sub. Doesn't really
matter, but as a practice, when I see a function, I expect it is to return a
value and when I see a sub, I expect no return. Not technically required,
but it helps me remember what I was doing.

Function newAppointImport()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSS As String
Dim strTransfer As String

If MsgBox("You are about to import a new appointment, " & _
"Do you wish to continue?. ", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
Set db = CurrentDb
Set rst = db.OpenRecordset("tblUser") 'the name of the table
If rst.Record Count = 0 Then
MsgBox "No Records To Process"
Exit Sub
End If
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
strSS = "C:\database\dataimports\Appointment_" _
& "*" & rst.Fields("User Name") & ".xls" 'the name of the
field
strTransfer = Dir(strSS)
Do While strTransfer <> "" ' Start the loop.
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects01", _
strTransfer, True, ""
strTransfer = Dir
Loop
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Call MsgBox("Import Completed", vbInformation Or vbDefaultButton1,
Application.Name)
End If
End Function

 

[LJG]

Thanks for that Klatuu, will try this suggestion and let you know how I get
on, will post back in a couple of days

Thanks
Les