delete row in excel object model

[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

 

[Clifford Bass]

Hi Fred,

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

Clifford Bass

 

[Fredrated]

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

 

[Clifford Bass]

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

 

[Fredrated]

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

 

[Clifford Bass]

Hi Fred,

You are welcome! And good luck,

Clifford Bass