TransferSpreadsheet & Append Additional Fields

[gammaman]

I am trying to use transferspreadsheet to import access worksheet and then I want to append additional fields. I am able to import the excel sheet into access, but need help appending the other fields.

In the code below, how can I do the piece in red?

 Dim fd2 As FileDialog
Dim xlapp As New Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk  As Excel.Workbook
 Dim db As DAO.Database
 Dim tb2 As TableDef
Dim fdx, fld As DAO.Field
  
 Private Sub Append_File_Click()
  
 et fd2 = Application.FileDialog(msoFileDialogFilePicker)
 With fd2
 .Show
For Each vrtSelected In .SelectedItems
    Set xlapp = CreateObject("Excel.application")
   Set xlWrkBk = GetObject(vrtSelected)
   Set xlsht = xlWrkBk.Worksheets(1)
  
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, TableName:="my_table", FileName:=vrtSelected, HasFieldNames:=True
  
 With [COLOR=red]"the table I just imported"
[/COLOR]  
   Set fld = .CreateField("Name", dbText)
   .Fields.Append fld
  
    Set fld = .CreateField("Birthdate", dbDate)
   .Fields.Append fld
  
 End With
   db.TableDefs.Append tb2
Next vrtSelected
End With

 

[JHB]

Try instead the below, be aware of Name is a reserved word in MS-Access:

CurrentDb.Execute ("ALTER TABLE my_table ADD TheName VARCHAR(50), Birthdate date;")

 

[gammaman]

Works Great. Thanks.

 

[JHB]

You're welcome, good luck.

 

[gemma-the-husky]

you can also do what you wanted by using the tabledef object

you need this sort of syntax to establish a pointer to the table
set tdf = db.tabledefs("my table")

then
with tdf

The rest of your code to add the field was correct, I think. (or very close!)