Solved

Spread headings

Posted on 2014-07-30
14
130 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 50

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 37

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all 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 50

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 37

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 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