Using DAO to export access query results to excel

[SHAWTY721]

Is it possible when using DAO to export results of a parameter query into
excel; to have more than one field from the parameter query to populate a
specific column in the specified excel spreadsheet template that is being
populated with the selected data.

 

[Jeanette Cunningham]

Hi
I have exported to excel templates where I have one sheet in the workbook
called RawExport.
The template has another worksheet linked to sheet RawExport using functions
on the data in RawExport.

Another possibility is to combine the fields in a calculated query before
you export them - don't know if this suits what you are trying to achieve.

Jeanette Cunningham

 

[SHAWTY721]

It sounds like the first example you gave using the excel template RawExport
and linking it to another spreadsheet might work for my needs and help me
accomplish what I am trying to do.

 

[SHAWTY721]

Jeanette I was wondering is there any way you can post a sample of this
example, so I can check it out fully.

Thanks!

 

[Jeanette Cunningham]

Hi,
in code use TransferSpreadsheet or OutputTo, to export to excel. Here is a
code snippet for exporting to excel. Use the appropriate lines from the code
to build your own functions.

'Using the path information, export the formatted
'export query as an Excel 2000 (or greater) file
DoCmd.OutputTo acOutputQuery, strSQL, acSpreadsheetTypeExcel9, strPath

'parse out the filename from strPath
intI = InStrRev(strPath, "\")
'Debug.Print intI
strFile = Mid$(strPath, intI + 1)
'Debug.Print strFile

'now format the worksheet
' pstrMakeActive is the name of the worksheet to activate
Call FormatXLReport(strPath, strFile, lngReportID, pstrMakeActive)
'use delay of 1 sec to allow excel to finish closing before access
continues
Call CreateDelay



In excel use paste special link from the sheet with the raw data to the
sheet the users will use.
I will leave the functions and paste special link bit for you to set up in
excel.
As you are using a template, I assume you will have the formatting setup the
way you want.


Here is some code from an app that shows how to choose a worksheet from a
workbook

Public Sub FormatXLReport(strPath As String, _
strFile As String, _
lngReportID As Long, _
strMakeActive As String)

'lngReportID identifies which report is being run
'strMakeActive name of worksheet to activate

On Error GoTo SubErr
pstrProc = "FormatXLReport"
pstrSubProc = "FormatXLReport"
Dim db As DAO.Database
Dim blnExcelExists As Boolean
Dim objXLApp As Object
Dim objActiveWkb As Object
Dim objXLWkb As Object
Dim objXLws As Object
Dim strWkbName As String
Dim strCriteria As String


Set db = DBEngine(0)(0)
'Open the raw data spreadsheet for formatiing

If fIsAppRunning("excel", False) Then 'yes it is running
' Get a reference to currently running Excel window
Set objXLApp = GetObject(, "Excel.Application")
blnExcelExists = True
Else
' Excel is not currently running so create a new instance
Set objXLApp = CreateObject("Excel.Application")
End If



'Hide warnings on the spreadsheet
objXLApp.DisplayAlerts = False
'prevent any excel macros from running
objXLApp.Interactive = False
'hide screen changes
objXLApp.ScreenUpdating = False
'Open a workbook
objXLApp.Workbooks.Open (strPath)
'point to the active workbook
Set objXLWkb = objXLApp.Workbooks(strFile)
'Debug.Print "active workbook: " & objXLWkb.Name
'activate the selected workbook
objXLWkb.Activate
'Debug.Print strMakeActive
'Debug.Print "active sheet: " & ObjXLApp.ActiveWorkbook.Worksheets(1)
'point to the wanted worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets(1)
'activate the selected worksheet
objXLws.Activate


'put focus back to first data cell
objXLws.Range("A2").Select

'Prevent Excel from prompting to save changes
objXLApp.ActiveWorkbook.Save

SubExit:
'turn on warnings on the spreadsheet
objXLApp.DisplayAlerts = True
'allow any excel macros from running
objXLApp.Interactive = True
'show screen changes
objXLApp.ScreenUpdating = True

'close the instance of Excel created by code
If Not blnExcelExists Then
objXLApp.Quit
End If

If Not objActiveWkb Is Nothing Then
Set objActiveWkb = Nothing
End If
If Not objXLApp Is Nothing Then
Set objXLApp = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If

DoCmd.Hourglass False
Exit Sub

SubErr:
Select Case Err.Number
Case 3010
MsgBox pmsg2 & strPath & pmsg3, vbInformation, pstrT
Case 70, 430
MsgBox pmsg4, vbInformation, pstrT
Case Else
Call fnFormErrHandler(pstrProc, pstrSubProc, pstrMdl, Err)
End Select
Resume SubExit

End Sub


Jeanette Cunningham

 

[SHAWTY721]

Thanks, this should give me a place to start.

Hi,
in code use TransferSpreadsheet or OutputTo, to export to excel. Here is a
code snippet for exporting to excel. Use the appropriate lines from the code
to build your own functions.

'Using the path information, export the formatted
'export query as an Excel 2000 (or greater) file
DoCmd.OutputTo acOutputQuery, strSQL, acSpreadsheetTypeExcel9, strPath

'parse out the filename from strPath
intI = InStrRev(strPath, "\")
'Debug.Print intI
strFile = Mid$(strPath, intI + 1)
'Debug.Print strFile

'now format the worksheet
' pstrMakeActive is the name of the worksheet to activate
Call FormatXLReport(strPath, strFile, lngReportID, pstrMakeActive)
'use delay of 1 sec to allow excel to finish closing before access
continues
Call CreateDelay



In excel use paste special link from the sheet with the raw data to the
sheet the users will use.
I will leave the functions and paste special link bit for you to set up in
excel.
As you are using a template, I assume you will have the formatting setup the
way you want.


Here is some code from an app that shows how to choose a worksheet from a
workbook

Public Sub FormatXLReport(strPath As String, _
strFile As String, _
lngReportID As Long, _
strMakeActive As String)

'lngReportID identifies which report is being run
'strMakeActive name of worksheet to activate

On Error GoTo SubErr
pstrProc = "FormatXLReport"
pstrSubProc = "FormatXLReport"
Dim db As DAO.Database
Dim blnExcelExists As Boolean
Dim objXLApp As Object
Dim objActiveWkb As Object
Dim objXLWkb As Object
Dim objXLws As Object
Dim strWkbName As String
Dim strCriteria As String


Set db = DBEngine(0)(0)
'Open the raw data spreadsheet for formatiing

If fIsAppRunning("excel", False) Then 'yes it is running
' Get a reference to currently running Excel window
Set objXLApp = GetObject(, "Excel.Application")
blnExcelExists = True
Else
' Excel is not currently running so create a new instance
Set objXLApp = CreateObject("Excel.Application")
End If



'Hide warnings on the spreadsheet
objXLApp.DisplayAlerts = False
'prevent any excel macros from running
objXLApp.Interactive = False
'hide screen changes
objXLApp.ScreenUpdating = False
'Open a workbook
objXLApp.Workbooks.Open (strPath)
'point to the active workbook
Set objXLWkb = objXLApp.Workbooks(strFile)
'Debug.Print "active workbook: " & objXLWkb.Name
'activate the selected workbook
objXLWkb.Activate
'Debug.Print strMakeActive
'Debug.Print "active sheet: " & ObjXLApp.ActiveWorkbook.Worksheets(1)
'point to the wanted worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets(1)
'activate the selected worksheet
objXLws.Activate


'put focus back to first data cell
objXLws.Range("A2").Select

'Prevent Excel from prompting to save changes
objXLApp.ActiveWorkbook.Save

SubExit:
'turn on warnings on the spreadsheet
objXLApp.DisplayAlerts = True
'allow any excel macros from running
objXLApp.Interactive = True
'show screen changes
objXLApp.ScreenUpdating = True

'close the instance of Excel created by code
If Not blnExcelExists Then
objXLApp.Quit
End If

If Not objActiveWkb Is Nothing Then
Set objActiveWkb = Nothing
End If
If Not objXLApp Is Nothing Then
Set objXLApp = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If

DoCmd.Hourglass False
Exit Sub

SubErr:
Select Case Err.Number
Case 3010
MsgBox pmsg2 & strPath & pmsg3, vbInformation, pstrT
Case 70, 430
MsgBox pmsg4, vbInformation, pstrT
Case Else
Call fnFormErrHandler(pstrProc, pstrSubProc, pstrMdl, Err)
End Select
Resume SubExit

End Sub


Jeanette Cunningham