Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Spread headings

Posted on 2014-07-30
14
Medium Priority
?
137 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 52

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 40

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 52

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 40

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

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!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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