Uploading Excel

[thesuggs76]

What is the easiest way to upload an excel file into a database so it overwrites data in a table. I want the user to be able to click on a button on a from and the data is updated with any changes to the spreadsheet. The database will be used for running queries.
I'm currently working on the VB, any help would be greatly appreciated.

Thanks

 

[alansidman]

The way I have handled this is to put a command button on the form that when executed deletes the existing table. Then I have a command button that when executed imports my data to a new table with the same name as the old table. In this manner, you don't have to change the existing queries as they are looking for data in a table that already is part of their set up. You could do it with one command button to do both steps if you think it would be easier for your users.

If you need specific examples of how to do this, post back.

Alan

 

[thesuggs76]

Hi thanks for replying, any examples would be greatly appreciated
Thanks

 

[Joe4]

I do something somewhat similar to Alan's, but a little differently.

Rather than re-create the table every time (because it might not automatically create the fields the way you like them and add indexes and primary keys, etc), I simply have a line of code that will delete the data in the table.

I often create a two step macro:

Step 1
Delete data in old table with RunSQL macro command. This is the code I use in that macro step:
DELETE [TableName].* FROM [TableName]

Step 2
Run TransferSpreadsheet macro command to import your new file to the table.

I actually have a few more commands in there, like SetWarnings to suppress the warnings that come up when you delete the data.

If your importing Excel file name varies where you cannot hard-code it into a Macro, you can create a macro like I described above, then convert your macros to visual basic (there is a menu option for that). You can then generalize your code and even have the file name link to a textbox that you enter at run-time (or browse to the file you want to import). You can then attach the VBA code to a command button on a Form to run it.

 

[alansidman]

I rather like Joe's solution better than mine, but since you asked, here is what I have used in the past.

Private Sub Command2_Click()
DoCmd.DeleteObject acTable, "tblName"
End Sub

I have used the following code to import multiple spreadsheets into multiple tables. This was coded for Excel 2002 and Access 2002. Also, the spreadsheets to be imported are residing on the desktop. You may need to change the path.

Private Sub Command1_Click()
'---------------------------------------------------------------------------------------
' Procedure : ImportXLSheetsAsTables
' Author    : as
' Date      : 1/16/2009
' Purpose   : To import all worksheets in a specified workbook into
'             individual tables in Access.
' Tables get names: Tbl_ + name of the worksheet
'
' NOTE: Must have a reference to the Microsoft Excel Object Library
'---------------------------------------------------------------------------------------
'
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String

   On Error GoTo ImportXLSheetsAsTables_Error

Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open("C:\Documents and Settings\" & Environ("UserName") & "\Desktop\FileName.xls")
For Each sh In wb.Sheets
Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_" & sh.Name, "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\FileName.xls", True, sh.Name & "!"
Next

wb.Close
appExcel.Quit

   On Error GoTo 0
   Exit Sub

ImportXLSheetsAsTables_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
End Sub

HTH
Alan