Results 1 to 11 of 11
  1. #1
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95

    Import from Excel Using TransferSpreadsheet

    I would just like to have a macro that imports the data from a single sheet spreadsheet into a table and replace the existing data (or just add the new data, either way would work).



    I figured out the syntax to export the data to excel, I just need to be able to import the same file into a updated version of the database.

  2. #2
    Join Date
    May 2010
    Posts
    339
    P5c768,

    Check out Ken Snell's web page on importing.
    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

    Lots of good stuff here.

  3. #3
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    Thanks, but I think that is more advanced than what I need. I'm fine with forcing the file to be located in the My Documents folder, I would just like to allow the user to choose their own name for the import file.

    This works with exporting the database, but when I export the data and immediately try to reimport, I get a message saying that access was unable to append the data and that all records were not added due to key violations. Do I need to add some code to delete the existign data first?

    Here is the code I am using right now:


    Private Sub Command4_Click()
    On Error GoTo Macro1_Err
    import_prompt = InputBox("Import file must be saved in the C:\Documents and Settings\My Documents folder. Enter the name of the file to import:", "Import Database", "My Data")
    import_check = MsgBox("Are you sure you want to import this file to the database?", vbYesNo, "Import Database")
    If import_check = vbYes Then
    DoCmd.TransferSpreadsheet acImport, 8, "Clients_tbl", import_prompt, True, ""
    MsgBox "Database imported to the C:\Documents and Settings\My Documents folder with the name " & import_prompt & ".xls"
    ElseIf import_check = vbNo Then
    MsgBox "The import has been cancelled"
    GoTo Macro1_Exit
    End If
    Macro1_Exit:
    Exit Sub
    Macro1_Err:
    MsgBox Err.Description
    Resume Macro1_Exit
    End Sub

  4. #4
    Join Date
    May 2010
    Posts
    339
    Hi,

    When you export your table the PK_ID (auto number) field goes into the spreadsheet, then when you try and import it back, you can get this type of error. One way to get around this is to use a query setup with only the fields you need leave out the auto number field. Just change your record source in your macro to your new query.

  5. #5
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    Are you talking about the row source for the transferspreadsheet property? I guess I'm not sure how to do that. It doens't appear to be a parameter in the property. Are you thinking of transfer database?

    Is this the best way to approach an import? The only reason I want this feature is to allow users to incorporate their data into updated version of the database that I distribute. I figured they could just export to excel and then import the same excel spreadsheet to a new version of the database. However I don't want to append the data but rather just replace any exisiting data.

    Would it be better to delete all the exisiting data before loading the new data? Or should I try and use an update query of some sort (although I don't know how that would work from excel). Thanks!

  6. #6
    Join Date
    May 2010
    Posts
    339

    Can you upload a copy?

    Can you upload a copy of your database with out sensitive info? You would need to convert it to access 2000 format.

    On the menu bar
    File -> tools -> database utilities -> convert database -> Access 2000
    Name it something different like sample, this will make a copy.

    I might be able to help you out.

    Richard

  7. #7
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    Thanks for the help, here is a simplified version of the database. I believe it's in the correct format, so let me know if it doesn't work.

  8. #8
    Join Date
    May 2010
    Posts
    339

    Sample

    Hi P5C768,

    Here is your sample back with a few tweaks...Unzip to your desktop.
    When you press the export button it will not give you a conformation it just exports to the current path. When you import a dialog box will open and you will then be able browse to the client.xls file probably on the desktop.

    You have a lot of fields for a contacts database you might consider rethinking your approach.

    I enclosed a picture of a contacts database I have been working on for my brother. It’s a MS template I reworked.

    -Richard-

  9. #9
    P5C768 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    Denver, CO
    Posts
    95
    Works like a charm! I do get a new table when I run the macro that is called Name Autocorrect save failures and has the faliure reason "Could not save the object." Is there any way to get the macro to ignore this error and not add the table? It doesn't seem to be a useful error message.

  10. #10
    Join Date
    May 2010
    Posts
    339
    Hi,

    I always turn off the autocorrect for the reason below.


    http://allenbrowne.com/bug-03.html


    Richard

  11. #11
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181
    What if you would like to only import specific worksheets from an Excel spreadsheet? I do not see worksheet or "tab" names as an option in this code:

    Code:
    Private Sub Command4_Click()    Dim strPathFile As String
    Dim strTable As String, strBrowseMsg As String
    Dim strFilter As String
    Dim blnHasFieldNames As Boolean
    
    
    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = True
    
    
    strBrowseMsg = "Select the EXCEL file:"
    
    
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
    strPathFile = ahtCommonFileOpenSave( _
          Filter:=strFilter, OpenFile:=False, _
          DialogTitle:=strBrowseMsg, _
          Flags:=ahtOFN_HIDEREADONLY)
    
    
    If strPathFile = "" Then
          MsgBox "No file was selected.", vbOK, "No Selection"
          Exit Sub
    End If
    
    
    ' Replace tablename with the real name of the table into which
    ' the data are to be imported
    strTable = "Clients_tbl"
    
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
          strTable, strPathFile, blnHasFieldNames
    
    
    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
        Kill strPathFile
        DoCmd.Requery
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cannot import excel or text files
    By donald_s in forum Access
    Replies: 2
    Last Post: 04-13-2010, 11:48 PM
  2. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 PM
  3. Replies: 2
    Last Post: 02-27-2010, 06:53 AM
  4. Import single cell from excel
    By zippy483 in forum Import/Export Data
    Replies: 9
    Last Post: 02-24-2010, 02:16 PM
  5. Import excel sheets to access.
    By calexandru in forum Import/Export Data
    Replies: 0
    Last Post: 08-19-2009, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums