Solved

Export Access data to Excel using Access VBA

Posted on 2013-01-28
4
387 Views
Last Modified: 2013-01-28
Hi all.

I have the following code that exports my Access data to Excel. But I want to make some changes so instead of the data appearing in column A, B, C, D of row 1, I want it to appear as

Column A, row 1--"Contract Number" data
Column A, row 2--"Status" data
Column A, row 3--"Facility ID" data
Column A, row 4--"Facility Name" data

Any ideas?

Dim xlWb1 'Script for creating excel files
Dim xlWs1

    Set xlApp1 = CreateObject("Excel.Application")
    Set xlWb1 = xlApp1.Workbooks.Add
    Set xlWs1 = xlWb1.Worksheets("Sheet1")
    xlApp1.Visible = True
    xlApp1.UserControl = True
    xlApp1.DisplayAlerts = True
    Dim objCon1  As New ADODB.Connection
    Dim objRS1 As ADODB.Recordset
    Set objRS1 = New ADODB.Recordset
    Dim rsExcel1 As DAO.Recordset
    Set rsExcel1 = CurrentDb.OpenRecordset("Select tblContract_Header.Contract_ID,tblContract_Header.Status,  tblContract_Detail.Facility_ID,tblContract_Detail.Name  FROM tblContract_Header INNER JOIN tblContract_Detail ON tblContract_Header.Contract_Number = tblContract_Detail.Contract_Number where tblContract_Header.[Contract_Number] = " & Me.txtContract__Number)
    xlWs1.Cells.CopyFromRecordset rsExcel1
    xlWs1.Rows("1").Insert
    xlWs1.Cells(1).Value = "Contract Number"
    xlWs1.Cells(2).Value = "Status"
    xlWs1.Cells(3).Value = "Facility ID"
    xlWs1.Cells(4).Value = "Facility Name"
    

    xlApp1.Rows(1).Font.Bold = True
    xlWs1.UsedRange.Borders.LineStyle = 1
    xlApp1.Columns.AutoFit

    Set xlWs1 = Nothing
    Set xlWb1 = Nothing
    Set xlApp1 = Nothing 'End Excel

Open in new window

0
Comment
Question by:printmedia
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38827295
change this part

   xlWs1.Cells(1).Value = "Contract Number"
    xlWs1.Cells(2).Value = "Status"
    xlWs1.Cells(3).Value = "Facility ID"
    xlWs1.Cells(4).Value = "Facility Name"

with

   xlWs1.Cells(1,1).Value = "Contract Number"
    xlWs1.Cells(2,1).Value = "Status"
    xlWs1.Cells(3,1).Value = "Facility ID"
    xlWs1.Cells(4,1).Value = "Facility Name"



post sample excel file  result
0
 

Author Comment

by:printmedia
ID: 38827355
Thanks capricorn.

But that just makes the words "Contract Number", "Status", "Facility ID" and "Facility Name" appear in Column A, but the data is still placed on row 1 Column A, Column B, Column C and Column D.

I have attached a sample excel file of how it will need to appear.
Sample-Excel.xlsx
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38827385
try this
Dim xlWb1 'Script for creating excel files
Dim xlWs1

    Set xlApp1 = CreateObject("Excel.Application")
    Set xlWb1 = xlApp1.Workbooks.Add
    Set xlWs1 = xlWb1.Worksheets("Sheet1")
    xlApp1.Visible = True
    xlApp1.UserControl = True
    xlApp1.DisplayAlerts = True
    Dim objCon1  As New ADODB.Connection
    Dim objRS1 As ADODB.Recordset
    Set objRS1 = New ADODB.Recordset
    Dim rsExcel1 As DAO.Recordset
    Set rsExcel1 = CurrentDb.OpenRecordset("Select tblContract_Header.Contract_ID,tblContract_Header.Status,  tblContract_Detail.Facility_ID,tblContract_Detail.Name  FROM tblContract_Header INNER JOIN tblContract_Detail ON tblContract_Header.Contract_Number = tblContract_Detail.Contract_Number where tblContract_Header.[Contract_Number] = " & Me.txtContract__Number)
   
   ' xlWs1.Cells.CopyFromRecordset rsExcel1
   
    xlWs1.Cells(1,1).Value = "Contract Number"
	xlWs1.Cells(1,2).Value =  rsExcel1("Contract Number")
    xlWs1.Cells(2,1).Value = "Status"
	 xlWs1.Cells(2,2).Value = rsExcel1("Status")
    xlWs1.Cells(3,1).Value = "Facility ID"
	 xlWs1.Cells(3,2).Value = rsExcel1("Facility ID")
    xlWs1.Cells(4,1).Value = "Facility Name"
	xlWs1.Cells(4,2).Value = rsExcel1("Facility Name")
    

    xlApp1.Rows(1).Font.Bold = True
    xlWs1.UsedRange.Borders.LineStyle = 1
    xlApp1.Columns.AutoFit

    Set xlWs1 = Nothing
    Set xlWb1 = Nothing
    Set xlApp1 = Nothing 'End Excel
                                  

Open in new window

0
 

Author Closing Comment

by:printmedia
ID: 38827416
Thanks!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

820 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