?
Solved

Spread headings

Posted on 2014-07-30
14
Medium Priority
?
132 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +2
14 Comments
 
LVL 51

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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 38

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1000 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 51

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 38

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

762 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