razorbackfan
asked on
Upload an Excel Spreadsheet into an Access 2010 table using a button on a form
I have a file "export.xlsx" that is imported on a daily basis to an Access 2010 table "FD Service Desk". I would like to create a form that has a button on it that once clicked, will upload the spreadsheet into the table. I can create the form and button, just not sure what coding is necessary to actually upload the file into the table.
ASKER
spreadsheet is uploaded but a new table is created each time i click the button. I need the data to be appended to the existing table.
you can do it by first
import the excel to a temporary table
docmd.transferspreadsheet acimport,10, "tmpTable", "C:\Folder\export.xlsx", true, "sheet1!"
then run an append query that appends the records from the tmpTable to "[FD Service Desk]"
something like this
Insert Into [FD Service Desk]
select tmpTable.*
from tmpTable
import the excel to a temporary table
docmd.transferspreadsheet acimport,10, "tmpTable", "C:\Folder\export.xlsx", true, "sheet1!"
then run an append query that appends the records from the tmpTable to "[FD Service Desk]"
something like this
Insert Into [FD Service Desk]
select tmpTable.*
from tmpTable
ASKER
I was hoping for a solution that only required one step to simplify the process
there is a one step solution, (not simple though)
by reading the excel file and appending to the table using vba.
just stay with the two step process.
by reading the excel file and appending to the table using vba.
just stay with the two step process.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
docmd.transferspreadsheet acimport,10, "[FD Service Desk]", "C:\Folder\export.xlsx", true, "sheet1!"