What I have is a command button in Access 2013. When it was a .adp project in Access 2010 I could click the button and it would transfer the results from a view in SQL Server into an excel spreadsheet. Ever since the upgrade from .adp in 2010 to .accdb in 2013 I get a property not found error on the first line of code. Ill paste the entire code below:
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
'On Error GoTo errorcatch
'Export the open issues query results (created view in SQL server)
DoCmd.OutputTo acOutputServerView, "dbo.Issue Open 3", acFormatXLSX, "I:\Groups\ccuser\Finance\Collection Control\Open Issues\VendorOpenIssues.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
objXLSheet.Range("A1", "L1").Select
objXLSheet.Cells.EntireColumn.AutoFit
'Center columns G and H, Set Column Width, Then Wrap Text
objXLSheet.Range("a:a", "l:l").VerticalAlignment = xlCenter
objXLSheet.Range("a:a", "d:d").HorizontalAlignment = xlCenter
objXLSheet.Range("f:f", "h:h").HorizontalAlignment = xlCenter
objXLSheet.Range("k:k", "l:l").HorizontalAlignment = xlCenter
objXLSheet.Range("A1", "L1").HorizontalAlignment = xlCenter
objXLSheet.Range("i:i", "m:m").HorizontalAlignment = xlCenter
objXLSheet.Range("j:j", "k:k").HorizontalAlignment = xlLeft
objXLSheet.Columns("f:h").ColumnWidth = 10
objXLSheet.Range("E1:L1").WrapText = True
objXLSheet.Columns("B").ColumnWidth = 19
objXLSheet.Columns("F").ColumnWidth = 35
objXLSheet.Columns("J").ColumnWidth = 80
objXLSheet.Columns("P").ColumnWidth = 15
'Add Filter
objXLSheet.Range("A1:T1").AutoFilter
'set the row height
objXLSheet.Rows.AutoFit
'Insert a Row at Row 1
objXLSheet.Range("A1").EntireRow.Insert
With objXLSheet.Cells(1, 1)
.Font.Size = 16
.Font.Bold = True
.Value = "OPEN ISSUES - " & Date
.HorizontalAlignment = xlLeft
End With
'Set Print Properties
objXLSheet.PageSetup.Orientation = xlLandscape
objXLSheet.PageSetup.PaperSize = xlPaperLegal
'Set 1 page wide
With objXLSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
'Calculate LastRow
LastRow = Cells(Cells.Rows.COUNT, "A").End(xlUp).Row
'Conditional formatting Green >1 Week before Expected or Revised Due date, Yellow =< 1 Week, Red past due
'Insert 3 columns to the left of column A
objXLSheet.Columns("A:C").Insert Shift:=xlToRight
'Insert equation into cell A3"=IF(R3="",Q3,R3)
objXLSheet.Cells(3, 1).Value = "=IF(R3="""",Q3,R3)"
objXLSheet.Range("A:A").NumberFormat = "yyyy-mm-dd"
'Insert the equation into cell B3 "=TODAY()"
objXLSheet.Cells(3, 2).Value = "=TODAY()"
'Insert the equation into cell C3 "=A3-B3"
objXLSheet.Cells(3, 3).Value = "=A3-B3"
objXLSheet.Range("C:C").NumberFormat = "0"
'Fill down equation in cells A3:C3
objXLSheet.Range("A3:C" & LastRow).FillDown
'Set the value I equal to 3
i = 3
'Check the value in column C and color everything to the right accordingly
For i = 3 To LastRow
If objXLSheet.Range("S" & i) = "Remediated Pending Further Testing" Then
ElseIf objXLSheet.Range("C" & i) >= 7 Then
objXLSheet.Range("C" & i & ":T" & i).Interior.ColorIndex = 10
ElseIf objXLSheet.Range("C" & i) >= O And objXLSheet.Range("C" & i) < 7 Then
objXLSheet.Range("C" & i & ":T" & i).Interior.ColorIndex = 6
ElseIf objXLSheet.Range("C" & i) < O Then
objXLSheet.Range("C" & i & ":T" & i).Interior.ColorIndex = 3
Else: Message = "VBA CODING IS INCORRECT Check and fix coding"
End If
Next i
'Set the value I equal to 3
i = 3
'Check the value in column C and color everything to the right accordingly
For i = 3 To LastRow
If objXLSheet.Range("S" & i) = "" Then
objXLSheet.Range("S" & i) = "Open"
End If
Next i
LastRow = 0
i = 0
'Hide columns A through C
objXLSheet.Columns("A:C").Hidden = True
'save the changes
objXLBook.Save
objXLBook.Close
Set objXLApp = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
'Opens the report
Application.FollowHyperlink strFilePath
'Exit Sub
'errorcatch:
'MsgBox Err.Description
End Sub