Styjun

I am trying to delete a column from a excel file created by exporting an access subform.

I use the following vba code to perform the export. The exported file contains are five column.

Private Sub CmdExporter_Click()
 DoCmd.RunCommand acCmdSelectAllRecords
 DoCmd.RunCommand acCmdCopy
 Dim xlapp As Object
 Set xlapp = CreateObject("Excel.Application")
 With xlapp
 .Workbooks.Add
 .ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
 False
 .Cells.Select
 .Cells.EntireColumn.AutoFit
 .Visible = True
 .Range("o1").Select
 End With
End Sub

Can someone help me adjust the code in order to be able to delete the last column(5th column) as the excel document gets exported?

You can use Range.Delete to delete cells. A refined version of your sub is:

Private Sub CmdExporter_Click()
 ' Create Excel app
 Dim xlapp As Object
 Set xlapp = CreateObject("Excel.Application")
 With xlapp
 .Visible = True ' show Excel app

 ' Create new workbook in Excel
 Dim xlWorkbook As Object
 Set xlWorkbook = .Workbooks.Add
 End With

 ' Get a reference to the workbook's first sheet
 Dim xlSheet As Object
 Set xlSheet = xlWorkbook.worksheets(1)

 ' Copy access data - done just before paste to reduce chance of clipboard being changed before paste
 DoCmd.RunCommand acCmdSelectAllRecords
 DoCmd.RunCommand acCmdCopy

 xlSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False ' Paste the data
 xlSheet.UsedRange.EntireColumn.AutoFit ' Auto fit column width

 ' Option 1: Delete last column
 xlSheet.UsedRange.Columns(xlSheet.UsedRange.Columns.Count).EntireColumn.Delete

 ' OR Option 2: Delete 5th column
 xlSheet.UsedRange.Columns(5).EntireColumn.Delete
End Sub

There are many examples online showing how to delete an Excel column using Access VBA. Which one is best for you depends on what you have and what you're trying to do.

Without having more specific information about your data or desired 'end-goal', I'd suggest that you may find it easier to use one of the methods specifically intended for exporting data from Access to Excel.

For example, a single line of code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Table1", "x:myOutputFile.xlsx", True, "myWorksheet"

...creates (or updates, if it exists and is closed) an Excel file x:myOutputFile.xlsx, creating (or updating if it exists) a sheet called myWorksheet, with the contents of table Table1. (For a partial dataset, you could specify a query name instead of table name.)

More Information:

• Microsoft Docs : DoCmd.TransferSpreadsheet method (Access)