Import and Modify data

[crownedzero]

I'm working on building a command button to import data into Access. I've tried some of the listed methods in the forums here and I must be missing something. I need to import an Excel spreadsheet into Access, I'd also like it to change all negative values in the "Available" column to 0.

Can anyone give me a "for Dummies" explanation of how to accomplish this?

Again, I know this is frequently referred to on the boards here, but I have yet to get one to work for me. If it matters I'm using Excel 2003.

[RuralGuy]

Can you achieve the import you want?

[crownedzero]

I have a update query to update negatives to zero, question is how do I add that to the button event procedures?

[RuralGuy]

Show us the Button Event procedure.

[crownedzero]

I think I got it figured out, it may not be the cleanest. If I wanted to update this to allow the user to choose which file to import how would I go about it?

I included a bit of code to close the table if it should be open but I've noticed it doesn't function like it should. If form button is clicked while the table is open the event will not run. Thoughts?

Private Sub Import_Data_Click()
 
    DoCmd.Close acTable, "TBL_Inventory"
    DoCmd.DeleteObject acTable, "TBL_Inventory"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_Inventory", "C:\Mydir\myfile.xls", True
    DoCmd.RunSQL "ALTER TABLE TBL_Inventory ALTER COLUMN Size Double"
    DoCmd.RunSQL "ALTER TABLE TBL_Inventory ALTER COLUMN Available Integer"
 
End Sub

[crownedzero]

Private Sub Import_Data_Click()
    
    On Error Resume Next
        
        DoCmd.Close acTable, "TBL_Inventory"
        DoCmd.DeleteObject acTable, "TBL_Inventory"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_Inventory", "C:\Mydir\myfile.xls", True
        DoCmd.RunSQL "ALTER TABLE TBL_Inventory ALTER COLUMN Size Double"
        DoCmd.RunSQL "ALTER TABLE TBL_Inventory ALTER COLUMN Available Integer"
    
End Sub

The update query I have written is called QRY_Negatives_To_Zero, any values <0 become 0.

[RuralGuy]

If I wanted to update this to allow the user to choose which file to import how would I go about it?

Here's the API I would use to let the user pick a file: http://www.mvps.org/access/api/api0001.htm

[RuralGuy]

I just put your two threads together. I was going around in circles until I figured out you had two threads on the same subject. Please do not do that. I'm going to take a break for a while now.