Solved

Spread headings

Posted on 2014-07-30
14
128 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 35

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 35

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

832 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