Results 1 to 3 of 3
  1. #1
    pnpez is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    18

    Access VBA to add records to already existing table

    Hello,
    I am a novice but copied this code and it works fine. But I am importing data from excel to access. I want the excel data to actually add to already existing table structure, not put a new table there every time. Can someone help me fix this:

    Function ImportInternal()


    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

    ' Use actual path to the folder that holds the EXCEL files
    strPath = "F:\Data"

    ' 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, acSpreadsheetTypeExcel12, _
    "Internal_" & 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 Function

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    please make use of the code tags to preserve indentation. But suggest you google 'transferspreadsheet' to see what the options are for parameters

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Might also see Ken Snell's site

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

Similar Threads

  1. Importing records into an existing table
    By Jamescdawson in forum Import/Export Data
    Replies: 8
    Last Post: 05-18-2015, 04:05 PM
  2. Replies: 5
    Last Post: 02-21-2014, 07:55 PM
  3. Replies: 2
    Last Post: 05-21-2012, 08:46 PM
  4. Access 2007 - Creating New Records Based on Existing Records
    By GeorgeAugustinePhillips in forum Forms
    Replies: 9
    Last Post: 10-05-2011, 05:58 PM
  5. Adding records to existing table
    By Mclaren in forum Programming
    Replies: 5
    Last Post: 05-18-2011, 12:44 PM

Tags for this Thread

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