Results 1 to 9 of 9
  1. #1
    redpanda is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    12

    Access Import from Excel Overwrite

    I am trying to import excel files from a specified folder into an Access database on a daily basis to update the tables. I found some code online that does the trick, however, it imports the new data to the table but instead of overwriting the data in the table it adds the data to the table so with each import it adds another copy of all the data. Below is the code I am using, I have been searching online for a solution but haven't had any luck... All help is appreciated. Thank you!


    Sub Import_Excel_Archive_All()
    'Import Data from All Worksheets in All EXCEL Files in a single Folder into Separate Tables via TransferSpreadsheet (VBA)
    Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
    Dim intWorkbookCounter As Integer
    Dim lngCount As Long
    Dim objExcel As Object, objWorkbook As Object
    Dim colWorksheets As Collection
    Dim strPath As String, strFile As String
    Dim strPassword As String
    ' Establish an EXCEL application object
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
    Set objExcel = CreateObject("Excel.Application")
    blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0

    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = True


    ' Replace C:\MyFolder\ with the actual path to the folder that holds the EXCEL files
    strPath = "C:\Users\Scott\Documents\My Dropbox\Fundies DataBase\Fundies Archives Daily Update\"
    ' Replace passwordtext with the real password;
    ' if there is no password, replace it with vbNullString constant
    ' (e.g., strPassword = vbNullString)
    strPassword = vbNullString
    blnReadOnly = True ' open EXCEL file in read-only mode
    strFile = Dir(strPath & "*.xlsx")
    intWorkbookCounter = 0
    Do While strFile <> ""
    intWorkbookCounter = intWorkbookCounter + 1
    Set colWorksheets = New Collection
    Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , _
    blnReadOnly, , strPassword)
    For lngCount = 1 To objWorkbook.Worksheets.Count
    colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
    Next lngCount
    ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
    objWorkbook.Close False
    Set objWorkbook = Nothing

    ' Import the data from each worksheet into a separate table
    For lngCount = colWorksheets.Count To 1 Step -1
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "" & colWorksheets(lngCount), _
    strPath & strFile, blnHasFieldNames, _
    colWorksheets(lngCount) & "$"

    Next lngCount
    ' Delete the collection
    Set colWorksheets = Nothing
    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    ' Kill strPath & strFile
    strFile = Dir()
    Loop
    If blnEXCEL = True Then objExcel.Quit
    Set objExcel = Nothing

    End Sub

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Instead on importing your excel spreadsheet to Access, have you considered linking it. Then it will be updated automatically whenever there are updates to the spreadsheet.

    msaccesstips.com/2008/08/opening-excel-database-directly/

    Alternatively, you could add a line of code to your current VBA at the beginning to delete the existing table.

    Alan

  3. #3
    redpanda is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    12
    I will look into linking it. The spreadsheets it is linked to probably total ~250 mb, would this cause the database to run slower as it would always be checking for updates? I don't want to delete the tables because then I will loose how they are grouped... Thank you for your reply.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    FYI, linked spreadsheets run slower than imported ones.

  5. #5
    redpanda is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    12
    Is there a way to clear the contents of a table? I could do that before the import which would allow me to retain the table. Due to the nature of the data appending the table is not an option.

  6. #6
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Look at this thread for a couple of options.

    http://www.dbforums.com/microsoft-ac...using-vba.html

    Alan

  7. #7
    redpanda is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    12
    Thank You!

  8. #8
    redpanda is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    12
    I am still having trouble with this. I am using,

    DoCmd.DeleteObject acTable, "AEP DAP"
    but it deletes the entire table but I need it to just delete the contents of the table.

  9. #9
    redpanda is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    12
    Got it! Thank you for the help!

    Function DeleteTable()

    DoCmd.SetWarnings False

    DoCmd.RunSQL "DELETE * FROM [BGE DAP];"

    DoCmd.SetWarnings True

    End Function

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

Similar Threads

  1. import excel file to access 2003 yes, access 2007 no
    By stapik in forum Import/Export Data
    Replies: 9
    Last Post: 07-27-2011, 07:09 AM
  2. Import Excel into access does not work
    By hawg1 in forum Import/Export Data
    Replies: 1
    Last Post: 05-28-2010, 12:05 PM
  3. XML File Import To Access...Overwrite Issue
    By wipers123 in forum Import/Export Data
    Replies: 0
    Last Post: 02-11-2010, 12:27 PM
  4. Import excel sheets to access.
    By calexandru in forum Import/Export Data
    Replies: 0
    Last Post: 08-19-2009, 09:44 AM
  5. Import and overwrite?
    By Ben in forum Import/Export Data
    Replies: 3
    Last Post: 08-11-2009, 08:43 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