How to import an Excel file in code? (1 Viewer)

[DennisB]

I've been looking in books for some code example to import an Excel file, change the field names and the data types. Then have this file append to an existing Access table.

Are there examples of this?

Please help, Dennis

 

[Danny Clarke]

I'm trying to do something similar at the moment so here's what I've managed to achieve so far (ie: almost nothing):

To import the Excel file:

DoCmd.TransferSpreadsheet acImport,Excelv,"Your Table",filepath,True

Where Excelv is the file version of the Excel sheet (eg: Excel3, Excel7, Excel97). The 'True' tells Access that the first row of your spreadsheet contains field names.

To change the field names you need to know the field names that come across from Excel and some code like this (I think!):

Sub AlterData()
Dim dbs As Database, rst As Recordset
Dim fld As Field

Set fld = rst.Fields![Excel field name]
Set fld.Name = "NewName"

End Sub

Hope this works and is of some help!

Dan


[This message has been edited by Danny Clarke (edited 03-08-2000).]

 

[MHM]

About changing Field-Types:

Seems to be quite difficult! (Access does not allow Field.Type to be changed after having been added to TableName.Fields .)

Suggestion:

Create new field with better Field Type, copy all contents of bad field to new field, delete bad field, rename new field as RequiredFieldName.

But:
Once You import into Table, changes cannot be reversed (happened when string "1.234.567.890" was converted to double 1.234567890E+09, which is difficult to be altered).

Example:

Function tryChangeFieldType(strTblName As String) As Boolean
Dim tdf1 As TableDef
Dim fld1 As Field, fldNew As Field, strFldName As String
Dim rs1 As Recordset
Dim SysCmdRet

tryChangeFieldTypeInTblTmpImpSAP = False
Set tdf1 = db.TableDefs(strTblName)
For Each fld1 In tdf1.Fields
Select Case fld1.Name
Case "xxx1", "xxx2", "xxx3"
If fld1.Type <> dbText Then
strFldName = fld1.Name
SysCmdRet = SysCmd(acSysCmdSetStatus, "Feldtyp von " & strFldName & " ändern")
Set fldNew = tdf1.CreateField("_" & strFldName, dbText)
tdf1.Fields.Append fldNew
tdf1.Fields.Refresh
Set rs1 = db.OpenRecordset(strTblName, dbOpenTable)
rs1.MoveFirst
Do Until rs1.EOF
rs1.Edit
rs1("_" & strFldName) = CStr(rs1(strFldName))
rs1.Update
rs1.MoveNext
Loop
rs1.Close
tdf1.Fields.Delete strFldName
Set fld1 = tdf1.Fields("_" & strFldName)
fld1.Name = strFldName
Set fld1 = Nothing
db.TableDefs.Refresh
SysCmdRet = SysCmd(acSysCmdClearStatus)
tryChangeFieldTypeInTblTmpImpSAP = True
End If
End Select
Next
Set tdf1 = Nothing
End Function


Comment: copying field not very fast