error transferring results from sql server to excel in acces 2013

[cleteh]

We upgraded to Access 2013 so .adp no longer is supported. The code below no longer works, can anyone help me code this correctly in VBA for Access 2013? This line errors out now that its not a .adp file DoCmd.OutputTo acOutputQuery, "dbo.Issue Open 3", "ExcelWorkbook(*.xlsx)", "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint

'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"

[OBP]

This appears to be a duplicate questioYou need to add Error trapping to your code to edentify why that line of code is failing.
It may well be this part

"ExcelWorkbook(*.xlsx)"

which should probably be

acFormatXLSX

[cleteh]

How do I add error trapping? The error I get reads Complile error: syntax error..... I tried your suggestion but it didn't work

[OBP]

Actually for that particular error you do not need error trapping, as it is a Compile error.
A syntax error means that the compiler is expecting either text to be in the correct format or it is expecting a character which may be used in the code which is missing or incorrect.

So when you tried my suggestion did you do so without the Quote " marks?

To add error trapping to code you should have this line at the beginning of the code

On Error GoTo errorcatch

and this code before the End Sub

Exit Sub
errorcatch:
MsgBox Err.Description


You can use another name instead of errorcatch, but they must be the same in both parts of the code.

[cleteh]

Yes, now I get a property not found error.... This code worked fine until we upgraded to Access 2013. Before it was a .adp project now its .accdb Should I try this error trapping now?

[cleteh]

I get property not found with the error trapping code you suggested as well

[cleteh]

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

[OBP]

Yes you could use the error trapping as it might highligt where the error is.
But it could be the Template File part of the expression, which is currently "", you could try deleting the quotes as normally you can just leave them blank when not being used.

[cleteh]

The big difference is in the previous version of the database which was a .adp and the code did work I did not link directly to the tables..... In this new version where the code does not work it is a .accdb file and I do link directly to the tables including the view Issue Open 3. The error I'm getting is 3270 Property not found.....

Its occurring on this line
DoCmd.OutputTo acOutputServerView, "dbo.Issue Open 3", acFormatXLSX, "I:\Groups\ccuser\Finance\Collection Control\Open Issues\VendorOpenIssues.xlsx", False, "", , acExportQualityPrint

I don't think acOutputServerView is appropriate to use anymore but im stuck on what to do

[OBP]

Did you try removing the "" from the
DoCmd.OutputTo acOutputServerView, "dbo.Issue Open 3", acFormatXLSX, "I:\Groups\ccuser\Finance\Collection Control\Open Issues\VendorOpenIssues.xlsx", False, "", , acExportQualityPrint
so tha it becomes
DoCmd.OutputTo acOutputServerView, "dbo.Issue Open 3", acFormatXLSX, "I:\Groups\ccuser\Finance\Collection Control\Open Issues\VendorOpenIssues.xlsx", False, , , acExportQualityPrint

[cleteh]

yes, I get the same error

[OBP]

OK, let's try a process of elimination.
First of all does the query run OK manually?
Can you change the Location path to something simpler on your own computer to see if it is the Path causing the problem.
If we can't fix this I do have VBA code for outputting the data directly into a worksheet.

[cleteh]

I just saw your reply not sure how I missed it. Yes the query works its actually a view in SQL Server. I can also open the linked table in Access 2013 and all the results are there. I know the path is correct because when I paste it into windows explorer it takes me to the file

[OBP]

How would you like to proceed?

[OBP]

I do have a couple of suggestions for you, you can copy & paste a query to Excel, so you can test if your Query will do so.
If it does it can probably be done so with VBA.
You can also Paste Link, so that the data remains "live" if the data changes so does the excel data to reflect it.

[cleteh]

This code is attached to a command button in an Access database that our employees use... I have written a ton of code to format the sheet and color format the rows after the data is moved into an excel sheet. This code worked fine before as .adp I really need to figure out how to get it right now that its .accdb. I really think it goes back to as a .adp file the database linked to sql server and now that its a .accdb file the tables are linked once you open the database.

This has to be common... I'm sure people use vba to transfer results from an SQL Server View to an.xlsx sheet all the time. I don't why I've had so much trouble finding out how to code this. I don't think acOutputQuery works at all in the .accdb format.

[cleteh]

SOLVED: I created a query and used acOutputQuery instead of acOutputServerView

DoCmd.OutputTo acOutputQuery, "OpenIssue3", acFormatXLSX, "I:\Groups\ccuser\Finance\Collection Control\Open Issues\OpenIssuesReport.xlsx", False, "", , acExportQualityPrint