CODEWITHSUNDEEP
sqlexcelms-accessvba
Houssam Nachit
Houssam Nachit

Reputation: 3

how to import a sheet of an excel file into the access database?

I want to import a sheet of an excel file into the access database

Private Sub Commande9_Click()
Dim filepath As String

filepath = "C:\Users\TXZG6067\Desktop\com.xlsx"
DoCmd.TransferSpreadsheet acImport, , "ImportFromExcel", filepath, True

End Sub

Please i need your help,thank you.

Upvotes: 0

Views: 347

Answers (5)

Gustav
Gustav

Reputation: 55806

Specify the worksheet name:

DoCmd.TransferSpreadsheet acImport, , "ImportFromExcel", filepath, True, "WorksheetName$"

or:

DoCmd.TransferSpreadsheet acImport, , "ImportFromExcel", filepath, True, "WorksheetName!"

Upvotes: 0

ARr0w
ARr0w

Reputation: 1731

Syntax: expression .TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

Example:

DoCmd.TransferSpreadsheet acImport, 3, _ 
 "Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"

You must give it the SpreadsheetType, for details about SpreadsheetType see below link:

https://msdn.microsoft.com/en-us/library/office/ff196017.aspx

Upvotes: 0

kulapo
kulapo

Reputation: 397

You can try this in Excel VBA (ADO), assuming that your data is in Column A to C and so forth..

Dim dbpath as String, _
    x as long, _
    rs As ADODB.Recordset, _
    cn As ADODB.Connection

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

dbPath = /your db path

cn.Open "Provider=Microsoft.ace.OLEDB.12.0;Data Source=" & dbPath
rs.Open "Consolidated", cn, adOpenKeyset, adLockOptimistic, adCmdTable

For x = 2 To LastRow
    rs.AddNew
    rs.Fields("Fieldnm1") = Range("A" & x).value
    rs.Fields("Fieldnm2") = Range("B" & x).value
    rs.Fields("Fieldnm3") = Range("C" & x).value
    rs.Update
Next x

rs.Close

Set cn = Nothing
Set rs = Nothing

Upvotes: 1

ARr0w
ARr0w

Reputation: 1731

To Import a Excel file or one of its sheet to Database, you have to follow some steps:

1) Convert that sheet/File to comma-delimiter. Use Save as option to convert.

2) Read this file using StreamReader class. Validate Columns length and the data that every column contains.

3) create a query using StringBuilder class (recommended) but you can also use a string too. Such as:

StringBuilder Sb = new StringBuilder("Insert Into table_name Values(");
foreach(row in dt.rows)
{
  sb = null;
  int j = 1;
  for(int i=0; i<columns_Count-1 ; i++) //Column count minus one to handle last column data
  {
   Sb.Append("'"+row[0][i]+"',"); 
   j++;
  }
  if(j == columns_Count) //last column appends data without comma
  {
  Sb.Append("'"+row[0][columns_Count]+"'");
  }
  Sb.Append(")");
 //execute insert query here
}

Upvotes: 1

Jul Pod
Jul Pod

Reputation: 396

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9 , _
"TableOrQuery","YourExcelPath", _
True, "TheNameofTheSheetYouWant"

Upvotes: 0

Related Questions