delete row in excel object model

  • Thread starter Thread starter Fredrated
  • Start date Start date
F

Fredrated

Can anyone provide the code, if possible, to delete a row from a spreadsheet
using the excel object model? The transferspreadsheet method makes the first
row a field name row, and I would like to delete it after the spreadsheet is
created.

Thanks.

Fred
 
Hi Fred,

How about just specifying False for the HasFieldNames (fifth) parameter?

Clifford Bass
 
Thanks for your reply.
According to the documantation, hasfieldnames only applies to imported data,
exporting to a spreadsheet always has field names in the first row. And
indeed I can believe that, because I set that param to false yet the field
names appear anyway.

Fred
 
Hi Fred,

That is a bummer :-( It would make the most sense. Oh well. Try
something like this:

Dim appExcel As New Excel.Application
Dim wkbExported As Excel.Workbook
Dim strFile As String

strFile = Environ("USERPROFILE") & "\Desktop\C Table.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C", strFile

Set wkbExported = appExcel.Workbooks.Open(strFile)
wkbExported.ActiveSheet.Range("A1").Delete
wkbExported.Save
wkbExported.Close
Set wkbExported = Nothing
appExcel.Quit
Set appExcel = Nothing

You will need to add the "Microsoft Excel nn.n Object Library" to your
references (Tools menu, References) for it to work.

Clifford Bass
 
Thanks, I just handed off the product to people that want to create reports
from the spreadsheets. I will try this and add it next week.

I created my own equiv. of the first row by unioning a table of column
descriptors with the output query, so we don't need the first row of field
names. I am sure they will be glad to see the field-names first row removed
in code.

Fred
 
Back
Top