Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117

    Cool Export and Import from excel with form in access

    hi all


    i want to build a form and import and export data by clicking button

    in the table and update existing records

    how can i do?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Write code. Post code for analysis when you have issues.

    Google: Access VBA import export

    Did you search this forum? This topic has been addressed.

    There is plenty of code out there. Specifics depend on the data format to import/export.
    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.

  3. #3
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117

    Cool Export and Import from excel by form in access database

    hi
    thanks for your reply i could not find any code from google

    i have a table in access and i want to import data by clicking button
    on form it should ask file path then if heading are not matching with table
    headings msg should appear and exit sub

    second: it should overright if same data is existing with two parameters
    parameters are id number and date.

    how can i do?

    any body guide me

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What you want to do is not trivial and can be quite complex. This thread might give you some idea of what you face http://forums.aspfree.com/microsoft-...le-413493.html

    There is lots of code samples out there. The difficulty is trying to adapt to your specific circumstances.

    You still haven't even indicated the type of source file.

    You should consider just linking to the source file.
    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.

  5. #5
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117

    Cool

    hi all,
    below i have pasted module code one function and in command button code where i called function and at last excel file which i want to import
    put one cmd button, one textbox name as txtfile and one label as lblMsg on access form i could not understand where i am wrong. it is working fine but table still empty i could not find any record in table.

    kindly guide me where i am wrong with in function code.

    module code:

    Public Function ProcessFileImportEmp(ByVal sFile As String, ByVal sTable As String) As String
    On Error GoTo ProcessFileImport_Error

    ' Excel object variables
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet

    ' Access object variables
    Dim dbs As DAO.Database
    Dim rstRead As DAO.Recordset
    Dim rstWrite As DAO.Recordset
    Dim fld As DAO.Field

    ' Declared variables
    Dim bytWks As Byte
    Dim bytMaxPages As Byte
    Dim intStartRow As Double
    Dim strData As String
    Dim intMaxRow As Double
    Dim strSQL As String
    Dim strMsg As String
    Dim intLastCol As Double
    Dim intRow As Double
    Dim intRec As Double
    Dim strCurrFld As String
    Dim intCol As Double
    Dim intLen As Double
    Dim varValue As Variant
    Dim lngErrs As Long
    'Const cPassword As String = "xxx999"

    DoCmd.Hourglass True

    ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
    On Error Resume Next
    Set appExcel = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
    Set appExcel = CreateObject("Excel.Application")

    End If
    Err.Clear
    'Set appExcel = Excel.Application
    Set wbk = appExcel.Workbooks.Open(sFile)
    Set dbs = CurrentDb

    ' Optionally, you can protect / unprotect with a password
    'wkb.Unprotect (cPassword)

    ' You could loop through sheets, but for this example, we'll just do one.
    bytMaxPages = 1

    ' Sometimes there is header info, so the "Start Row" isn't the first one.
    ' Set this variable to the first row that contains actual data.
    intStartRow = 2
    PostMessage "Opening file: " & sFile
    'MsgBox sFile
    For bytWks = 1 To bytMaxPages
    ' Initialize variables on each pass
    Set wks = Nothing
    Set rstRead = Nothing
    intRow = intStartRow

    ' Load current worksheet. Find used range to determine row count.
    Set wks = appExcel.Worksheets(bytWks)

    ' Optionally, you can protect / unprotect with a password
    'wks.Unprotect (cPassword)

    ' You need to figure out how many rows this sheet contains, so to know
    ' how far down to read. That value is saved in intMaxRow
    strData = wks.UsedRange.Address
    'MsgBox strData
    intMaxRow = Int(Right(Mid(strData, InStrRev(strData, "$")), Len(Mid(strData, InStrRev(strData, "$"))) - 1))
    'intMaxRow = CInt(Mid(strData, InStrRev(strData, "$")))
    'MsgBox intMaxRow
    'MsgBox sTable & " Table"
    'intMaxRow = CInt(Mid(strData, LastInStr(strData, "$")))

    strData = ""

    ' Go get the list of fields for this worksheet from the Field Map table
    strSQL = "SELECT [AccessField], [OrdinalPosition] FROM ImportColumnSpecs " & _
    "WHERE [ImportName]='" & sTable & "' ORDER BY [OrdinalPosition] ASC;"

    Set rstRead = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    ' If there is a mistake and no specification exists, then exit with message
    If rstRead.BOF And rstRead.EOF Then
    strMsg = "The import spec was not found. Cannot continue."
    'MsgBox strMsg, vbExclamation, "Error"
    Else
    rstRead.MoveLast
    rstRead.MoveFirst
    intLastCol = rstRead.RecordCount

    ' The name of the import and destination table should be the same for this
    ' code to function correctly.
    Set rstWrite = dbs.OpenRecordset(sTable, dbOpenDynaset)
    Do Until intRow > intMaxRow
    ' Check row to be sure it is not blank. If so, skip the row
    For intCol = 1 To intLastCol
    strData = strData & Trim(Nz(wks.Cells(intRow, intCol), ""))
    Next

    If strData = "" Then
    intRow = intRow + 1
    Else
    intRec = intRec + 1
    PostMessage "Processing record " & intRec & ". {StoreID=" & wks.Cells(intRow, 1) & "}"
    rstWrite.AddNew
    Do Until rstRead.EOF
    ' Loop through the list of fields, processing them one at a time.
    ' Grab the field name to simplify code and improve performance.
    strCurrFld = Nz(rstRead!AccessField, "")
    intCol = rstRead!OrdinalPosition

    ' Make sure that text fields truncate data at prescribed limits.
    ' Users may not enter supply more text than the fields can contain.
    If dbs.TableDefs(sTable).Fields(strCurrFld).Type = dbText Then
    intLen = dbs.TableDefs(sTable).Fields(strCurrFld).Size
    varValue = Left(Nz(wks.Cells(intRow, intCol), ""), intLen)
    Else
    varValue = wks.Cells(intRow, intCol)
    End If

    ' The database schema requires that empty fields contain NULL, not
    ' the empty string.
    If varValue = "" Then varValue = Null

    ' Handle date columns. Sometimes Excel doesn't format them as dates
    If InStr(1, strCurrFld, "Date") > 0 Then
    If Not IsDate(varValue) Then
    If IsNumeric(varValue) Then
    On Error Resume Next
    varValue = CDate(varValue)
    If Err.Number <> 0 Then
    ' Can't figure out the date. Set to null
    varValue = Null
    Err.Clear
    End If
    On Error GoTo ProcessFileImport_Error
    Else
    lngErrs = lngErrs + 1
    varValue = Null
    End If
    End If
    rstWrite.Fields(strCurrFld) = varValue
    Else
    ' If not a date field, then just write the value to the rst
    rstWrite.Fields(strCurrFld) = varValue
    End If

    rstRead.MoveNext
    Loop
    If Not rstRead.BOF Then rstRead.MoveFirst

    rstWrite.Update

    ' Reset the variables for processing of the next record.
    strData = ""
    intRow = intRow + 1
    'Debug.Print intRow
    End If
    Loop
    Set wks = Nothing
    End If
    Next

    Exit_Here:
    ' Report results
    strMsg = "Total of " & intRow & " records imported."
    PostMessage strMsg
    ProcessFileImportEmp = strMsg
    ' Cleanup all objects (resume next on errors)

    On Error Resume Next
    ' Optionally, you can protect / unprotect with a password
    'wkb.Protect (cPassword)
    'wks.Protect (cPassword)
    Set wks = Nothing
    wbk.Close True
    Set wbk = Nothing
    appExcel.Quit
    Set appExcel = Nothing
    Set rstRead = Nothing
    Set rstWrite = Nothing
    Set dbs = Nothing
    DoCmd.Hourglass False
    Exit Function

    ProcessFileImport_Error:
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_Here

    End Function
    -------------------------------------------------
    Private Sub cmdImport_Click()
    On Error Resume Next
    Dim strMsg As String
    Dim strFile As String

    strFile = Nz(Me.txtFile, "")

    If Dir(strFile) <> "IMPORT TO ACCESS.xls" Then
    strMsg = "You must enter a path for the file named IMPORT TO ACCESS.xls"
    MsgBox strMsg, vbExclamation, "Error"
    Me.txtFile.SetFocus
    Else
    Me.lblMsg.Caption = "Import store records."
    strMsg = "Do you want to import Employees records from file: " & vbCrLf & strFile
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Start Import") = vbYes Then
    CurrentDb.Execute "DELETE FROM [EmpClocking]"
    strMsg = ProcessFileImportEmp(Me.txtFile, "EmpClocking")
    MsgBox strMsg, vbInformation, "Finished"
    Me.lblMsg.Caption = strMsg
    DoCmd.OpenTable "EmpClocking"
    DoCmd.MoveSize 100, 100, 9500, 6500
    Else
    Me.lblMsg.Caption = "Import Cancelled."
    End If
    End If

    End Sub
    excel file:
    IDEMPNAMECINCOUTOFFTYPEOtherTypeSHIFT CODE410552ABDUL HAMEED KODIPADI01/Aug/2011 08:00:0001/Aug/2011 15:00:000RAM141055202/Aug/2011 08:00:0002/Aug/2011 18:00:000RAM141055203/Aug/2011 08:00:0003/Aug/2011 14:00:000RS141055204/Aug/2011 08:00:0004/Aug/2011 14:00:000RAM141055205/Aug/2011 00:00:00OFF41055206/Aug/2011 08:00:0006/Aug/2011 14:00:000RS141055207/Aug/2011 08:00:0007/Aug/2011 14:00:000RS141055208/Aug/2011 08:00:0008/Aug/2011 14:00:000RS141055209/Aug/2011 08:00:0009/Aug/2011 14:00:000RS141055210/Aug/2011 08:00:0010/Aug/2011 14:00:000RS141055211/Aug/2011 08:00:0011/Aug/2011 14:00:000RS141055212/Aug/2011 00:00:00OFF41055213/Aug/2011 08:00:0013/Aug/2011 18:00:000RAM141055214/Aug/2011 08:00:0014/Aug/2011 18:00:000RAM1

  6. #6
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117

    Cool

    pls guide me
    where i have to attached excel file for example to all to check code.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You can attach the Excel file to a post. Click the 'Go Advanced' button to get the advanced post editor. Tools to attach files will be below the editor window.

    Have you step debugged?
    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.

  8. #8
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117

    Cool

    i have zip and attached excel file.

    kindly guide me how can i import file in access table

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Now tell us what the issue is - error message, wrong results, nothing happens? What line errors? Have you step debugged? Follow the code as it executes, find where behavior deviates from expected and fix, repeat.

    Why ask us to do what you haven't already tried for yourself? Hard to test code without the db you are importing into. The code refers to tables we don't have.
    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.

  10. #10
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117
    My Dear

    i have attached database.

    i have tried many times but result is nothing every time table is empty.

    no error is appear, nothing happen

    and new things how can i debugg code? tell me about this also.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Check this tutorial on debugging http://www.cpearson.com/excel/debug.htm

    Debugging skills are essential to programming.

    You tried to attach db to post? In a zip? Don't care if table is empty, attach the Access project file. Or at least an extract with the tables need for the code to run.
    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.

  12. #12
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117
    see attachment

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No attachment. Attempt debug and when you can give an error message or describe results of running code even if doesn't error, I will assist.
    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.

  14. #14
    waqas is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    117
    i think its heavy almost 4.33 mb after zip.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you run Compact & Repair? Does that include data? Extract to another project just the tables needed for the procedure.

    Have you run debug yet?
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. excel link with access differs from simple export from access
    By M0RDANT in forum Import/Export Data
    Replies: 4
    Last Post: 03-25-2013, 02:43 PM
  2. Export from Access to Excel
    By Eowyne in forum Import/Export Data
    Replies: 5
    Last Post: 04-23-2011, 07:08 PM
  3. Export Access '07 Pivot Tbl to Excel'07
    By nagiese in forum Import/Export Data
    Replies: 1
    Last Post: 02-08-2011, 08:19 AM
  4. Access date export to excel
    By jituknows in forum Access
    Replies: 1
    Last Post: 02-05-2011, 01:32 PM
  5. Access export to Excel
    By Rick West in forum Import/Export Data
    Replies: 4
    Last Post: 01-09-2010, 03:40 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