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

"Fredrated" wrote:

> 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

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" wrote:

> Hi Fred,
>
> How about just specifying False for the HasFieldNames (fifth) parameter?
>
> Clifford Bass
>
> "Fredrated" wrote:
>
> > 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,

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" wrote:

> 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

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" wrote:

> 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" wrote:
>
> > 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,

You are welcome! And good luck,

Clifford Bass

"Fredrated" wrote:

> 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