Solved

Spread headings

Posted on 2014-07-30
14
129 Views
Last Modified: 2014-08-01
Hi EE

I generate a spread sheet using the following
FileName = "SmartCashFinancialReport.xls"
Tpath = "C:\SmartCash\AnnualReport\" & FileName
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblFinancialReport", Tpath

Is it possibe to add a heading. Any help appreciated

chestera
0
Comment
Question by:chestera
  • 7
  • 2
  • 2
  • +2
14 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40231147
No. For that you will need to use automation - calling Excel to open the worksheet and then manipulate this.

/gustav
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40231291
You could try creating a query based on your table, and then add a Field to the query:

SELECT Col1, Col2, Col3, '' AS Col5 FROM tblFinancialReport

Save that query, then use that in your TransferSpreadsheet call:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMyQuery", Tpath
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40232776
If by heading you mean column headings, the answer is yes; that is an option in the TransferSpreadsheet method.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 40232783
<Is it possibe to add a heading.>
Add a heading where exactly?
What is this heading to do?
Can you give an example?

Whenever you are importing and exporting, it is best to not include anything other than the field names and the data, ...just concentrate on getting the correct data in.

In a spreadsheet export in Access, Row 1 contains the field names.  And the data starts in Row 2.
So it is not clear where your "heading" would go.
If you want it "above" all the data (in row 1, then move everything else down 1 row), you will need vba automation as the first expert points out.
But then this "Modified" spreadsheet might give you headaches if you ever need to export it anywhere else.
Because most programs that will accept a spreadsheet, will expect the field names in row 1 and the data to start in row 2.

What Scott posted will create a new "Field" in row 1 with your heading text.
This might be your best shot at creating a "heading".

But again, ...you need to clarify what you mean by a "Heading", its purpose, and where you want it located.

JeffCoachman
0
 

Author Comment

by:chestera
ID: 40233092
boag2000

Hi Jeff

The spread would look like this
                    Financial Report

Col1             Col2               Col3                 Col4

At the moment I only have the data

Alan
0
 

Author Comment

by:chestera
ID: 40233104
Scott McDaniel

Would that ocmo out like
Heading  Col1 Col2 Col3 etc

Alan
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40233316
Lets see what Scott posts on his suggestion...

Doing what you are asking above would require vba automation as the first expert suggests.
0
 

Author Comment

by:chestera
ID: 40233376
boag2000

I was just going to test, I have just created a blank spreadsheet with a heading
0
 

Author Comment

by:chestera
ID: 40233424
boag2000

I now generate the spreadsheet then it  opens and the user can enter a header if required

Alan
0
 

Author Comment

by:chestera
ID: 40233426
Scott McDaniel

I tried your suggestion but it's not quite what's need. I get the following
Col1 Col2 Col3,,,,,,, Heading
I need
            Heading
Col1 Col2 Col3 etc

I now generate the spreadsheet then auto opens so the user can enter a heading if required.

The reason for the question I thought there might be some way to incorporate into the DoCmd.Transfer command

Alan
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40233787
I need
             Heading
Col1 Col2 Col3 etc
That's what I imagined. For this, automation is the only method with the latest and current versions of Microsoft Office.

/gustav
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40234232
You could union the header with the query you are exporting now and export the union query.  That will get the header on the first line but it will be squished into the first column.  There is no way without automation to center it, etc.

An alternative might be to control the name that appears on the tab.  Excel picks up the name of your query so you could change the query name to be more descriptive.
0
 

Author Comment

by:chestera
ID: 40235460
PartHartman

Thank you Pat. The user can enter a heading of their choice from the generated spreadsheet

Alan
0
 

Author Closing Comment

by:chestera
ID: 40235468
Thank you for your help
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question