printmedia
asked on
Export Access data to Excel using Access VBA
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?
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
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