Uploading Excel

T

thesuggs76

Board Regular
Joined
Nov 15, 2006
Messages
247
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
I rather like Joe's solution better than mine, but since you asked, here is what I have used in the past.

Code: 
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.

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
ACCESS suitability for multiple column "TEMPLATE" concept
Replies
4
Views
399
heathball
H
C
  • Question
VBA code for Next button to move to each record in Textbox1
Replies
2
Views
214
welshgasman
welshgasman
S
  • Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
860
Micron
M
S
  • Solved
Export Access Table data to Excel - DoCmd.TransferSpreadsheet function
Replies
3
Views
649
alansidman
A
K
  • Question
Help repairing a Query Expression show records by a specified year
Replies
1
Views
217
Micron
M

Forum statistics

Threads
1,215,368
Messages
6,124,521
Members
449,169
Latest member
mm424

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top