Re: Excel Export - Field Names in First Row of Spreadsheet

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

At the moment I am using TransferSpreadsheet to create the Excel file.
How do I open the Excel file and delete the first row of records using
automation? I've never done this before. The TransferSpreadsheet is carried
out in VBA called from a command button (not in a macro.) Or is the first
way you suggest a better way of doing this?

One other thought: I am including the time and date in the filename when
creating the Excel file, using the following code:
Dim sNow As String
sNow = Format(Date, " dd-mm-yyyy") & " " & Format(Time, "hhmm")
DoCmd.TransferSpreadsheet acExport, 8, "VRM for Export", "C:\My
Documents\DSCP ANPR" & sNow & ".xls", True

If I need to include code to open the Excel file (to delete the first row),
I will (presumably) need to include the filename again. So using the
following for the filename would work (right?):
"VRM for Export", "C:\My Documents\DSCP ANPR" & sNow & ".xls"
My only thought is whether this would include the time as it "now" is,
rather than as it was when the Excel file was saved.... i.e. if the realtime
has just changed to the next minute (in the milliseconds it has taken for the
code to be processed), would the variable sNow now show the updated minute?
If it would, then the filename would be cited above would now have changed!
If it would still show the "old" time, there would be no problem.
Can you advise me on this point as well?

Thank you
rich

"Ken Snell [MVP]" wrote:

> Comments inline...
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Rich1234" <Rich1234@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:FE57C4B8-2277-4408-8F3B-F4555312165C@xxxxxxxxxxxxxxxx
> > Hi
> >
> > I have done a lot of "digging" through posts to try to find the answers I
> > need.....
> >
> > I am setting up an export to excel. I have read in the Access help File,
> > TransferSpreadsheet Action, that it is not possible to export to Excel
> > without the Field Names appearing in the first row of the spreadsheet.
>
> This is correct. TransferSpreadsheet will always export the field names when
> exporting to an EXCEL file.
>
>
> > (Is there any way around this?
>
> Not unless you use VBA code to open a recordset for the query you're
> exporting, open an EXCEL file using Automation, and write the data directly
> into the spreadsheet. Or use TransferSpreadsheet to export the query, then
> use Automation to open the spreadsheet and delete the first row.
>
>
> > Or is it possible to have the field names appear
> > in the "horizontal axis" at the top of the spreadsheet where A, B, C etc
> > appear as column names by default?)
>
> Nope, not to my knowledge.
>
> >
> > I have read in posts that a way to put the field names in column headers
> > in
> > the spreadsheet is to use a "make table query." How do I do this? I
> > would
> > like to have a button that the user presses on an Access form to save just
> > one field from one table to an Excel file.
>
> I'm not understanding this. A "make table query" has no advantages for what
> you want to do. You can export a query directly, and you can "change" the
> field names in the query using calculated fields in place of the original
> fields. For example, if you wanted the "field name" of a field to be "User
> Name" instead of "UserName" (the name in the table), you'd have a calculated
> field that would look like this in the QBE design window:
> User Name: [UserName]
>
> This means you can use customized "field names" for the export, and these
> customized names will appear in the first row of the spreadsheet.
>
>
>
> > This is new to me... if you could post what is needed step by step
> > (including code) I'd be very grateful.
> >
> > TIA
> > Rich
> >
>
>
>
.

Relevant Pages

  • Re: Importing Excel, more data type problems
    ... <MS ACCESS MVP> ... those first row values do not need to match the table's field names (but ... two problems importing this data. ... row of the Excel file, ...
    (microsoft.public.access.externaldata)
  • Re: Importing Excel, more data type problems
    ... If you want to use the first row in the EXCEL file as your field names, ... two problems importing this data. ... to automate the wizard process in VBA or any other way to get ...
    (microsoft.public.access.externaldata)
  • Re: TransferSpreadsheet not updating Excel file
    ... that's good that the make-table query is run separately from the export ... Is the EXCEL macro set to run when the EXCEL file is opened? ... Change your TransferSpreadsheet macro to export to ...
    (microsoft.public.access.macros)
  • Re: Mail Merge
    ... The Excel file I receive is an automated process that I receive every ... Aid Text - FSSL ... first row in the mail merge. ... Row 1- Mack Miller, $10,000 ...
    (microsoft.public.office.misc)
  • Text disappears in printing and print preview
    ... I have had a brief look in other posts and can't find the answer. ... get the first little bit of the first row, ... I have checked the cells, they are set to no fill, so it is not an accidental ... It was originally an excel file and I ...
    (microsoft.public.word.tables)