Exporting from Access to a specific table and range in excel (1 Viewer)

A

archy321

Registered User.
Local time
Today, 02:37
Joined
Sep 10, 2018
Messages
11
Hello,

Im wanting to export two queries into into specific ranges into a template i've made in excel. So far i've been able to code a button on a form to take a specific query and export it into the correct field on a brand new excel sheet, but no more. I've been working on this over the past week and this is me right now :banghead::banghead: any help would be greatly appreciated!

This is my code so far:

Private Sub command25_Click()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'Step 2: Identify the database and query
Set MyDatabase = CurrentDb
Set MyQueryDef = MyDatabase.QueryDefs("April deviations") 'Query name in the database
'Step 3: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 4: Clear previous contents
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add
.Sheets("Sheet1").Select
'Step 5: Copy the recordset to Excel
.ActiveSheet.Range("A41").CopyFromRecordset MyRecordset
'Step 6: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With
'MsgBox "Query has been successful", vbInformation, "Sample"

End Sub
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,241
to work with your template you must open it:

dim wb as excel.workbook
set wb=xlApp.Workbooks.open("path and name of template")
 
A

archy321

Registered User.
Local time
Today, 02:37
Joined
Sep 10, 2018
Messages
11
Thank you so much for answering! I'm now getting an error though: Compile error: user-defined type not defined. What did i do wrong?


Private Sub command25_Click()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
Dim wb As excel.workbook
'Step 2: Identify the database and query
Set MyDatabase = CurrentDb
Set MyQueryDef = MyDatabase.QueryDefs("April deviations") 'Query name in the database
Set wb = xlApp.Workbooks.Open("C:\Users\archy\Documents\metrics")
'Step 3: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 4: Clear previous contents
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add
.Sheets("Sheet1").Select
'Step 5: Copy the recordset to Excel
.ActiveSheet.Range("A41").CopyFromRecordset MyRecordset
'Step 6: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
End With
'MsgBox "Query has been successful", vbInformation, "Sample"

End Sub
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,241
on vba, Tools->References,

add reference to Microsoft Excel x.xx Object.

x.xx means the version you have.

also you open the workbook After instatiating an Excel app:

set xlApp=createobject("excel.application")
set wb=xlApp.workbooks.open(....

put the extension also on the filename, .xlsx or .xls

and remove, adding another workbook. (xlapp.workbooks.add)
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,241
Code: 
Private Sub command25_Click()
'Step 1: Declare your variables
	'* arnelgp
	'* put the Full path + filename + extension below
	Const myTemplate As String = "C:\Users\archy\Documents\metrics.xlsx"
	
	Dim MyDatabase As DAO.Database
	Dim MyQueryDef As DAO.QueryDef
	Dim MyRecordset As DAO.Recordset
	Dim i As Integer
	
	'* must add reference (Tools->References) to
	'* Microsoft Excel x.xx Object
	Dim xlApp As Excel.Application
	Dim wb As Excel.Workbook

'Step 2: Identify the database and query
	Set MyDatabase = CurrentDb
	Set MyQueryDef = MyDatabase.QueryDefs("April deviations") 'Query name in the database

'Step 3: Open the query
	Set MyRecordset = MyQueryDef.OpenRecordset

'Step 4: Clear previous contents
	Dim xlApp As Object
	Set xlApp = CreateObject("Excel.Application")
	With xlApp
		.Visible = True
		Set wb = .Workbooks.Open(myTemplate)
		'* we don't need to add, we already open the template above
		'.Workbooks.Add
		'* this will not work if there is no 'Sheet1' named worksheet.
		'* use the Index number instead
		'.Sheets("Sheet1").Select
	End With

'Step 5: Copy the recordset to Excel
	With wb.WorkSheets(1)
		.Range("A41").CopyFromRecordset MyRecordset

'Step 6: Add column heading names to the spreadsheet
		For i = 1 To MyRecordset.Fields.Count
			.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
		Next i
		.Cells.EntireColumn.AutoFit
	End With
	
'Step 7: Housekeeping and Message
	MyRecordset.Close
	Set MyQueryDef=Nothing
	Set MyRecordset=Nothing
	Set MyDatabase=Nothing
	'MsgBox "Query has been successful", vbInformation, "Sample"
	
End Sub
 
A

archy321

Registered User.
Local time
Today, 02:37
Joined
Sep 10, 2018
Messages
11
Hello,

I am so sorry I did not reply sooner.

I've enabled the references as instructed (Microsoft excel 16.0 object library) and used the code that you provided, but I am now getting an error. Compile error: Duplicate declaration in current scope.

Private sub command25_Click() is highlighted yellow and
Dim xlApp as object is highlighted blue.

Did I miss something when entering my own information into your coding provided?

Also, If I haven't thanked you yet, thank you thank thank you!



Private Sub command84_Click()
'Step 1: Declare your variables
'* arnelgp
'* put the Full path + filename + extension below
Const myTemplate As String = "C:\Users\archy\Documents\metrics"

Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'* must add reference (Tools->References) to
'* Microsoft Excel x.xx Object
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook

'Step 2: Identify the database and query
Set MyDatabase = CurrentDb
Set MyQueryDef = MyDatabase.QueryDefs("April deviations") 'Query name in the database

'Step 3: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 4: Clear previous contents
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
Set wb = .Workbooks.Open("myTemplate")
'* we don't need to add, we already open the template above
'.Workbooks.Add
'* this will not work if there is no 'Sheet1' named worksheet.
'* use the Index number instead
'.Sheets("Sheet1").Select
End With

'Step 5: Copy the recordset to Excel
With wb.WorkSheets(1)
.Range("A41").CopyFromRecordset MyRecordset

'Step 6: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
.Cells.EntireColumn.AutoFit
End With

'Step 7: Housekeeping and Message
MyRecordset.Close
Set MyQueryDef = Nothing
Set MyRecordset = Nothing
Set MyDatabase = Nothing
'MsgBox "Query has been successful", vbInformation, "Sample"

End Sub
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:37
Joined
May 7, 2009
Messages
19,241
find the duplicaye sub and delete it.
also your excel file shoild include the extension, .xlsx or .xls
 
You must log in or register to reply here.

Similar threads

M
Export multiple access query to same workbook two sheets
Replies
9
Views
705
Pat Hartman
P
A
Trouble Exporting Sorted Data from Access Form with Subforms to Excel
Replies
2
Views
97
ebs17
E
MattBaldry
Solved Export and Format Excel
Replies
7
Views
582
Gasman
Gasman
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,051
Cotswold
Cotswold
L
Retrieve excel from access (vol 4) , extended format , alternative color
Replies
7
Views
568
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom