Linking Access worksheet and appending data to Access table

[Mkuria]

I have little knowledge of VB but this is what I am trying to accomplish.
I need to automate a process where a user links an excel file on their
machine and then runs an append query to update that data to an already
existing table.
I tried the transferspreadsheet however - because users store files on the
local drive - they would have to select the file location---
the second question is I can create an append query to run using linked file
but I would like to delete the linked file after the append query runs. By
deleting the link - the append query will not work next time.

I have copied Kens code OPen file API as a start but I am not sure how to
add it to the module. The linked file will alway be named - Full Project
Detail

DoCmd.TransferSpreadsheet acLink, 8, "Full Project Detail",
"C:\FolderName|" & ("Select the Excel file"), True, "A:BW"

Any insight on how to approach this issue is really appreciated.

 

[Jeff Boyce]

It sounds like you've decided that the solution to what you are facing is to
append data from Excel into one/more Access table(s).

If you'll describe what you are facing, folks here may be able to offer
alternate approaches...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MKuria]

thanks Jeff for responding.
I have created a database for one group of users to upload data and another
to update some fields (via forms). I have a BE and FE database. I need to
automate the Upload process. The data is on an excel spreadsheet, previously
I have had to reformat the excel spreadsheet due to conversion errors so I
thought if they linked to the file and run an append query that will work
better. Also the excel file is stored on their local drive so I need the
option for them to select the file.
I appreciate your responses.

 

[Jeff Boyce]

Are you saying that you use an upload-and-append process to clean up the
data?

Even if the Excel file is located on each user's local drive, you could
specify where that file must be located ("this application requires that the
Excel file you are processing is located in C:\xxx\yyy\, and is named
"ZZZZ").

Otherwise, if you give them the option of selecting a file, you'll also need
to somehow test whether they are selecting the correct type of file (not
Word, not PowerPoint, not ...) and that the haven't previously loaded that
file. Are you up to covering those possiblities too?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MKuria]

Jeff,
What I am trying to do is automate the way the new data is uploaded into
the database. Rep A has a file to update - I would like to create a command
button imports the file (to temp table) and runs and append query to upload
that data to the main table and delete tdata in he temp file. ( the append
query also add field to table which is compiled by concatenating four
fields). I
mmk