PDA

View Full Version : issue opening a view in sql server using vba



cleteh
07-14-2017, 06:43 AM
I recently had to upgrade an access database from .adp project to an actual database in Access 2013 because Access 2013 doesn't support a .adp project. A line of code that worked previously is now causing an error when I try to transfer the results of a view into excel. The line causing the error is highlighted in red below. Its the line that reads DoCmd.OutputTo acOutputQuery, "dbo.Issue Open 3", "ExcelWorkbook(*.xlsx)", "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint


Private Sub OpenIssues_Click()
Dim strFilePath As String
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim LastRow As Long
Dim i As Integer
'Export the open issues query results (created view in SQL server)
DoCmd.OutputTo acOutputQuery, "dbo.Issue Open 3", "ExcelWorkbook(*.xlsx)", "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint
'Sets strFilePath equal to the location of the excel file
strFilePath = "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx"
'Opens Excel
Set objXLApp = CreateObject("Excel.Application")
'Opens Workbook
Set objXLBook = GetObject(strFilePath)
'Opens the sheet
Set objXLSheet = objXLBook.Worksheets(1)
'for testing, make the application visible
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
'autofit and format the columns
objXLSheet.Activate