pass query set to excel sheet/form

  • Thread starter Thread starter don
  • Start date Start date
D

don

I have an Access 2007 application and would like to pass fields (like a mail
merge), or for a particular single person's row selected, into an existing
excel form/doc. Fields such as name, address, $$, etc.
any samples for automating?
thanks!
 
Don,
here is some code
most of this code opens excel, finds the correct workbook,
correct worksheet, correct cells.

These 2 lines
objXLws.Cells(3, 1) = "Category"
objXLws.Cells(3, 2) = "Sales"
Put the values Category and Sales into 2 cells

The remainder of the code saves the data and closes excel.



'----------------------
'replace strPath, strWkbName
'and strWksName with your values
'to call this sub
' Call CopyValues2XL
'----------------------

Public Sub CopyValues2XL()
On Error GoTo SubErr
Dim db As DAO.Database
Dim objXLApp As Object 'Excel.Application
Dim objActiveWkb As Object 'Excel.Application
Dim objXLWkb As Object 'Excel.Application
Dim objXLws As Object 'Excel.Application
Dim strWkbName As String 'name of workbook
Dim strWksName As String 'name of worksheet
Dim strPath As String 'path to workbook



'this make take a while,
'turn on the hourglass
DoCmd.Hourglass True

strPath = "C:\Temp"
strWkbName = "Test.xls"
strWksName = "Sheet1"

Set db = DBEngine(0)(0)

'Open excel
Set objXLApp = CreateObject("Excel.Application")


'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(strWkbName)
'activate the selected workbook
objXLWkb.Activate
'point to the wanted worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets(strWksName)
'activate the selected worksheet
objXLws.Activate


objXLws.Cells(3, 1) = "Category"
objXLws.Cells(3, 2) = "Sales"

'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

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:
MsgBox Err.Number & " " & Err.Description
Resume SubExit

End Sub


Jeanette Cunningham
 
Jeanette - thank you, it's perfect!

Jeanette Cunningham said:
Don,
here is some code
most of this code opens excel, finds the correct workbook,
correct worksheet, correct cells.

These 2 lines
objXLws.Cells(3, 1) = "Category"
objXLws.Cells(3, 2) = "Sales"
Put the values Category and Sales into 2 cells

The remainder of the code saves the data and closes excel.



'----------------------
'replace strPath, strWkbName
'and strWksName with your values
'to call this sub
' Call CopyValues2XL
'----------------------

Public Sub CopyValues2XL()
On Error GoTo SubErr
Dim db As DAO.Database
Dim objXLApp As Object 'Excel.Application
Dim objActiveWkb As Object 'Excel.Application
Dim objXLWkb As Object 'Excel.Application
Dim objXLws As Object 'Excel.Application
Dim strWkbName As String 'name of workbook
Dim strWksName As String 'name of worksheet
Dim strPath As String 'path to workbook



'this make take a while,
'turn on the hourglass
DoCmd.Hourglass True

strPath = "C:\Temp"
strWkbName = "Test.xls"
strWksName = "Sheet1"

Set db = DBEngine(0)(0)

'Open excel
Set objXLApp = CreateObject("Excel.Application")


'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(strWkbName)
'activate the selected workbook
objXLWkb.Activate
'point to the wanted worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets(strWksName)
'activate the selected worksheet
objXLws.Activate


objXLws.Cells(3, 1) = "Category"
objXLws.Cells(3, 2) = "Sales"

'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

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:
MsgBox Err.Number & " " & Err.Description
Resume SubExit

End Sub


Jeanette Cunningham
Click to expand...
 
Back
Top