-
Dec 15th, 2005, 10:51 AM
#1
Thread Starter
Junior Member
Export from ACCESS to Excel using VBA
Hi,
I want to export queries, pivot tables from ACCESS to EXCEL using VBA codes.
and i want to export them to specific places in excel spreadsheets. like i want to export two different query recordsets into one worksheet and put them in specific places. how to achieve that?
for now, i only have
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_lookup_ird", "c:\temp.xls", True
but i dont know to how to export pivot table using VBA, and i dont know how to put them in specific places.
thanks a lot in anticipation.
-
Dec 15th, 2005, 11:08 AM
#2
Hyperactive Member
Re: Export from ACCESS to Excel using VBA
Why not export the data then create a pivot in excel? I found this:
VB Code:
Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
FieldName As String, TargetRange As Range)
' Example: DAOCopyFromRecordSet "C:\FolderName\DataBaseName.mdb", _
"TableName", "FieldName", Range("C1")
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
" WHERE " & FieldName & _
" = 'MyCriteria'", dbReadOnly) ' filter records
' write field names
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
-
Dec 15th, 2005, 11:14 AM
#3
Thread Starter
Junior Member
Re: Export from ACCESS to Excel using VBA
Thanks a lot for the reply.
i will look into it.
but the reason that i want to export the pivot table is, i have created the pivot table in access, for viewing purpose. cos, i am going to view the reports and pivottables in access. only when i want to document them, i need to export all of them into one excel file.
anything that you think would help me with my export would be greatly appreciated.
-
Dec 16th, 2005, 06:39 AM
#4
Re: Export from ACCESS to Excel using VBA
Open access
OPen a recordset (rst)
Open excel
Add a new workbook
Get a ref to the sheet
Get a range object (rng) to the cells where you want the table to display.
Use Excels method to copy a recordset : rng.copyfromrecordset rst
It will overwrite the cells with the recordset. If you are automating from Access, this shouldn't be a problem.
Oh, has to be less than 65000 records returned too.
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Jan 3rd, 2006, 02:42 AM
#5
Thread Starter
Junior Member
Re: Export from ACCESS to Excel using VBA
Hi, what i have tried is this, can help me out with the missing part?
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sOutput As String
Dim rng As Range
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Const cTabTwo As Byte = 1
DoCmd.Hourglass True
' set to break on all errors
Application.SetOption "Error Trapping", 0
sOutput = CurrentProject.Path & "c:\Book1.xls"
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
sSQL = "select * from qry_Client_Ranking"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
Set rng = A1: G26 (How TO DEFINE THE RANGE OBJECT?)
rng.CopyFromRecordset rst
may i know how to correct the range object and complete this procedure? thanks
-
Feb 24th, 2010, 11:56 AM
#6
New Member
Re: Export from ACCESS to Excel using VBA
Hey, you can set a range as below:
Code:
Set rng = wks.Range("A1:I4000")
If you get an error that the range type is undefined, you have to check Excel in the "Refferences" from the "Tools" menu.
Here was my script:
Code:
Dim rst As DAO.Recordset
Dim customQuery As String
Dim cnt As Integer
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim fileName As String
Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A2:I4001")
wks.Cells(1, 1).Value = "Generating data..."
Set rst = CurrentDb.OpenRecordset(customQuery)
If (rst.RecordCount > 0) Then
cnt = 1
For Each fld In rst.Fields
wks.Cells(1, cnt).Value = fld.Name
cnt = cnt + 1
Next fld
Call rng.CopyFromRecordset(rst, 4000, 26)
End If
fileName = ExportDir & "\Molenproductie" & DateTimeFrom & "-" & DateTimeTo & ".xls"
wks.SaveAs (fileName)
rst.Close
Set rst = Nothing
PS Sorry for the grave digging, but nice thread!
Last edited by peertje888; Feb 24th, 2010 at 03:31 PM.
-
Mar 22nd, 2010, 10:28 AM
#7
New Member
Re: Export from ACCESS to Excel using VBA
This article was very helpful for me in what I am trying to accomplish. However, I would ALSO like to have the user specify the range for the export in the same way you do when creating a fomrula in Excel. Does anyone have some tips for me in that regard?
-
Mar 22nd, 2010, 11:49 AM
#8
Re: Export from ACCESS to Excel using VBA
What I posted before puts in a specific place... however you will need to tie it together somehow (like how does access know which excel and sheet to use and the cell location...?)
The problem with the specific place is that if it is not big enough, then it will overwrite rather than move the data out of the way.
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|