Member Avatar for preethi_ga2005

hei friends can anyone tell me how to upload excel file to access in vb.....if u give code it will be very much helpfull to me, thankx.

  • 3 Contributors
  • 2 Replies
  • 307 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by abu taher

Dani AI

Generated

Brief practical summary for : the two common, reliable ways to move Excel data into Access from VB are (A) Use Access’s built‑in DoCmd.TransferSpreadsheet for quick imports and simple scripts, or (B) read the workbook via ADO (ACE OLEDB) and insert rows programmatically when more control is needed. ’s reply demonstrates the TransferSpreadsheet approach for multiple sheets, but creating an Excel.Application before calling TransferSpreadsheet is usually unnecessary — TransferSpreadsheet can import directly. (learn.microsoft.com)

Notes to apply when using TransferSpreadsheet: pick the right AcSpreadSheetType for the file format (for example, use the Excel12Xml option for .xlsx files), set HasFieldNames=True when the first row is headers, and use the Range argument only for imports if a specific cell range is required. Appending to an existing Access table requires matching field names and compatible data types (or use a staging table and convert types explicitly). See the TransferSpreadsheet parameters and the AcSpreadSheetType mapping for exact constants. (learn.microsoft.com)

When TransferSpreadsheet is too rigid (mixed datatypes, need to transform rows, scheduled/background runs), read Excel via ACE OLEDB and ADODB, then write into Access. This avoids Excel automation and gives control over data typing (IMEX to force text, explicit conversions). Example (VB late binding for the read step):

Dim cn As Object, rs As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\file.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
Set rs = cn.Execute("SELECT * FROM [Sheet1$]")
' iterate rs and insert into Access table (or use DAO/CurrentDb to append)

Refer to ACE/connection-string notes (IMEX and Excel version tokens). (connectionstrings.com)

Fast troubleshooting checklist: 1) test the same workbook manually via External Data > Excel to confirm how Access maps columns; 2) if the OLEDB provider error appears (“provider not registered”), install the Access Database Engine that matches the process bitness; 3) validate file extension vs. chosen spreadsheet type (mismatch can create corrupt/format warnings). These fixes cover most common import failures. (microsoft.com)

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

Ok i will give you code.

How much you are ready to pay me ?

Member Avatar for abu taher

if you mean this: import data from excel spreadsheet to access table then use this

'replace this "c:\temp\filename.xls" with your excel filename.

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:\temp\filename.xls"

With xl
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).NAME
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, "c:\temp\filename.xls"
Next i
End With

End With
Set xl = Nothing

End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.