I seek assistance with the following piece of VBA code. By the way the code as it current is works well. However, I would like to change one piece.
The issues are as follows.
1 – The spreadsheet and the database files are always in the same folder.
2 – How do I code the path for the excel spreadsheet without the need for the whole path. A user may want locate the database and spreadsheet in a new folder. (This would mean recoding the full path)
3 – The piece of code is identified in red
CODE
Function ExportPerformanceGoals()
'The code references the Excel Object Library.
'If code fails, make sure to click Tool>References
'and check box for Microsoft Excel.
On Error Resume Next
Dim db As Database
Dim rst As Recordset
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set db = CurrentDb()
'Your path and filename go here
[color="red"]Set objBook = GetObject("C:\Documents and Settings\AM (Lou) Kapeller\My Documents\MS Access 2002\AAMS\MSU Authorised Assessors Report.xls") [/color]
Set objApp = objBook.Parent
Set objSheet = objBook.Worksheets("Audit Plan") 'Name of sheet you want to export to
objBook.Windows(1).Visible = True
Set rst = db.OpenRecordset("qryAuthorisedAssessorAuditPlan", dbOpenSnapshot) 'Opens the recordset and sets the variable
With objSheet
.Select
.Range("ExternalData").Clear 'Clears the current data in the workbook range
.Range("A15").CopyFromRecordset rst 'Copies the recordset into the worksheet
End With
rst.Close
objApp.Visible = True
Set rst = Nothing
Set db = Nothing
Set objSheet = Nothing
Set objBook = Nothing
Set objApp = Nothing
End Function
'The code references the Excel Object Library.
'If code fails, make sure to click Tool>References
'and check box for Microsoft Excel.
On Error Resume Next
Dim db As Database
Dim rst As Recordset
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set db = CurrentDb()
'Your path and filename go here
[color="red"]Set objBook = GetObject("C:\Documents and Settings\AM (Lou) Kapeller\My Documents\MS Access 2002\AAMS\MSU Authorised Assessors Report.xls") [/color]
Set objApp = objBook.Parent
Set objSheet = objBook.Worksheets("Audit Plan") 'Name of sheet you want to export to
objBook.Windows(1).Visible = True
Set rst = db.OpenRecordset("qryAuthorisedAssessorAuditPlan", dbOpenSnapshot) 'Opens the recordset and sets the variable
With objSheet
.Select
.Range("ExternalData").Clear 'Clears the current data in the workbook range
.Range("A15").CopyFromRecordset rst 'Copies the recordset into the worksheet
End With
rst.Close
objApp.Visible = True
Set rst = Nothing
Set db = Nothing
Set objSheet = Nothing
Set objBook = Nothing
Set objApp = Nothing
End Function
I would appreciate help with this one.
Thanks
Cheers!!!!
Lou