Better Way to Import from Excel

[Guest]

Hello again all,
I am trying to get Access and Excel to work a bit better together. In my
application, user's import data from excel spreadsheets. I use a line of
code to display an existing excel file I use as a template (template1).
User's paste the data from their mulitple sources and then do a SAVE AS to
save it as "import.xls". User closes excle doc and then clicks on the import
button in the application to import the "import.xls" file.
Is there a better way? Here is what I think may be a better way, but have
no idea how to make it happen.

In Access, User open template1 excel file (code stops), user pastes data to
template1.xls file, user clicks on a macro button in excel that saves doc as
import.xls file. User closes out of excel and Access code resumes (which
imports the import.xls file.

Any suggestions or hints on how to better accomplish this would be greatly
appreciated.

 

[George Nicholson]

Is there anything special going on in your excel template? If not (and maybe
even if there is), why not just create a table in Access to act as your
template? If there is no way around having your users pasting data into
something, have them paste it directly into Access. Then, append that data
to wherever it needs to go. When done (or at the start of the next
"import"), delete the records from your "template" so it's ready for new
data.

HTH,

 

[Guest]

George,
I am using Access 2002 (latest service pack)... critical info with the ISAM
errors.
Thank you for your suggestion. I am tried using an access form in datasheet
and continuous. Problem is when a user selects multiple rows of values in
one column, all of those value are pasted to one field in Access. The
spreadsheets the user uses are not in the same order (left to right) so they
can't just select the whole row and insert the whole row.... so... Here is
the solution I am trying. I tried to just run and update query to copy data
from temp1 to another table then delete the data in temp1 (excel linked doc),
but ran into the ISAM error which does not allow me to update/delete data in
a linked excel doc since I have Access 2002.

within Access, have 2 linked Excel documents. Temp1 - User adds data
and Temp2 - Always blank

Have a access form to open excel document (temp1), user pastes data in the
excel doc, clicks on a macro button in excel that will save and close temp1.
Use Access to copy data from Temp1 to a REAL access table.

The SNAG I am hitting is to blank out the temp1 linked excel doc. ISAM error.

I am using this code to delete temp1 object and copy temp2 object to temp1
object, however the record source for temp1 is still temp2, just the name has
changed. Can I somehow modify the data source of temp1 in linked table
manager with code? The code I saw online deals with verifying the connection
and if no connection is there, prompt the user.

Here is the code to delete and rename object.

Private Sub cmd_Clear_Click()
On Error GoTo Err_cmd_Clear_Click

DoCmd.SetWarnings False

DoCmd.DeleteObject acTable, "Temp1"
DoCmd.CopyObject "", "Temp2", acTable, "Temp1"

Me.temp_Upload.Requery

DoCmd.SetWarnings True

Exit_cmd_Clear_Click:
Exit Sub

Err_cmd_Clear_Click:
MsgBox Err.Description
DoCmd.SetWarnings True
Resume Exit_cmd_Clear_Click


End Sub