Results 1 to 3 of 3

Import excel sheet data into Ms-access using VBA macros

  1. #1
    gokul1242 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    5

    Import excel sheet data into Ms-access using VBA macros

    I have a excel file with three sheets named "A","B","C".
    I have four columns: ID,Name_S,Age,City in all the sheets.

    Now i want these data to be imported into a Ms-access database file.
    I want three tables with their names same as the Sheet names ie.,A,B,C and also i want their field names as the column names.
    I have used the following code to do this,but having few problems

    1.I want only three columns to be imported ie.,Column B,C,D in all the sheets
    2.I am not getting the field name as the column names i am getting field name as F1,F2 etc.,
    Code:
    
    
    Private Sub ImportXLSheets()
    
    Dim WrksheetName As String
    Dim i As Integer
    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    
    xl.Visible = True
    xl.Workbooks.Open "C:\Users\GOKULNAATH\Desktop\sa.xlsx"
    
    With xl
    .Visible = True
    With .Workbooks(.Workbooks.Count)
    For i = 1 To .Worksheets.Count
    WrksheetName = .Worksheets(i).NAME
    DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, "C:\Users\GOKULNAATH\Desktop\sa.xlsx"
    Next i
    End With
    
    End With
    Set xl = Nothing
    
    End Sub
    How to go about it ?Desktop.zip


    i have attached the sample excel file .also i have attached the access file image how i want the output to be.

  2. #2
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,436
    Here is code that I use to import multiple spreadsheet tabs. Note that this does not use any argument for named ranges, but if you look at the link at the bottom, it will tell you how todo that.
    Code:
    Private Sub Command1_Click()
    '---------------------------------------------------------------------------------------
    ' Procedure : ImportXLSheetsAsTables
    ' Author    : jed
    ' Date      : 1/16/2009
    ' Purpose   : To import all worksheets in a specified workbook into
    '             individual tables in Access.
    ' Tables get names: Tbl_ + name of the worksheet
    '
    ' NOTE: Must have a reference to the Microsoft Excel Object Library
    ' Make sure that the file GEMCAP.xls is located on your desktop.  You may have to change the path
    ' if you are using a newer version of Windows.  This was designed to run on Windows XP.
    '---------------------------------------------------------------------------------------
    '
    Dim appExcel As Excel.Application
    Dim wb As Excel.Workbook
    Dim sh As Excel.Worksheet
    Dim strValue As String
    
    
       On Error GoTo ImportXLSheetsAsTables_Error
    
    
    Set appExcel = CreateObject("Excel.Application")
    Set wb = appExcel.Workbooks.Open("C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls")
    For Each sh In wb.Sheets
    Debug.Print sh.Name
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_" & sh.Name, "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls", True, sh.Name & "!"
    Next
    
    
    wb.Close
    appExcel.Quit
    
    
       On Error GoTo 0
       Exit Sub
    
    
    ImportXLSheetsAsTables_Error:
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
    End Sub
    Look here for import instructions on named ranges.

    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

  3. #3
    gokul1242 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    5
    Quote Originally Posted by alansidman View Post
    Here is code that I use to import multiple spreadsheet tabs. Note that this does not use any argument for named ranges, but if you look at the link at the bottom, it will tell you how todo that.
    Code:
    Private Sub Command1_Click()
    '---------------------------------------------------------------------------------------
    ' Procedure : ImportXLSheetsAsTables
    ' Author    : jed
    ' Date      : 1/16/2009
    ' Purpose   : To import all worksheets in a specified workbook into
    '             individual tables in Access.
    ' Tables get names: Tbl_ + name of the worksheet
    '
    ' NOTE: Must have a reference to the Microsoft Excel Object Library
    ' Make sure that the file GEMCAP.xls is located on your desktop.  You may have to change the path
    ' if you are using a newer version of Windows.  This was designed to run on Windows XP.
    '---------------------------------------------------------------------------------------
    '
    Dim appExcel As Excel.Application
    Dim wb As Excel.Workbook
    Dim sh As Excel.Worksheet
    Dim strValue As String
    
    
       On Error GoTo ImportXLSheetsAsTables_Error
    
    
    Set appExcel = CreateObject("Excel.Application")
    Set wb = appExcel.Workbooks.Open("C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls")
    For Each sh In wb.Sheets
    Debug.Print sh.Name
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_" & sh.Name, "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls", True, sh.Name & "!"
    Next
    
    
    wb.Close
    appExcel.Quit
    
    
       On Error GoTo 0
       Exit Sub
    
    
    ImportXLSheetsAsTables_Error:
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
    End Sub
    Look here for import instructions on named ranges.

    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    thanks...got it.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-15-2012, 04:15 PM
  2. Replies: 2
    Last Post: 08-14-2012, 04:24 AM
  3. Import Excel Data to Multiple Access Tables
    By colby in forum Import/Export Data
    Replies: 3
    Last Post: 11-04-2011, 12:17 PM
  4. Import Data from Excel into Access
    By sauce1979 in forum Import/Export Data
    Replies: 2
    Last Post: 10-14-2011, 12:05 AM
  5. Import Excel sheet with query
    By tpcervelo in forum Queries
    Replies: 1
    Last Post: 08-03-2010, 07:10 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
  •  
Tech Forums: Microsoft Office Forums