I have been attempting to create a VB procedure which takes data from access and writes it to Excel. I have it down with tables but when I try queries I run into SOME queries which do not work and receive the Error
Invalid SQL statement, expected 'delete, 'Insert', 'Procedure', select or 'update'
As I havent done much vba, and it was in DAO, so I am confused as to why. I have been working at home over the weekend with the North winds sample MDB trying to get it to work and have these examples.
The first "open" is a table and this writes to excel fine.
The second "open' is a saved query from the sample database which gives me the error.
The third is another sample query from Northwinds but it WORKS perfectly. I cut and pasted the names to be sure.
The only general difference I can see between the two queries is the "products by category" has a criteria on one field "<>yes".
I would think I could open ANY saved query. I wanted to use saved queries as the end users may wish to modify them over time.
Here is the entire code if it helps. It will end up pulling from Oracle and some huge data tables our developers are creating. But I find it is often easier to get the logic down by working small!
Invalid SQL statement, expected 'delete, 'Insert', 'Procedure', select or 'update'
As I havent done much vba, and it was in DAO, so I am confused as to why. I have been working at home over the weekend with the North winds sample MDB trying to get it to work and have these examples.
The first "open" is a table and this writes to excel fine.
The second "open' is a saved query from the sample database which gives me the error.
The third is another sample query from Northwinds but it WORKS perfectly. I cut and pasted the names to be sure.
The only general difference I can see between the two queries is the "products by category" has a criteria on one field "<>yes".
I would think I could open ANY saved query. I wanted to use saved queries as the end users may wish to modify them over time.
Code:
Set rst = New ADODB.Recordset
With rst
.Open "Customers", strConn, adOpenKeyset, adLockOptimistic 'TABLE
'.Open "Products by Category", strConn, adOpenForwardOnly, adLockReadOnly 'Query
'.Open "orders qry", strConn, adOpenForwardOnly, adLockReadOnly 'Query
End WithHere is the entire code if it helps. It will end up pulling from Oracle and some huge data tables our developers are creating. But I find it is often easier to get the logic down by working small!
Code:
Option Compare Database
Option Explicit
Public myExcel As Excel.Application
Sub CopyToExcel()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim StartRange As Excel.Range
Dim xlRange As Excel.Range
Dim strConn As String
Dim C As Integer 'column
Dim R As Integer 'Row
Dim I As Integer 'counter
Dim F As Variant
'On Error GoTo ErrorHandler
strConn = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & CurrentProject.path & _
"\northwind.mdb"
Set conn = New ADODB.Connection
'open recordset on the employees table
Set rst = New ADODB.Recordset
With rst
.Open "Employees", strConn, adOpenKeyset, adLockOptimistic
End With
' declare a module-level object
' variable myExcel as Excel.application
' at the top of the module
Set myExcel = New Excel.Application
' Create a new excel workbook
Set wbk = myExcel.Workbooks.Add
'
'START WORKSHEET 1-----------------------------------------------------
' set the reference to the ActiveSheet
Set wks = wbk.ActiveSheet
'Rename the first workbook
wks.Name = "Summary by Employee"
' make the excel application window visible
myExcel.Visible = True
'Start at this column 1 = A, 2 is B etc
C = 1
I = C
'Start with this row
R = 2
' create the column headings in cells
With rst
For Each F In .Fields
With wks
.Cells(R, I).Value = F.Name
I = I + 1
End With
Next
End With
'FORMAT THE HEADINGS
'select the first row
Set xlRange = wks.Rows(R)
'bold the selection, set the font and fontsize, center all the cells
xlRange.Font.Bold = True
xlRange.Font.Size = 10
xlRange.Font.Name = "arial"
xlRange.HorizontalAlignment = xlCenter
' specify the range that will receive the data (a2)
Set StartRange = wks.Cells(R + 1, C)
' copy the records from the recordset
' and place in cell A2
StartRange.CopyFromRecordset rst
'close and empty the recordset rst
rst.Close
Set rst = Nothing
'autofit the columns to make the data fit
'wks.Columns("a:z").AutoFit
wks.Columns.AutoFit
'FREEZE THE PANE so the header row doesn't scroll
wks.Activate
wks.Range("A2").Select
myExcel.ActiveWindow.FreezePanes = True
'Start second tab ----------------------------------------------------------------
'change to another worksheet tab
Set wks = wbk.Sheets("Sheet2")
wks.Name = "Summary by Product"
wks.Cells(1, 1) = "Tesco"
wks.Cells(1, 2) = "Stock holding by line"
'MAKE SURE WORD WRAP IS OFF FOR FORMATTING
wks.Cells.WrapText = False
' xlRange.WrapText = False
'FORMAT THE TITLE
'select the first row
Set xlRange = wks.Cells(1, 1)
'bold the selection, set the font and fontsize, center all the cells
xlRange.Font.Bold = True
xlRange.Font.ColorIndex = 3 'red
xlRange.Font.Size = 18
xlRange.Font.Name = "Times New Roman"
xlRange.HorizontalAlignment = xlCenter
'FORMAT THE REPORT NAME
'select the first row
Set xlRange = wks.Cells(1, 3)
'bold the selection, set the font and fontsize, center all the cells
xlRange.Font.Bold = False
xlRange.Font.Size = 16
xlRange.Font.Name = "arial"
xlRange.HorizontalAlignment = xlLeft
'
' 'FORMAT THE ROW HEIGHT AND COLUMN WIDTH
' Cells.Select
' Selection.ColumnWidth = 100
' Selection.Columns.AutoFit
' Selection.Rows.AutoFit
'
Set rst = New ADODB.Recordset
With rst
.Open "Customers", strConn, adOpenKeyset, adLockOptimistic 'TABLE
'.Open "Products by Category", strConn, adOpenForwardOnly, adLockReadOnly 'Query
'.Open "orders qry", strConn, adOpenForwardOnly, adLockReadOnly 'Query
End With
' 'Start at this column 1 = A, 2 is B etc
C = 1
'
' 'Start with this row
R = 2
' This is just a counter for the number of columns in the recordset
I = C
'
' ' create the column headings in cells
With rst
For Each F In .Fields
With wks
.Cells(R, I).Value = F.Name
I = I + 1
End With
Next
End With
'
'FORMAT THE HEADINGS on the second sheet
'select the first row
Set xlRange = wks.Rows(R)
'bold the selection, set the font and fontsize, center all the cells
xlRange.Font.Bold = True
xlRange.Font.Size = 10
xlRange.Font.Name = "arial"
xlRange.HorizontalAlignment = xlCenter
' ' specify the rang that will receive the daa (a2)
Set StartRange = wks.Cells(R + 1, C)
'
' copy the records from the recordset
' and place in cell A2
StartRange.CopyFromRecordset rst
rst.Close
Set rst = Nothing
'autofit the columns to make the data fit
'wks.Columns("a:z").AutoFit
wks.Columns.AutoFit
'FREEZE THE PANE so the header row doesn't scroll
wks.Activate
wks.Range("A2").Select
myExcel.ActiveWindow.FreezePanes = True
' 'End Worksheet 2------------------------------------------------------------------
' wbk.Save
'wbk.Close savechanges:=True, fileName:="c:\filedump.xls"
'Quit excel application
' myExcel.Quit
Set conn = Nothing
Set StartRange = Nothing
Set wks = Nothing
Set wbk = Nothing
Set myExcel = Nothing
'conn.Close
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical, "Don't blame the computer"
Set myExcel = Nothing
Exit Sub
End Sub
