How do I make sure the user opens the right file? 1

[ErikZ]

Ok, I'm happy with the database right now. You select the Excel file you want to load, and everything works great after that.

Except now I need to plan on user error. My database will take any excel file and load it into a new table and then try to do operations on it. How do I make sure the correct file is loaded?

The name of the file changes from day to day.
The position of the file can change also
The type of file stays the same. (Excel)
and the columns wll stay the same.

 

[Maquis]

Hmmm, will the file have a date in one of the fields that you can verify against? If the date in the file isn't what expect you can prompt the user. Maq B-)
<insert witty signature here>

 

[ErikZ]

The file is coming from a warehouse. Every once in a while they send this to my user. My user is supposed to compare the serial number column against the main inventory list and mark on the main inventory list where they match.

Hard for humans, way easy for Access.

Anyway, the same warehouse ALSO sends an inventory update file, which will be appended to the main inventory list. This file contains MANY of the same columns.

I'm leaving nothing to chance, I KNOW that if I let them, they'll end up loading the &quot;Fantasy Football&quot; excel sheet into my database. Hmm, maybe I can have Access check to see if each column is in the file? I don't remember seeing this in my books, is this possible?

 

[DjangMan]

You could have your Access application open up the Excel spreadsheet via VBA and look for the things that only the correct sheet would have. If you find it you then let Access open up the sheet in the normal way you've already coded.

DjangMan

 

[LonnieJohnson]

Right after you import the file you could iterate thru the fields(columns) to see if a unique field name exist.

If the field DOES NOT exist, then delete table and prompt user.
If the field does exist, then do your thing.

Iterattion process:

Private Sub CheckName()
Dim db As Database
Dim rs As Recordset
Dim fld As Field
Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;YourTableName&quot

For Each fld In rs.Fields

If fld.Name = &quot;UniqueFieldName&quot; Then
Call YourProcedure()
Exit Sub
End If

Next fld

Msgbox &quot;Not the right table&quot;
DoCmd.DeleteObject acTable, &quot;YourTableName&quot;

End Sub

ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[ErikZ]

Ok, now for the second file. It keeps the same name, so I can use filter to only give the user one option. The only problem is when the user hits &quot;Cancel&quot; the common dialog crashes out with a &quot;32755 Error&quot;.

Man, ActiveX information is hard to find!

 

[ErikZ]

Oops. The extra stuff that is normally added to a button WASN'T because I didn't use the wizard. Adding:

On Error GoTo Err_CmdOpen_Click

Err_CmdOpen_Click:
MsgBox Err.Description
Resume Exit_CmdOpen_Click

Exit_CmdOpen_Click:
Exit Sub

Fixed it.