Solved

exporting into a file with header in access

Posted on 2015-02-24
3
87 Views
Last Modified: 2016-02-12
How do you export from access into a excel file with the excel file with a header already formatted.


I want to be able to export the data starting from row 15 in the excel file and how do you call the same file and still be able to export data again and again
0
Comment
Question by:Jass Saini
3 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40628865
To do this, you need to make Access start an instance of Excel, and use Excel vba to write to the worksheet.

Typically I'd use code something like

Dim xl As Object
Dim xlw As Object
Dim xls As Object

Set xl = CreateObject("Excel.Application")
xl.DisplayAlerts = False
xl.Visible = False

Set xlw = xl.Workbooks.Add
xlw.SaveAs sWB, FileFormat:=56 ''Fileformat 56 is the excel 97-2003 file format for xls files

Set xls = xlw.Worksheets("Sheet1")
xls.Name = "Record Sheet"
xls.SELECT
xls.Activate

From there you need to manage what cell or cells you are using and what you want to happen.


Kelvin
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40628900
try this codes, replace objects as appropriate with your actual object names.

Sub exp2XL()
Dim rs As DAO.Recordset
Dim xlObj As Object
Dim Sheet As Object

Set rs = CurrentDb.OpenRecordset("select * from queryName")

If rs.EOF Then Exit Sub

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.open "c:\folder\excelfile.xlsx"
      
      Set Sheet = xlObj.activeworkbook.Sheets("sheet1")
      
       Sheet.Range("A15").CopyFromRecordset rs  'copy the data
      
    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing
      
end sub
0
 

Author Closing Comment

by:Jass Saini
ID: 40642735
Thanks
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem copying record details to a new record 5 9
Merging spreadsheets 8 42
aggregate query? 20 52
macro modification Column C 14 31
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

803 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