Results 1 to 8 of 8
  1. #1
    zahin is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    8

    Import excel file into specific multi table using command button

    Hi!



    I am newbie here. Sorry if I post at the wrong thread.
    I have a database with multi-table namely (with many fields in each table);
    Table 1: Personal Details_tbl
    Table 2: Assets_tbl
    Table 3: Liabilities_tbl

    And I have excel.xls file with multi-sheet namely;
    Sheet 1: Personal_Details_tbl
    Sheet 2: Assets_tbl
    Sheet 3: Liabilities_tbl

    How do I import data from excel.xls file and overwrite/ update data in access table by using command button. Perhaps, I can put vba codes when I click the command button. Thank you all!

  2. #2
    zahin is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    8
    I was forgot to update that i need to upload the emailed excel.xls datasheet to the database when client email to me the excel file.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Do you want to replace all the records in tables? Or do you want to add new records and edit others based on data in the spreadsheets? Are these 3 tables related? What are the PK/FK fields?

    What you want can be automated, even pulling the attachment from email, with code but will be complex. Do you understand VBA programming? Know anything about programming concepts?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    zahin is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    8
    Quote Originally Posted by June7 View Post
    Do you want to replace all the records in tables? Or do you want to add new records and edit others based on data in the spreadsheets? Are these 3 tables related? What are the PK/FK fields?

    What you want can be automated, even pulling the attachment from email, with code but will be complex. Do you understand VBA programming? Know anything about programming concepts?
    Hi June7,

    Thank you for your immediate respond. My VBA programming knowledge is just a basic knowledge but I am willing to learn.

    1) Yes. I want to add new record and edit others based on data in spreadsheet.
    2) All tables in access are not related to each other.
    3) Primary Key of the fields is ID

  5. #5
    zahin is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    8
    I've tried some code.. but this code Import only sheet 1 and created new table.

    'Here are the codes:



    Dim i As Integer
    Dim zXLFPath As String
    Dim zXLFName As String
    Dim iFileType As Integer
    'Requires reference to Microsoft Office 14.0 Object Library Office 2010.
    'Note: Returns a fully qualified filename, e.g. d:\path\filename.ext


    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim zCurDir As String


    'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
    'Allow user to make multiple selections in dialog box
    .AllowMultiSelect = True
    .Title = "Please select the file to upload"


    'Clear out the current filters, and add your own.
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls"
    .Filters.Add "Access 2007-10", "*.xlsx"


    'Set the initial directory using passed argument string
    .InitialFileName = "C:\Users\User\Desktop\NWV279data.xls"


    'Show the dialog box. If the .Show method returns True, the
    'user picked a file. If the .Show method returns
    'False, the user clicked Cancel.
    If .Show = True Then
    For i = 1 To .SelectedItems.Count
    zXLFPath = .SelectedItems(i)
    Debug.Print zXLFPath
    If UCase(Right(zXLFPath, 1)) = "X" Then
    iFileType = acSpreadsheetTypeExcel12Xml
    Else
    iFileType = acSpreadsheetTypeExcel12
    End If


    DoCmd.TransferSpreadsheet acImport, iFileType, "Personal Details_tbl", zXLFPath, True
    Next i
    End If
    End With

    Dim i As Integer
    Dim zXLFPath As String
    Dim zXLFName As String
    Dim iFileType As Integer
    'Requires reference to Microsoft Office 14.0 Object Library Office 2010.
    'Note: Returns a fully qualified filename, e.g. d:\path\filename.ext

    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim zCurDir As String

    'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
    'Allow user to make multiple selections in dialog box
    .AllowMultiSelect = True
    .Title = "Please select the file to upload"

    'Clear out the current filters, and add your own.
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls"
    .Filters.Add "Access 2007-10", "*.xlsx"

    'Set the initial directory using passed argument string
    .InitialFileName = "C:\Users\User\Desktop\NWV279data.xls"

    'Show the dialog box. If the .Show method returns True, the
    'user picked a file. If the .Show method returns
    'False, the user clicked Cancel.
    If .Show = True Then
    For i = 1 To .SelectedItems.Count
    zXLFPath = .SelectedItems(i)
    Debug.Print zXLFPath
    If UCase(Right(zXLFPath, 1)) = "X" Then
    iFileType = acSpreadsheetTypeExcel12Xml
    Else
    iFileType = acSpreadsheetTypeExcel12
    End If

    DoCmd.TransferSpreadsheet acImport, iFileType, "Personal Details_tbl", zXLFPath, True
    Next i
    End If
    End With
    Dim i As Integer
    Dim zXLFPath As String
    Dim zXLFName As String
    Dim iFileType As Integer
    'Requires reference to Microsoft Office 14.0 Object Library Office 2010.
    'Note: Returns a fully qualified filename, e.g. d:\path\filename.ext

    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim zCurDir As String

    'Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
    'Allow user to make multiple selections in dialog box
    .AllowMultiSelect = True
    .Title = "Please select the file to upload"

    'Clear out the current filters, and add your own.
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls"
    .Filters.Add "Access 2007-10", "*.xlsx"

    'Set the initial directory using passed argument string
    .InitialFileName = "C:\data.xls"

    'Show the dialog box. If the .Show method returns True, the
    'user picked a file. If the .Show method returns
    'False, the user clicked Cancel.
    If .Show = True Then
    For i = 1 To .SelectedItems.Count
    zXLFPath = .SelectedItems(i)
    Debug.Print zXLFPath
    If UCase(Right(zXLFPath, 1)) = "X" Then
    iFileType = acSpreadsheetTypeExcel12Xml
    Else
    iFileType = acSpreadsheetTypeExcel12
    End If

    DoCmd.TransferSpreadsheet acImport, iFileType, "Upload_tbl", zXLFPath, True
    Next i
    End If
    End With

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The Range argument of TransferSpreadsheet can be used to specify other sheets.

    However, this method will not allow you to edit existing records based on the data in spreadsheet. It will just append records to table if table already exists or create a new table if it doesn't.

    For updating records set a link to the spreadsheet or import to a 'temp' table' and use that dataset as source for UPDATE sql.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    zahin is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    8
    Quote Originally Posted by June7 View Post
    The Range argument of TransferSpreadsheet can be used to specify other sheets.

    However, this method will not allow you to edit existing records based on the data in spreadsheet. It will just append records to table if table already exists or create a new table if it doesn't.

    For updating records set a link to the spreadsheet or import to a 'temp' table' and use that dataset as source for UPDATE sql.
    Thank you June7,

    I really appreciate your help. Perhaps:
    1) how can i set a link to the spreadsheet
    2) or import to a temp table to use dataset as source for update sql.

    is there any code that I can try with? Thank you June7.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    TransferSpreadsheet does have an option to set a link. It is set with the first argument. Instead of acImport or acExport, choose acLink.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-15-2013, 08:33 AM
  2. Import Specific cells from Excel based on selected file.
    By Only4Access in forum Programming
    Replies: 5
    Last Post: 02-29-2012, 02:32 AM
  3. create a button for import excel file
    By tggsun in forum Forms
    Replies: 3
    Last Post: 01-17-2012, 08:40 AM
  4. Replies: 11
    Last Post: 06-02-2011, 10:21 AM
  5. Disabling Excel import to a specific table
    By JoshD in forum Import/Export Data
    Replies: 0
    Last Post: 04-15-2011, 02:26 PM

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