Export and Import from excel with form in access

[waqas]

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?

[June7]

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.

[waqas]

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

[June7]

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.

[waqas]

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

[waqas]

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

[June7]

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?

[waqas]

i have zip and attached excel file.

kindly guide me how can i import file in access table

[June7]

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.

[waqas]

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.

[June7]

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.

[waqas]

see attachment

[June7]

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.

[waqas]

i think its heavy almost 4.33 mb after zip.

[June7]

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?